Date Criteria Help

[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.