SELECT WHERE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I noticed that whenever I have a SELECT statement with a WHERE =
[Forms]![frmForm1]![txtInvoiceNo], it doesn't work. But if I point to the
actual numerical field instead of the textbox like
[Forms]![frmForm1]![lngInvoiceNo], it will work. I have tried with quotes and
doubl-quotes but to no avail. Am I missing something? Thanks.
ck
 
You need single quotes either side of the text string as below

SELECT ..... WHERE [FieldName] = '" & [Forms]![frmForm1]![txtInvoiceNo] &
"'"
 
Thanks Dennis, I've tried this but got a Data Type mismatch error. Is there
any difference if I'm doing this using the Design View of the Query Builder?
If I change it to SQL View it looks like this:

SELECT tblContSeal.lngContSealNo, tblContSeal.txtContainerNo AS [Container
No], tblContSeal.txtSealNo AS [Seal No], tblContSeal.txtContainerSize AS
[Container Size]
FROM tblContSeal
WHERE (((tblContSeal.lngInvoiceNo)=' "&
[Forms]![frmInvoiceEdit]![txtInvoiceNo] & " '));

ck

Dennis said:
You need single quotes either side of the text string as below

SELECT ..... WHERE [FieldName] = '" & [Forms]![frmForm1]![txtInvoiceNo] &
"'"

CK said:
I noticed that whenever I have a SELECT statement with a WHERE =
[Forms]![frmForm1]![txtInvoiceNo], it doesn't work. But if I point to the
actual numerical field instead of the textbox like
[Forms]![frmForm1]![lngInvoiceNo], it will work. I have tried with quotes and
doubl-quotes but to no avail. Am I missing something? Thanks.
ck
Click to expand...
Click to expand...
 
I think it is because your WHERE clause is comparing a numeric value field in
your table with a text value on your form

CK said:
Thanks Dennis, I've tried this but got a Data Type mismatch error. Is there
any difference if I'm doing this using the Design View of the Query Builder?
If I change it to SQL View it looks like this:

SELECT tblContSeal.lngContSealNo, tblContSeal.txtContainerNo AS [Container
No], tblContSeal.txtSealNo AS [Seal No], tblContSeal.txtContainerSize AS
[Container Size]
FROM tblContSeal
WHERE (((tblContSeal.lngInvoiceNo)=' "&
[Forms]![frmInvoiceEdit]![txtInvoiceNo] & " '));

ck

Dennis said:
You need single quotes either side of the text string as below

SELECT ..... WHERE [FieldName] = '" & [Forms]![frmForm1]![txtInvoiceNo] &
"'"

CK said:
I noticed that whenever I have a SELECT statement with a WHERE =
[Forms]![frmForm1]![txtInvoiceNo], it doesn't work. But if I point to the
actual numerical field instead of the textbox like
[Forms]![frmForm1]![lngInvoiceNo], it will work. I have tried with quotes and
doubl-quotes but to no avail. Am I missing something? Thanks.
ck
Click to expand...
Click to expand...
Click to expand...
 
Back
Top