Date functions in queries.

[ddmtn546]

I have a workorder database where: field1=start date & field2=end date. This work period could be from, say, 10/22/02 (field1) to 12/22/02 (field2). I am trying to run a report every day stating open and jobs. I am using in the query criteria field: Between Date() And DateAdd("d",30,Date()). The works fine provided the start date is the current date. If the actual start date (field1) is prior to the current date the query won't pick it up even though the job is still open. Help - how would I express the criteria to pick up all open jobs. Example - pick up: 10/22/02 thru 12/22/02 while todays date is 10/24/02. Thanks in advance.

 

[rac2]

I am thinking the end date is the date planned for the work to end. Both start date and end date have a value for the workorder when it is created. If so then the logic is

SELECT workorder info FROM workorder table
WHERE wo_start_date <= Date()
AND wo_end_date >= Date()

Work orders starting or ending today are included.


If the end date is not filled in until the work order is completed then the work orders open today are those without a value for the end date. So the logic is

SELECT workorder info FROM workorder table
WHERE wo_end_date IS NULL