Using DatePicker reference in Query

[RomeERome]

I am using a datepicker on a form, and when I reference that datepicker field in a query to help filter dates, my results are not filtered correctly.

The syntax in my query is as follows:

Between Forms!frmProjections!DTPicker3 And Forms!frmProjections!Text232

The code above is under the Criteria area of the QBE screen.

The begin date that is chosen on my datepicker is 4/29/2015, and the end date that is in Text232 = 11/27/2015.

The data in the Text232 field is calculated based on weeks being added to the datepicker date by using the DateAdd() function.

DateAdd("ww", Forms!frmProjections!DTPicker3, Forms!frmProjections!Text232)

The calculation of the date is correct, but the filtering of the dates is what is incorrect.

To my knowledge, this shouldn't have any barring on the outcome of the query, but it does.

Thanks

 

[dhookom]

I don't think you understand DateAdd(). The second argument should be an integer like 1, 2, 3, ... The final argument is a date value.

Duane
Hook'D on Access
MS Access MVP

 

[RomeERome]

My mistake!! The DateAdd() function has syntax that looks as follows:

DateAdd("ww",Forms!frmProjections!Text147,Forms!frmProjections!DTPicker3)

The Text147 field is the number of weeks that need to be calculated.

That was an error, but that's not my real issue. The real issue is that the query does not filter properly when using the datepicker in the criteria section of the QBE.

Thanks.

 

[dhookom]

Can you explain "does not filter properly"? We can't see your data or your results.

Can you provide the full SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[RomeERome]

Hey Duane,

Thanks for your help.

Here is what the SQL looks like.

  SELECT ([polnenddue]+30)-Weekday([polnenddue]+30,1)+6 AS [Due Date], 
[indent][indent][indent]polin001.polnenddue,[/indent][/indent][/indent] 
[indent][indent][indent][polnenddue]+30 AS [Date],[/indent][/indent][/indent] 
[indent][indent][indent]Sum([recqty]*[polnlp]) AS Cost[/indent][/indent][/indent] 
    FROM polin001
   WHERE polin001.polnsoln)="X" 
     AND polin001.shpqty)>0 
     AND polin001.recqty)>0 
     AND [polnenddue]+30) [b]Between Format([Forms]![frmProjections]![DTPicker3],"m/d/yyyy") And [Forms]![frmProjections]![Text232][/b]))
GROUP BY ([polnenddue]+30)-Weekday([polnenddue]+30,1)+6, polin001.polnenddue, [polnenddue]+30;

The date that's in the datepicker = 4/29/2015, and the calculated date that is in text232 = 11/27/2015.

The results that I receive do not fall within that date range. The results come back with a due date range of 2/11/2000 to 5/1/2015.

Below is a sample of the data.

Due Date polnenddue Date Cost
2/11/2000 1/10/2000 2/9/2000 26,593.42
11/3/2000 10/4/2000 11/3/2000 28,764.45
11/3/2000 10/5/2000 11/4/2000 11,660.00
11/10/2000 10/8/2000 11/7/2000 103,000.25
11/10/2000 10/10/2000 11/9/2000 99,343.00
12/1/2000 10/29/2000 11/28/2000 21,801.10
...
...
...
4/17/2015 3/16/2015 4/15/2015 29,985.00
4/17/2015 3/18/2015 4/17/2015 7,234.00
4/17/2015 3/19/2015 4/18/2015 135,447.54
4/24/2015 3/21/2015 4/20/2015 12,822.00
4/24/2015 3/22/2015 4/21/2015 81,887.60
4/24/2015 3/24/2015 4/23/2015 26,680.00
4/24/2015 3/25/2015 4/24/2015 20,830.50
5/1/2015 3/30/2015 4/29/2015 7,500.00

The reason the date range is correct is because the data is being forecasted out for 30 weeks from the datepicker date.

So if you are concerned about the date range, it is the correct range.

Now you see my problem.

Thanks in advance for your help.

 

[Andrzejek]

I assume your field [TT]polnenddue[/TT] is declared as Date/Time
[tt]
& " AND [polnenddue]+30) Between #" & _
Format([Forms]![frmProjections]![DTPicker3],"m/d/yyyy") & "# And #" & _
[Forms]![frmProjections]![Text232])) & "#"
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

What do you get when you replace the control references with hard-coded date values? I'm not sure why you use the Format() function on one date and not the other. Format() typically converts a number to a string which is not what you want.

Duane
Hook'D on Access
MS Access MVP