Query with spaces

[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

 

[Allen Browne]

Try without the:
.[Value]
in each case.

 

[de Vroede]

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.

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"

 

[Allen Browne]

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