Between function 1

[BrockLanders]

Hi, I have a query with a date field and want to add criteria to only return records between certain dates. In the criteria section of the query for the date field I have Between [Enter a beginning date] And [Enter a ending date], which brings up two input boxes asking the user for each date. This works fine, but I'd like to take it one step further. The ending date is always five days after the beginning date so I'd like to include this logic somehow and only have to type the beginning date. I tried: Between [Enter a beginning date] And [Enter a beginning date]+5, but Access gives an error that the expression is too complex.

Any ideas how to accomplish this?

Thanks in advance for any help.

 

[PHV]

In the SQL pane:
SELECT ...
WHERE yourDateField - [Enter a beginning date] Between 0 And 5 ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrockLanders]

Below is my SQL, but I'm getting the same error, is there something wrong with my syntax? This is a find unmatched query.

SELECT [2005 Inv Sched].[Store #], [2005 Inv Sched].Date,
FROM [2005 Inv Sched] LEFT JOIN [Sched RGIS] ON [2005 Inv Sched].[Store #] = [Sched RGIS].[Store #]
WHERE ((([2005 Inv Sched].Date) - [Enter a beginning date] Between 0 And 5) AND (([Sched RGIS].[Store #]) Is Null));

 

[PHV]

And this ?
WHERE DateDiff("d",[Enter a beginning date],[2005 Inv Sched].Date) Between 0 And 5 AND [Sched RGIS].[Store #] Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrockLanders]

That worked perfect!! Thank PHV, have a star.