SELECT WHERE

[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

 

[Guest]

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

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

 

[Guest]

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

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

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

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

 

[Guest]

I think it is because your WHERE clause is comparing a numeric value field in
your table with a text value on your form

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

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

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

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

 

[Guest]

Thanks.
ck