converting qbe query to vba code problem

[zathrus777]

This is the SQl produced by access from my QBE query

SELECT felearnaim.ID, felearnaim.A14, felearnaim.ERRORS
FROM felearner INNER JOIN felearnaim ON felearner.ID = felearnaim.STUDENT
WHERE (((felearnaim.ERRORS) Like "*""a14_*&quot);

When I try to run the same query in VBA it baulks on the

Like "*""a14_*&quot);


I tried it on a simpler text string:

ExpNames.Open _
"SELECT ID, A14 FROM felearnaim " & _
"Where ERRORS like '*TH*'", CurrentProject.Connection

but this produces nothing. I know the problem is the syntax of the "" I need. Any ideas?

Mike
note this was also posted in the JET SQL forum but I think this forum is more appropriate.

 

[vbajock]

Like "*""a14_*" looks invalid. Could you explain it a little?

Generally you solve quote problems by using a single quote in code whereever you have a double quote. Jet (and most SQL engines) interpet them the same way. if you are searching for a double quote, enclose the double quote in singles.

 

[zathrus777]

I have partly solved it: I am looking for the string

"a14_

the whole string contains loads of " characters so I need wildcards. I have just been told that * does not work and to use % which just fixed that but in the QBE grid I used "" to represent " but this no longer works in VBA.

thanks

 

[zathrus777]

On talking to collegues it seems that the setup of access may be at fault here as others use wildcards and quotes normally - could this be the problem. Am using ADO 2.1 and access 2000.

Any help appreciated.

 

[vbajock]

If I was trying to find the string

"a14_

this is how I would do it:

'chr(34) = "

dim sql as string

sql="SELECT felearnaim.ID, felearnaim.A14,
sql=sql+" felearnaim.[ERRORS]
sql=sql+" INNER JOIN felearnaim ON
sql=sql+" felearner.[ID] = felearnaim.STUDENT
sql=sql+" WHERE felearnaim.[ERRORS] = ' "
sql=sql+chr(34)+"a14_' "


Like "*""a14_*"

would be "Like '*"+chr(34)+"a14_* ' "

ExpNames.Open sql, CurrentProject.Connection
Note Errors, ID are in brackets as they may be key words

 

[zathrus777]

am afraid this produces the same result. I am thinking its a problem with the references I havce setup in tools and wether I am using ADO or DAO. Could this be the reason?

I need to produce this datbase and currently I am just going to have to have a load of queries sitting there being run by docmd which is a pain.

I have both DAO 3.6 and ADO 2.1 installed. I gather DAO uses * and ADO uses % as a wildcard but there seems no correlation to my problem.

Mike

 

[vbajock]

ADO or DAO doesn't make any difference. Code within the SQL string is processed by the Jet SQL engine, not by ADO or DAO, they are merely the transport technology between your application and the engine. If the query grid will process it, Jet will process it as a string in the same way using the same symbols. If it doesn't, you have a syntax problem. I am assuming your QBE returns the records you need ?


Click Tools/References and you will see what libraries you are using. Access 2000 and up uses ADO by default.

If you get a break in your code when it errors out, choose View/Immediate window
In the window type
? sql
Hit enter and the current value of the variable sql will appear in the immediate window.
Cut and paste this into a reply window so I can see what you are getting back.

What is the exact error message you are getting? Your open statement looks incomplete. Here is an example of what I would use. I put in an error handler so you can process errors with in the function:

Function MyFunc()

Dim oConn as New ADO.Connection
Dim Expnames as New ADO.Recordset
Dim sql as string

On error goto err_h

Set oConn=CurrentProject.Connection

sql="SELECT felearnaim.ID, felearnaim.A14,
sql=sql+" felearnaim.[ERRORS]
sql=sql+" INNER JOIN felearnaim ON
sql=sql+" felearner.[ID] = felearnaim.STUDENT
sql=sql+" WHERE felearnaim.[ERRORS] = ' "
sql=sql+chr(34)+"a14_' "

ExpNames.Open sql, oConn,_ adOpenDynamic, adLockOptimistic, adCmdText

'do stuff

Exit Function

'*****
Err_h:
'*****
Msgbox Error$
'For initial debug only
'Hit F8 to return to offending line
Stop
Resume



End Function

 

[zathrus777]

vbajock

The code you give baulks at:

Dim oConn as New ADO.Connection

Compile error, user defined type not defined.

When using the locals window it shows up my SQL command as:

: CommandText : "SELECT ID, A14 FROM felearnaim Where (errors like '%"a14_%')" : String

It produces output but ignores the " so instead of 15 records I get about 100.

Here is the sub in full which includes an output to file routine.

Sub writequery03()
Dim strSQL As String
strSQL = "'%""a14_%'"
Dim Expnames As New Recordset
Expnames.Open _
"SELECT ID, A14 FROM felearnaim " & _
"Where (errors like " & strSQL & &quot", CurrentProject.Connection
Open "felearner_Write.txt" For Output As #2
Write #2, "enrolmentisr.id", "enrolmentisr.studentreference"
Do Until Expnames.EOF
Write #2, _
Expnames!ID, _
Expnames!A14
Expnames.movenext
Loop
Close #2
End Sub

This version produces the following SQL in the locals window:

: CommandText : "SELECT ID, A14 FROM felearnaim Where (errors like '%"a14_%')" : String


It produces results bur the " does nothing and I have to have this in the string.

Mike

 

[vbajock]

Dim oConn as New ADO.Connection
should be
Dim oConn as New ADODB.Connection

Try * instead of %

 

[tstrike]

Try using double quotes. It worked for me on my problem.

Look at the Thread I wroet not to long ago. It sould be towards the top of this forum.

 

[zathrus777]

t strike - I have tried the double quote thing but still get nowhere. It just seems to ignore the quote when added in.

VBA jock I haven't got yor version to work yet as I have been getting my docmd version of the routine going. I have a workaround for the offending query of running the design grid version and carrying on with the rest of my stuff in code. messy but it works until I can get a solution.

 

[zathrus777]

Annoyingly this works but I cannot find the correct objects to define it. I need to use option explicit and dont like not knowing how things work. I gather it is a DAO type as it is using * instead of % as a wildcard but am not sure

Dim dbs As Variant
Dim rscheap As Variant
Dim strselect As String
Set dbs = CurrentDb()
strselect = "select ID, a14 from felearnaim where (errors like '*""a14_*')"
Set rscheap = dbs.OpenRecordset(strselect)

any ideas?

 

[tstrike]

Are you looking for anything that has a14_ in it?

If so, try this
WHERE (((felearnaim.ERRORS) Like ""*a14_*"&quot)

I am not sure if it will work, but when I used the wild cards in a query, that is what comes up.