Select probs in Access / VB

Question

Post reply

Select probs in Access / VB

De Leeuw David

Grasshopper

Points: 12
More actions
February 12, 2004 at 1:06 pm

#65944

Hello, I'm dealing with a problem and hope that someone here can help me out.
I used to consult a datebase by the help of forms and sub-forms, but since I need to make the consultation available true a network I switch to VB and here is where my problem is. My select-statement in Access with a sub-form was working fine and is build up as follows :
SELECT [tbl_Conclusions].[Num_conclusions], [tbl_Requêtes].[Nom_contribuable], [tbl_Conclusions].[Sorte_de_document], [tbl_Conclusions].[Sélection]
FROM tbl_Requêtes INNER JOIN (tbl_Conclusions INNER JOIN tbl_Mots_clés ON [tbl_Conclusions].[Num_conclusions]=[tbl_Mots_clés].[Num_conclusions]) ON [tbl_Requêtes].[Num_requete]=[tbl_Conclusions].[Num_requete]
WHERE ((([tbl_Mots_clés].[Explication_code_interne])=[Forms]![Frm_RechercheViaMotsClés]![Liste1]))
ORDER BY [tbl_Conclusions].[Num_conclusions];
the bold text is listbox where I select a keyword, in VB it changes up to :
WHERE (((tbl_Mots_clés.Explication_code_interne) = 'dbcMotsClé.Text'))
you can see that I need to use apostrophes because it is a string and since I'm from belgium and the language I use in the database is french lots of keywords already contains an apostrophe and then I get this as result.
)='COMMUNE D'IMPOSITION')) -> 3 apostrophes are giving a problem
Can somebody help me how to solve this problem ? What do I have to do so the apostrophe in "COMMUNE D'IMPOSITION" doesn't give a problem??

David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

February 12, 2004 at 1:24 pm

#493887

'COMMUNE D''IMPOSITION'

* Noel

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Use folowing function to convert ' to ''

on Error GoTo eh:

Dim strName() As String

Dim i As Integer

strName = Split(txtName, "'")

Dim obj As Variant

txtName = ""

i = 0

For Each obj In strName

txtName = txtName + strName(i) & "''"

i = i + 1

txtName = Left(txtName, Len(txtName) - 2)

End If

Exit Sub

eh:

My Blog: http://dineshasanka.spaces.live.com/

kangaroo Newbie Points: 1 More actions · Answer 3

There is much easier way in VB. You can just apply replace function to string you want to pass to SQL, for example:

strText=replace(strText,"'","''")

Should work

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

Yes ur right kangaroo

But the problem is what will happen for a string which already has two '

Say

for Dinesh''Asanka

replace(Dinesh''Asanka) = Dinesh''''Asanka

My function will avoid such situations

My Blog: http://dineshasanka.spaces.live.com/

De Leeuw David Grasshopper Points: 12 More actions · Answer 5

I would like to thanks all off you for replying but I made the select working by making a small change.

Old not working code:

WHERE (((tbl_Mots_clés.Explication_code_interne) = 'dbcMotsClé.Text'))

new code

WHERE (((tbl_Mots_clés.Explication_code_interne) = " & chr(39) & dbcMotsClé.Text & chr(39)))

chr(39) = "

David

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 6

Sorry De Leeuw David

I was carried away with the first answer. sorry about that.

I think What u have done last is right.

U have to create a SQL Statment whcih u have have done now.

Sorry again for the mistake.

I think I should read the questions more carefully.

My Blog: http://dineshasanka.spaces.live.com/