Between Date() and Date()-6 is not working in criteria

[MFS]

For some reason or another this basic code is not working for me [Between Date() and Date()-6] in my query criteria. (Looking to review all data from todays date back 6 days.


If I type [#8/16/2016#] the database returns all data for today, I just cannot get the system to show me all data for today and last six days.
What am I doing wrong?

[JoeM]

Don't place it in between square brackets. You use square brackets when you want to prompt the user for criteria at run-time. Just use:
Between Date() and Date()-6
in your criteria

[MFS]

Sorry, I'm not using the square brackets, I used them on the forum for the purpose of placing code.

[JoeM]

Your code works for me, exactly as how you have presented it.

A few things to check:
- Are the fields you checking really date fields (that is, do they have the Date Data Type)?
- Are their matching records that should be returned?
- Do you have other criteria that might be filtering the records out?

If you cannot figure it out, please post the SQL code of your query for us to analyze (change your query to SQL View and copy and paste code here).

[ranman256]

That's not how you do date math.
use: DateAdd("d",6,date())

[JoeM]

That's not how you do date math.
use: DateAdd("d",6,date())

Date()-6 works just for me, as it should.
Excel and Access store dates as the number of days since 1/1/1900. So dates are whole numbers. So subtracting a number like that works just fine.

If you don't believe, try entering this calculated field in a query and see what it returns.

MyDateCalc: Date() - 6

[MFS]

See attachment.
I have tried both of your suggestions without success. DateAdd("d",6,date()) and Date() - 6

[JoeM]

Please post your SQL code.
Can you also post a small sample of your data?

[MFS]

Code:

[INSERT INTO dbo_OT_HOURS ( Clock_Number, OT_Offered, Employee_Name, OT_Hours, Coordinator_Name, Period_End )
SELECT Employees.Clock_Number, Timesheet.OT_Offered, [Employees].[Name_First] & " " & [Employees].[Name_Last] AS Employee_Name, Timesheet.Hours_OT, [Coordinators].[Name_First] & " " & [Coordinators].[Name_Last] AS Coordinator_Name, Timesheet.Period_End
FROM (Employees INNER JOIN Timesheet ON Employees.Employee_ID = Timesheet.Employee_ID) INNER JOIN Coordinators ON Employees.Coordinator_Id = Coordinators.Coordinator_ID
GROUP BY Employees.Clock_Number, Timesheet.OT_Offered, [Employees].[Name_First] & " " & [Employees].[Name_Last], Timesheet.Hours_OT, [Coordinators].[Name_First] & " " & [Coordinators].[Name_Last], Timesheet.Period_End, Coordinators.Coordinator_ID, Timesheet.Period_Start
HAVING (((Timesheet.Period_End)=Date()-6) AND ((Coordinators.Coordinator_ID)=2));]



Data Example:

Employee_Name	Coordinator_Name	OT_Offered	OT_Hours	Clock_Number	Period_end
David Smith	Mike Speck	19	19	16	2016-08-07
Carl Snyder	Mike Speck	0	0	48	2016-08-07
Troy Wix	Mike Speck	3	3	455	2016-08-07
Frederick Smith	Mike Speck	16	0	719	2016-08-07
Jeffrey Roberts	Mike Speck	7	7	764	2016-08-07
David Smith	Mike Speck	19	19	16	2016-08-08
Carl Snyder	Mike Speck	0	0	48	2016-08-08
Troy Wix	Mike Speck	3	3	455	2016-08-08
Frederick Smith	Mike Speck	16	0	719	2016-08-08
Jeffrey Roberts	Mike Speck	7	7	764	2016-08-08
David Smith	Mike Speck	19	19	16	2016-08-09
Carl Snyder	Mike Speck	0	0	48	2016-08-09
Troy Wix	Mike Speck	3	3	455	2016-08-09
Frederick Smith	Mike Speck	16	0	719	2016-08-09
Jeffrey Roberts	Mike Speck	7	7	764	2016-08-09

[JoeM]

HAVING (((Timesheet.Period_End)=Date()-6) AND ((Coordinators.Coordinator_ID)=2));]

A few things:
- Why aren't you using the BETWEEN logic in your Criteria (as shown in post #2)? You have it set to only check for dates EXACTLY equal to 6 days prior to today (2016-08-10)?
- It doesn't appear that any data in your example meets the logic anyway. You have nothing later than 2016-08-09
- Note your other criteria also, that Coordinator_ID must be equal to 2. You did not show that field in your data example, so I am not sure if any of these sample records meet that criteria.

[MFS]

JoeM,

Answers to your questions . . .
The BETWEEN logic does not work as explained, In the current example, =Date()-6, is just something I tried to see if the Query responded to any date function criteria.
The data I provided is just an example, I have dates in the table for pretty much every day of the year.
There is data for Coordinator_Id =2. In other words if I simply use the criteria just for Coordinator 2 it gives me years and years worth of data. Which leads me to my issue with the date field, I only want to see the last 30 days of data for Coordinator 2.

[JoeM]

So what you have there is an Append Query, and you could have things like Indexes preventing you from adding new records.
So, the first step should be just to run the SELECT portion of the query to see if it returns any records. If it does, then the issue is not with the criteria logic, but rather with the Append portion.

Also note that since your criteria is based on the underlying field values, and not the aggregated amounts, you can place them in the WHERE clause instead of the the HAVING clause. It makes it a little more efficient.
So try this:

SELECT Employees.Clock_Number, Timesheet.OT_Offered, [Employees].[Name_First] & " " & [Employees].[Name_Last] AS Employee_Name, Timesheet.Hours_OT, [Coordinators].[Name_First] & " " & [Coordinators].[Name_Last] AS Coordinator_Name, Timesheet.Period_End
FROM (Employees INNER JOIN Timesheet ON Employees.Employee_ID = Timesheet.Employee_ID) INNER JOIN Coordinators ON Employees.Coordinator_Id = Coordinators.Coordinator_ID
WHERE ((Timesheet.Period_End Between Date()-6 AND Date()) AND (Coordinators.Coordinator_ID=2))
GROUP BY Employees.Clock_Number, Timesheet.OT_Offered, [Employees].[Name_First] & " " & [Employees].[Name_Last], Timesheet.Hours_OT, [Coordinators].[Name_First] & " " & [Coordinators].[Name_Last], Timesheet.Period_End, Coordinators.Coordinator_ID, Timesheet.Period_Start;

Does this SELECT query return any records?

[MFS]

Yes, This Query sql works perfectly.

[JoeM]

OK, now try add in your INSERT line and try that, and see if it runs and adds any data.
If not, pay attention to any errors you might get. Usually, they are some key field violations such as you are missing required fields, or trying to put a duplicate value in a field that is required to be unique. Or you may be trying to paste data in a field that is the wrong Data Type.

To take a step back, why is it that you are writing calculated records to a new table?
The general rule of thumb is to not write anything to a table which can be calculated on the fly.

[MFS]

So now I took this one step further and added the [INSERT INTO dbo_OT_HOURS ( Clock_Number, OT_Offered, Employee_Name, OT_Hours, Coordinator_Name, Period_End )] At the top of the SQL. Changed the 6 in the Between Date()-6 AND Date() statement with a 30.
And now the system is working as I need it to.