"Stupid" Select Statement

[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

 

[Alex Ivanov]

Syntax error at And

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

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

Alex.

 

[TC]

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

 

[DJ]

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

& " '"
^

 

[Alex Ivanov]

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.

 

[Bas Cost Budde]

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.

DCount in this case, I think.

 

[Bas Cost Budde]

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.

 

[Mark Burns]

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 & " '"

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).

 

[TC]

DCount in this case, I think.

Oops! Quite right.

TC