Parameter Queries 1

[cpmasesa]

Hello ALL,

I would like to write a query that will return records within a given date range (simple enough) BUT the date field queried varies!!

My Table has oDate, tCompleted, tDelivered, aCompleted and aDelivered as Date fields

To return records where oDate is btn two dates i have the following sql:
SELECT * FROM Orders
WHERE oDate >=[DateFrom] AND oDate <=[DateTo]
ORDER BY OrderNo

If i am interested in the other date fields i have to write EXACTLY the same sql only replacing 'oDate' with the other date fields.

Problem: Is it possible to pass the date fields (oDate, tCompleted, tDelivered, aCompleted) as parameters and hence use only one sql?? if so, how??

TIA

cpmasesa

 

[cpmasesa]

Additional information:

i tried:
PARAMETERS [sDateField] VARCHAR;
SELECT * FROM Orders
WHERE [sDateField] >=[DateFrom] AND [sDateField] <=[DateTo]
ORDER BY OrderNo

but it returns 0 records no matter what date range i use!!

i tried:
PARAMETERS [sDateField] TEXT, [DateFrom] DATETIME, [DateTo] DATETIME;
SELECT * FROM Orders
WHERE [sDateField] >=[DateFrom] AND [sDateField] <=[DateTo]
ORDER BY OrderNo

and it gives me an error.

Am stumped, or maybe what i want to do is not possible??!

By the way, i do not want to do it through VB code!! bcs am using MS Access as backend for my DB App and i want to pass the parameters from within my app (not VB app!)

 

[sanders720]

How about something like this...

Timecards is the table

Employee Number, Job Number, Order Number and Op Number are fields.

Date is another field, and the WHERE locks the information between two dates. I have used variables to define desire date ranges.

Another note, select distinct does not produce duplicate information, as in this application I am looking only for one set of data, even though it may exist several times. I have excluded the date from the selection because that would make the data non-distinct.

sql = &quot;SELECT DISTINCT &quot; & _
&quot;TimeCards.[Employee Number], TimeCards.[Job Number], TimeCards.[Order Number], TimeCards.[Op Number] &quot; & _
&quot;FROM TimeCards &quot; & _
&quot;WHERE (((Timecards.Date)>=#&quot; & SelectedDate & &quot;# AND (Timecards.Date)<=#&quot; & SelectedDateEnd & &quot;#) AND ((TimeCards.[Employee Number])=&quot; & EmployeeNumber & &quot)&quot;

 

[cpmasesa]

Well its not exactly what i wanted, i want to pass the date field, 'TimeCards.Date', as a parameter/variable.

Like instead of :
....&quot;WHERE (((Timecards.Date)>=#&quot; & SelectedDate & &quot;# AND (Timecards.Date)<=#&quot; & SelectedDateEnd & &quot;#) AND ((TimeCards.[Employee Number])=&quot; & EmployeeNumber & &quot)&quot;

it should be:
...&quot;WHERE ((SelectedDateField>=#&quot; & SelectedDate & &quot;# AND SelectedDateField<=#&quot; & SelectedDateEnd & &quot;#) AND ((TimeCards.[Employee Number])=&quot; & EmployeeNumber & &quot)&quot;

i.e. i need to replace 'Timecards.Date' with a parameter/variable 'SelectedDateField'.

Any help appreciated.