Query with spaces

  • Thread starter Thread starter de Vroede
  • Start date Start date
D

de Vroede

Hi,

I'm trying to build a query which combines multiple fields and searches
for this field in another field. When I build it using a query design
it works, but I want to use it for a RecordCount and making it into a
SQL statement doesn't seem to work. This is what I got:

"SELECT Tbl_HWcollection.Omschrijving FROM Tbl_HWcollection WHERE
(([Tbl_HWcollection]![Omschrijving] =
[Forms]![Frm_HWDescription]![Combo6].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo8].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo10].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo16].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo14].[Value]))"

When I run it I get an error message stating "Expected: end of
Statement"

regards,

Jeroen
 
Hi,

Tried it, but still getting the error.

regards,

Jeroen


Allen Browne schreef:
Try without the:
.[Value]
in each case.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

de Vroede said:
I'm trying to build a query which combines multiple fields and searches
for this field in another field. When I build it using a query design
it works, but I want to use it for a RecordCount and making it into a
SQL statement doesn't seem to work. This is what I got:

"SELECT Tbl_HWcollection.Omschrijving FROM Tbl_HWcollection WHERE
(([Tbl_HWcollection]![Omschrijving] =
[Forms]![Frm_HWDescription]![Combo6].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo8].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo10].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo16].[Value] & " " &
[Forms]![Frm_HWDescription]![Combo14].[Value]))"

When I run it I get an error message stating "Expected: end of
Statement"
Click to expand...
Click to expand...
 
Just noticed you have quotes around this, which suggests the context is VBA.

The quote within quotes will need to be doubled, you you probably need to
concatenate the values into the string and add the correct delimiters.

"SELECT Tbl_HWcollection.Omschrijving FROM Tbl_HWcollection " & _
"WHERE [Tbl_HWcollection]![Omschrijving] = """ & _
[Forms]![Frm_HWDescription]![Combo6] & " " & _
[Forms]![Frm_HWDescription]![Combo8] & " " & _
[Forms]![Frm_HWDescription]![Combo10] & " " & _
[Forms]![Frm_HWDescription]![Combo16] & " " & _
[Forms]![Frm_HWDescription]![Combo14] & """;"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
 
Back
Top