SQL syntax query in VBA 2

[mje397]

Can I declare an SQL statement in a string declared as "strSQL" like this

strSQL= "Store='Store1'AND Product='HardDrive'"

I want to go to that location for which store is Store1 and Product = HardDrive. However its giving some error.

 

[EBGreen]

What is your actual code? It is a little unclear what you are trying to do. What is the error?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

However its giving some error
Could you by chance post the error message and the relevant code ?
Anyway your WHERE close snippet lack a space:
strSQL= "Store='Store1'[highlight] [/highlight]AND Product='HardDrive'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mje397]

This is my code


Dim strSQL As String

strSQL = "Store='Store1' AND Product='HardDrive'"

rst.Find strSQL

However when it comes to rst I am getting the following error

" Arguments are of wrong type, are out of acceptable range or are in conflict with each other"

Both Store and Product are in the same table.

 

[PHV]

So, in ADO the Find method is restricted to one criteria, use the Filter property instead:
rst.Filter = strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mje397]

hi PHV,, It worked,, but I had another statement in my coding.

rst.Find strSQL,1,adSearchForward

when I replaced it with

rst.Filter=strSQL,1,adSearchForward

its giving me an error.. Any idea how to go about !!!!!!!!!

 

[PHV]

Try simply this:
rst.Filter=strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mje397]

What I am trying to do is, Go to a particular record, ( by using rst.Filter=strSQL) do some calculations and then go to the next record.

when I was using FIND instead of FILTER , i was using the code

rst.Find strSQL,1,adSearchForward

How will I traverse from one record to next when I use FILTER?

 

[PHV]

You have to play with the Bookmark property (and the F1 key I'm afraid ;-))
Sometimes Access don't react well on help demands about DAO and/or ADO stuff.
I suggest you to search your local drives for files named ADO*.CHM.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244