Issues with Quotes

S

Sash

DebtID = rs4.Fields("PRIMARY_Debtor_ID")
DebtNo = rs4.Fields("Debt_No")

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & DebtID & "" And
[DEBT_NO] = "" & DebtNo & """"
Set rs2 = db.OpenRecordset(strSQL2, dbOpenDynaset, dbSeeChanges)


I'm trying to create a recordset using the above and know I have the quotes
wrong. The DEBT_ID and DEBT_NO are all numbers. I just can't seem to get a
handle on the quotes. Any help would be greatly appreciated.
 
D

Douglas J. Steele

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & DebtID & " And
[DEBT_NO] = " & DebtNo
 
J

John W. Vinson

DebtID = rs4.Fields("PRIMARY_Debtor_ID")
DebtNo = rs4.Fields("Debt_No")

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & DebtID & "" And
[DEBT_NO] = "" & DebtNo & """"
Set rs2 = db.OpenRecordset(strSQL2, dbOpenDynaset, dbSeeChanges)


I'm trying to create a recordset using the above and know I have the quotes
wrong. The DEBT_ID and DEBT_NO are all numbers. I just can't seem to get a
handle on the quotes. Any help would be greatly appreciated.
Click to expand...

You need to delimit criteria with quotes if the field being searched is of
Text type. Number fields don't get any quotemark delimiters. You're just
making your own job harder!

Try just concatentating literal strings (delimited by " marks) and VBA
variables into one string:

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & DebtID & " And
[DEBT_NO] = " & DebtNo
 
S

Stuart McCall

Sash said:
DebtID = rs4.Fields("PRIMARY_Debtor_ID")
DebtNo = rs4.Fields("Debt_No")

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & DebtID & "" And
[DEBT_NO] = "" & DebtNo & """"
Set rs2 = db.OpenRecordset(strSQL2, dbOpenDynaset, dbSeeChanges)


I'm trying to create a recordset using the above and know I have the
quotes
wrong. The DEBT_ID and DEBT_NO are all numbers. I just can't seem to get
a
handle on the quotes. Any help would be greatly appreciated.
Click to expand...

Try grabbing this function:

http://www.smccall.demon.co.uk/Windows.htm#CriteriaValue

Then use it like this:

strSQL2 = "SELECT * from DEBT_PMT where [DEBT_ID] = " & Cv(DebtID) & " And
[DEBT_NO] = Cv(DebtNo)

The function will determine the data type of the passed value and quote it
accordingly.
 
D

Douglas J. Steele

Nice function, Stuart, but it won't work in cases where a numeric value is
being stored in a text field (such as a telephone number or a SSN)
 
S

Stuart McCall

Douglas J. Steele said:
Nice function, Stuart, but it won't work in cases where a numeric value is
being stored in a text field (such as a telephone number or a SSN)
Click to expand...

Hmm. I've been using this for a couple of years with no problem. Maybe I've
just been lucky. Thanks for the heads-up and I'll withdraw it from my site
while I look into it.
 
Top