VBA SQL Query

[oli82]

Morning,

I have been struggling to get this going for a couple of weeks now. Could you please take a look at let me know where this code is going wrong. Many many thanks

Q.SQL = "Select * From [Q_SeqStrat1] Where [Country] in ("Egypt",) And [user] In ("Depth",) & ";"

Basically it comes up with the error cant find object or expected end of statement.

Any help would be really useful.

Many Thanks.

Oliver

 

[namliam]

Using quotes inside strings... this is one mess... something like so...

Q.SQL = "Select * From [Q_SeqStrat1] Where [Country] in ('Egypt') And [user] In ('Depth') ;"

but without the complete code of what you are trying to do.... Guarantees and null and void...

 

[oli82]

sorry more help needed

Thanks for the fast reply.

This is great and works fine until I try to call this from a text field on the form.

I was trying to get it like this:

Q.SQL = "Select * From Q_SeqStrat1 Where & Text1 & Text2 & ";"

Text 1 = [Country] in ('Egypt','Spain',)
Text 2 = AND [Userfield4] In ('Depth','Interval')

This comes up with errors I really cant understand why. Any help would be really appreciated.

 

[Rabbie]

Try some simple diagnostics to see what is happening.

try some thing like
Dim strSQL as string
strSQL = "Select * From Q_SeqStrat1 Where & Text1 & Text2 & ";"
msgbox(strSQL) ' this will display your actual string
Q.SQL = strSQL


Hope this helps

 

[gemma-the-husky]

you have a superfluous comma at the end of spain - is it just that?
you also need a space after SPAIN and before AND

sql needs everything spaced

 

[namliam]

you have a superfluous comma at the end of spain - is it just that?
you also need a space after SPAIN and before AND

sql needs everything spaced

Not quite gemma , you are spot on about the extra comma after spain, should not be there

The space tho is needed inbetween the closing bracket and the and... like so:
"Select * From Q_SeqStrat1 Where & Text1 & " " & Text2 & ";"

 

[oli82]

Sorry,

I get an expected end of line error after the code:

"Select * From Q_SeqStrat1 Where & Text1 & " " & Text2 & ";"

What do I need to do?
Thanks for the help.

 

[oli82]

Thanks

Thank you all for your help.

The end product is below. The trailing , does not seem to affect the SQL code.

Cheers,

Dim Q As QueryDef, DB As Database
Dim Text1 As String
Dim Text2 As String
Dim Text3 As String
Dim Text4 As String

Text3 = Me![TextSeqCountry]
Text4 = Me![TextseqDI]

Text1 = "[country] IN" & " " & Text3
Text2 = "AND [userfield4] IN" & " " & Text4

Set DB = CurrentDb()
Set Q = DB.QueryDefs("Q_SeqStrat2")


Q.SQL = "Select * From Q_SeqStrat1 Where" & " " & Text1 & " " & Text2 & "ORDER BY [country];"

Q.Close

DoCmd.OpenQuery "Q_SeqStrat2"

 

[gemma-the-husky]

true

Text 1 = [Country] in ('Egypt','Spain',)
Text 2 = AND [Userfield4] In ('Depth','Interval')

[Country] in ('Egypt','Spain',) AND [Userfield4] In ('Depth','Interval')

true - i just meant you needed a space before the AND - i forgot about the close bracket

 

[namliam]

Thank you all for your help.

That shouldnt work tho...


IN requires () if there are multiple options... you are doing an =

The "Text2" and "Order" will be concatinated, resulting in an error.

You need to Disambiguate
Dim Q As QueryDef, DB As Database
to
Dim Q As DAO.QueryDef, DB As DAO.Database

Do a search on disambiguate and read up, prevent a lot of different future errors.

 

[oli82]

Thanks will do.