Date Criteria Help (1 Viewer)

[lafrazia]

Hello everyone. I'm having a problem with a date criteria. I'm creating a query that returns one year of data from a specific date entered thru a prompt. This seems to work - >=#12/4/2021#-365 And <=#12/4/2021#. The issue is that the date will constantly change and I want to be able to enter the date thru a prompt. I've tried

>=Year([Enter Date])-1 And <=[Enter Date] as well but this only returns data from January 2020 instead of 12/4/2020 of the previous year. I need a full 365 days from the date. Any suggestions anyone?

 

[June7]

Advise not to use prompt as cannot validate input. User should enter criteria into controls on form and query references controls for dynamic parameter.

You can't just extract the year part and subtract 1. That provides only a year value for parameter. Use [Enter Date]-365 or DateAdd() function.

 

[theDBguy]

Hi. Welcome to AWF!

I agree about losing control when you use a parameter prompt. If you want a criterion to represent one year from today, you could try something like:

Between DateAdd("yyyy",-1,CDate([Enter Date])) And CDate([Enter Date])

Hope that helps...

Edit: Wasn't thinking straight. Fixed typo. Thanks to @June7 for the assist.

 

[lafrazia]

Thank you, but unfortunately its returning all data going back to 2015.

 

[theDBguy]

Thank you, but unfortunately its returning all data going back to 2015.

Can you post the SQL statement for your query?

 

[lafrazia]

This is the sql. The data it returns is accurate. Just looking to simplify so that when running for other individuals the date we need to do a 52 week lookback for a total gross can be done thru a prompt for the specific date.

SELECT pay_check.emplid, employees.first_name, employees.last_name, Sum(pay_check.total_gross) AS [52 Week's Cumulative Gross]
FROM pay_check INNER JOIN employees ON pay_check.emplid = employees.key2_emplid
WHERE (((pay_check.key4_pay_end_dt)>=#12/4/2021#-365 And (pay_check.key4_pay_end_dt)<=#12/4/2021#))
GROUP BY pay_check.emplid, employees.first_name, employees.last_name
HAVING (((pay_check.emplid)="12345"));

 

[jdraw]

Is pay_check.emplid numeric or alpha?

 

[June7]

Need DateAdd, not DateDiff.

 

[theDBguy]

Need DateAdd, not DateDiff.

Slap forehead!

 

[cheekybuddha]

You realise you are searching between 4th December 2021 and 4th December 2021 minus 365 days,

and not between 12th April 2021 and 12th April 2021 minus 365 days

?

If that's what you intend then ignore this post.

 

[lafrazia]

Is pay_check.emplid numeric or alpha?

This Field is numeric

 

[lafrazia]

You realise you are searching between 4th December 2021 and 4th December 2021 minus 365 days,

and not between 12th April 2021 and 12th April 2021 minus 365 days

?

If that's what you intend then ignore this post.

That's correct. I need the sum of gross from 12/4/20 thru 12/4/21 for this once instance and in others I will have different dates that I would like to enter total gross for the period entered.

 

[June7]

Slap forehead!

I made same mistake and same reaction as well and had to edit my earlier post.

 

[cheekybuddha]

I need the sum of gross from 12/4/20 thru 12/4/21

OK, no problem.

I read that as 12th April, but SQL reads that as 4th December.

 

[Pat Hartman]

The HAVING should be in the WHERE instead. The WHERE clause is applied BEFORE the data is aggregated and the HAVING is applied after. So the HAVING is applied to something that is aggregated such as a sum or an average. It is never applied to something that is grouped by or not even included in the Select clause. In this particular case, an index would be used to find the record you are looking for if you used the WHERE clause but by using the HAVING, you are forcing the query engine to aggregate first so it has to aggregate all the data and only then can it sift through all the rows to find the one you want.

The QBE makes this mistake easy to make because it always assumes a HAVING.