VBA SQL Query

oli82

Registered User.
Local time
Today, 23:14
Joined
Jan 10, 2008
Messages
54
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
 
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...
 
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.
 
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
 
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
 
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 & ";"
 
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.
 
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"
 
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom