MSQuery Parameter in WHERE defaulting to All

[AFSSkier]

I need an Excel parameter prompt where the user can get a list of ALL items
or only the KVI items.

I've tried WHERE Unit = [Enter Unit] OR [Enter Unit] IS NULL. It works
great in Access, but I get a syntax error in MSQuery. I tried using a single
prompt in MSQuery, but when I input % wild card only brings back the K items
& non of the nulls. Leaving blank in Excel returns no records.

The following is a test SQL that works in MSAccess, but not MSQuery for
Excel. The field kvi in the table has K or nulls.
SELECT DISTINCT a1.item_id, a1.kvi_id
FROM p_item AS a1
WHERE (a1.kvi_id Like [Enter "K" for KVI only]) OR ([Enter "K" for KVI only]
Is Null)
ORDER BY a1.kvi_id;

 

[Joel]

I suspect you need a carriage return and linefeed in the SQL. See my
changes below

MySQL = "SELECT DISTINCT a1.item_id, a1.kvi_id" & vbCrLf & _
"FROM p_item AS a1" & vbCrLf & _
"WHERE (a1.kvi_id Like [Enter "K" for KVI only]) OR " & _
"([Enter "K" for KVI only] Is Null)" & vbCrLf & _
"ORDER BY a1.kvi_id;"


or soimetimes I like doing this
MySelect = "SELECT DISTINCT a1.item_id, a1.kvi_id"
MyFrom = "FROM p_item AS a1"
MyWhere = "WHERE (a1.kvi_id Like [Enter "K" for KVI only]) OR " & _
"([Enter "K" for KVI only] Is Null)"
MyOrder = "ORDER BY a1.kvi_id;"
MySQL = MySelect & vbCrLf & Myfrom & vbCrLf & Mywhere & vbCrLf & MyOrder


Two tricks I use.

1) Record a macro while performing a query manually. Then use the recorded
macro as part of a longer macro.
2) there is a query editor that you can use. Create a simple query
without the where or order section. then click on the returned table. In
2003 you then go to the menu
Data - Import Data - Import External data. There is an edit query option
that becomes active if you select any cell in the returned query. there is a
SQL box in the query editor that allows you to change the query in real time
and will let you know if your syntac is correct. You will see that the Where
is on its own line in the SQL box which is equivalent to the vbCrLF in my
modifications.