MS Query & Criteria

[Mark]

I'd like to use a cell reference containing a date from a
worksheet in my MS Query to filter the record set.

I'm receive a syntax error when specifying the cell in the
SQL query shown below:

WHERE (EXCEL_FOrwardPlanning.fStartDate<='Summary - Perth'!
B44) AND (EXCEL_FOrwardPlanning.fStopDate>={ts '2004-06-
21 00:00:00'})

Does MS query allow you to use data from worksheets as
part of the query criteria and if so, what would be the
correct format?

Many thanks in advance.

Mark

 

[K Dales]

I would guess the syntax error comes from trying to use
the entire range B44 as a date/time - don't quite
understand what you are attempting there, since you are
specifying multiple cells to return a single date/time
value?

To use a cell value as a parameter in MSQuery:
In the criteria grid, put the field(s) you wish to filter
by (i.e. fStartDate). As the criteria enter a named
parameter such as [StartDate] - you can use any name
enclosed in square brackets.

When you Return Data to Excel, it will ask you to enter
the value for StartDate - just enter anything at this
point. Then, when specifying where to put the query
results, press the button that says Parameters... and it
will show you the parameter "StartDate" and ask how you
wish to specify it: by using a constant value, prompting
the user, or using a cell value. Tell it to use the cell
value and specify the cell that contains your parameter
value (Still not sure what that value is: B44??? You
need a unique cell value).