"Stupid" Select Statement

  • Thread starter Thread starter David Jones
  • Start date Start date
D

David Jones

Attempting to get this syntax correct:

strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & Me.Barcode_Number And "Field49 = '" &
IR & " '"

I am attempting to use the two pieces of info on a form
(1) a integer field on a form and (2) a string value on
the form. But, this damn syntax is killing me. Can
someone help in getting this statement syntax correct.
Would appreciate it.

Thanks
DJ
 
Syntax error at And
strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & Me.Barcode_Number And "Field49 = '" &
IR & " '" ^
Click to expand...

Should be
strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & Me.Barcode_Number & " And Field49 = '" &
IR & " '"

Alex.
 
Note you could also use DLookup(). DLookup() is often easier for quick &
dirty checks. No need to worry with database references, recordset objects &
so on.

HTH,
TC
 
Alex, your recommendation does not work. I think we are
both close but not completely. I get an error when I use:

strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & Me.Barcode_Number & " And Field49 = '"
& IR & " '"


-----Original Message-----
Syntax error at And
Click to expand...
& " '"
^
 
What error? I don't know the data types of your fields, assuing you have
correct quoting around Field48 and Field49. The query will crash if
Me.Barcode_Number is Null or an empty string, the rest seems ok assuming
Field48 is numeric and Field49 is a string. There is an extra space after IR
and before the closing quote, but that should not matter.

Alex.
 
TC said:
Note you could also use DLookup(). DLookup() is often easier for quick &
dirty checks. No need to worry with database references, recordset objects &
so on.
Click to expand...
DCount in this case, I think.
 
Try creating the query using the QBE grid and check the resulting
syntax. Simulate that with code.

I tried your statement and I get no error.
 
DJ said:
Alex, your recommendation does not work. I think we are
both close but not completely. I get an error when I use:

strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & Me.Barcode_Number & " And Field49 = '"
& IR & " '"
Click to expand...

I found that being overly precise sometimes helps keep me straight.
strSQL = "SELECT count(*) As MyCount FROM Registration
where Field48 = " & cstr(Me.Barcode_Number.Value) & " And Field49 = '"
& IR & "';" (<-- you had an extra space before the trailing appostrophe,
and that counts here)
(also, of IR is another form control, then I'd use: cstr(MeIR.Value) there).
 
Back
Top