help with DateAdd function

[icyrius]

I populated a criteria using the wizard to filter a query using a date range based on a date from a table, but it is not working.

Between DateAdd("d",+1,[tbl_OZ]![pilotDate]) And DateAdd("d",+2,[tbl_OZ]![pilotDate])

if I type the same formula using dates in this format #02/03/2016# then I get data.

What am I doing wrong? Thanks for your help.

 

[Norie]

Does the pilotDate field include time as well as date?

 

[icyrius]

Data is being pulled from two tables. Let me list the inputs the same way as Access,
Field: Date_from_Table2 <---- Includes time
Table: Table2
Sort: ascending
Show: checked
Criteria: Between DateAdd("d",+1,[table1]![pilotDate]) And DateAdd("d",+2,[table1]![pilotDate]) <---- does not include time

 

[bobsan42]

Is this used in a Query? Can you post the SQL string of the Query?
Is table1 (or tbl_OZ) part of this query? Are all links established?

Try to add the two DateAdd functions as Fields in the Query and check the result. And maybe add [table1]![pilotDate] as a Field.

 

[icyrius]

1. Is this used in a Query? Yes.
2. Can you post the SQL string of the Query?

SELECT SALES.Facility_ID
    , [table1].pilotDate
    , SALES.Sales_Trans_NO
    , SALES.Sales_Trans_TM
FROM [table1] INNER JOIN SALES ON ([table1].pilotDate = SALES.Sales_Trans_DT) AND ([table1].StoreID = SALES.Location_ID)
WHERE (((SALES.Location_ID)=11) 
AND ((SALES.Sales_Trans_NO) Between DateAdd("d",+1,[table1]![pilotDate]) And DateAdd("d",+2,[table1]![pilotDate])));

3. Is table1 (or tbl_OZ) part of this query? Yes.
4. Are all links established? Yes.

5. Try to add the two DateAdd functions as Fields in the Query and check the result. Most functions work OK when used in fields. I am trying to use DateAdd inside the criteria. I works fine when not referencing a table/date.

6. And maybe add [table1]![pilotDate] as a Field. I tried this already and still does not work.

 

[Micron]

If you can get results from something like Between #02/15/2016# And #03/01/2016
I'd say either the DateAdd function is not returning valid dates, the values being passed to it are not suitable, or it doesn't like your use of the bang operator (!).

If this is of no help

Between DateAdd("d",+1,[table1][COLOR=#ff0000][B].[/B][/COLOR][pilotDate]) And DateAdd("d",+2,[table1][B][COLOR=#ff0000].[/COLOR][/B][pilotDate])));

I would try variations of your query (in a copy of it) and include pilotDate as a field with no criteria. If you get records, ensure there are valid dates in this field (not NULL) and are formatted suitably for your system date/time settings. Then I'd add two more calculated fields to the new query to see what the function does to those valid dates:

Exp1: DateAdd("d",+1,[table1].[pilotDate]

and

Exp2:DateAdd("d",+2,[table1].[pilotDate]

and check the results. I don't think the fact that the field contains time is of any consequence for your issue, given that a static date works for you. However, you should know that using Between on date fields with time introduces a problem. When no time is provided, the second date cutoff point is midnight. That is, you will get no records for 1:00 AM (for example) for the second date. To compensate for this, one way is to DateAdd 23 hours and 59 minutes to your second date.