if statement with sql

[Guest]

I want to do a something like:

if rs.Open "SELECT PRIMARY_KEY FROM TEST WHERE PRIMARY_KEY LIKE '" & _
variable & "%'" then

....

First, when I try this I have to put the sql statement in () or I cannot
execute. Second, this tends to always return Empty. Am I doing this right?
The connection to the database is established and rs.activeconnection=cn
declared. Thank you.

Brent

 

[Tim]

What are you trying to test? If there are any records with keys like the
variable value ?

Something like this:

'*******************************
rs.Open "SELECT PRIMARY_KEY FROM TEST WHERE PRIMARY_KEY LIKE '" & _
variable & "%'"

if not rs.eof then
'have matches - maybe process the records here
else
'no matches
end if
'******************************

or if you just want a count of matches

'*****************************
rs.Open "SELECT count(PRIMARY_KEY) as numrecs FROM TEST WHERE PRIMARY_KEY
LIKE '" & _
variable & "%'"

msgbox rs("numrecs").value
'*****************************


Tim

 

[Guest]

I am just trying to see if the record already exists. I am uploading files
that are updated weekly, but all the files for a year are put in one folder.
I previously had an if statement with the EOF test, but figured there would
be a way to use the sql select directly in the if statement test. Thank you!!

Brent