Selecting multiple options

[Turb]

All,
I have to create a query(?) that will export to an Excel spreadsheet, but I'm not sure how to set it up to filter by the following criteria:
The query has to take multiple user selectable options including a 'to-from' date range, a null/not null/all selection (based on a table field), and a selectable field (all or selectable by the table values) based on a combo-box table and the query must be able to be started from it's own form or command button on a start form.
Can someone help?

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[SeeThru]

Build an UNBOUND form.

Put the combo box and various controls on the form - the combo boxes can have row sources from your table, but should not be bound to it.


Put a command button on the form. Now the tricky bit.

In the command buttons event, you have to build the SQL string manually. Use query builder to help you to build the query string

ie

Button1_click
dim strSQL as string
dim strWHERE as string

strsql="SELECT * FROM MYTABLE"

now, use your options boxes to build the WERE part of the string.

if not isnull(option1) then strWHERE=strWHERE & "
AND MyRef=" & me!option1

if not isnull(startdate) and Not isnull(enddate) then strWHERE=strWHERE & "AND mydate between #" & startdate & "# AND #" & enddate & "#"

and so on. You cna also add error checking to the code, so if something is missing out outside the expected value it stops and given an error.

Now, remove the extra leading and, and stick the two parts together with WHERE keyword. Yuo may want to check there is at least something in the strWHERE.

strWHERE=mid(strwhere,5)
strSQL=strSQL & " WHERE " & strSQL

Now, you have a SQL string, and can use various commands to bring it up on screen, save it as a table or erport. I thing for Excel you want docmd.transferspreadsheet()

 

[Turb]

SeeThru,
Not sure I'm doing this right.
It doesn't seem to be working for me.

I should mention that the combobox is reading (but not bound to) one table (this is the selectable field by all or table values combobox), the to-from date range is from a single column on another table, the null/not null/all selection if from the same table (as the item prior) but from a different column.

Does this help?