OK this one killed me

[gblack]

Can someone explain why this is evaluating as having no records when I actually print the strSQL and run it, it produces several records.

strSQL = "SELECT STOREROOM, NSN, [NCF LIFE EXP], SumOfQTY, REGISTERED, STATUS_TYPE, " & _
"NSN_TYPE, Found_In, OA, BP_Position " & _
"FROM 10_POINVASS " & _
"WHERE NSN_TYPE Like '*P*' " & _
"AND STATUS_TYPE = 'FNOO' " & _
"AND STOREROOM = '" & strSTOREROOM & "' " & _
"ORDER BY NSN, OA ;"

With rsFROM
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open (strSQL)
End With


' MsgBox (rsFROM.RecordCount)
If rsFROM.RecordCount <> 0 Then
'something cool
Else
'Uber lame
End If

The problem is with the WHERE Clause, if I change it to this:

"WHERE Instr(NSN_TYPE, 'P') > 0 " & _

It works as intended... But I was having a serious issue with using double quotes and such. Since using this laptop I haven't been able to use the ASCII to get double quotes, like I have always done (e.g., & Chr(34) &, ends up yeilding two sets of double quotes in the string... seems that single quotes are the only way I can do this now...

Also doing something like:

"WHERE NSN_TYPE Like " & chr(34) & Chr(42) & "P" & Chr(42) & Chr(34) & " " & _

Ends up evaluating as:
WHERE NSN_TYPE Like ""*"P"*"""
Or some such craziness... I have been dealing with SQL strings for well over a decade and all of a sudden I am having issues with this... what is going on?

Why won't the original where clause evaluate as something cool... instead of something Uber lame?

V/R,
-Gary

 

[gemma-the-husky]

the ascii thing you mention is strange

what do you see if you just do this

msgbox(chr(34))

 

[WayneRyan]

Gary,

If it's ADO ...

"WHERE NSN_TYPE Like '%P%' "

hth,
Wayne

 

[gblack]

Dave: I simply get one set of double quotes like I am supposed to... (i.e. "), but for some reason when I set a string variable equal to something with the ASCII it adds an extra set of double quotes and fails... if I take the ASCII away it adds no double quotes at all and fails. I have no idea why and it's driving me crazy!

Wayne... thanks... that very well may be why it wasn't working in VBA but was in the GUI... I already changed my code to the instr() method and moved on, ofr times' sake... but when I have time (*IF*) I will try to go back and test that. Thanks though it is definitely something to keep in mind for future reference.