Query for last month, yyyymmdd (1 Viewer)

JonesD

Registered User.
Local time
Yesterday, 22:17
Joined
Dec 4, 2018
Messages
11
I am looking to setup a query that will run on the previous month's data, the db has dates stored in the yyyymmdd format, which is what I believe is causing issues with any criteria I have tried to use. It will be pulling from a table called "Attendance" and a Field called "AssignDate". Thank you!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Aug 30, 2003
Messages
36,118
The data type of the field is text, not date/time? That will certainly complicate matters, but something like this should work:

Between "20181101" And "20181130"

without the quotes if it's a numeric data type.
 

plog

Banishment Pending
Local time
Yesterday, 21:17
Joined
May 11, 2011
Messages
11,613
Formatting is for displaying data. Field types are for storing data. Are your dates stored as date/time fields? Or as text?

When they are stored as dates, you can then use Date functions (https://www.techonthenet.com/access/functions/index.php) to work with them. You could create a query using them to determine the starting and ending dates of the prior month (no matter what the current month is) and see if AssignDate fell between them. Your query will never need touching to determine 'the prior month'.
 

JonesD

Registered User.
Local time
Yesterday, 22:17
Joined
Dec 4, 2018
Messages
11
Unfortunately the data is stored from a software program - when I open the tables I only see it stored as yyyymmdd, the company that provides the software did provide a "module" that converts the date fields to the standard mm/dd/yyyy format - so maybe there is a way to use this in the query criteria?

From Date Funciton Module:

Option Compare Database

Public Function ImcDate(theDate As String) As Date

Dim theYear As String
Dim theMonth As String
Dim theDay As String

On Error Resume Next

If theDate <> "" Then

theYear = Left(theDate, 4)
theMonth = Mid(theDate, 5, 2)
theDay = Right(theDate, 2)

ImcDate = CDate(theMonth & "/" & theDay & "/" & theYear)

Else

ImcDate = ""

End If

End Function
Public Function ImcTime(theDate As String, theTime As String) As Date

Dim theHour As String
Dim theMin As String

On Error Resume Next

If theTime <> "" Then

theHour = Left(theTime, 2)
theMin = Right(theTime, 2)
ImcTime = LTrim(ImcDate(theDate) & " " & theHour & ":" & theMin)

Else

ImcTime = ""

End If

End Function


Public Function ImcTimeSec(theDate As String, theTime As String, theSec As String) As Date

Dim theHour As String
Dim theMin As String

On Error Resume Next

If theTime <> "" Then

theHour = Left(theTime, 2)
theMin = Right(theTime, 2)
ImcTimeSec = LTrim(ImcDate(theDate) & " " & theHour & ":" & theMin & ":" & theSec)

Else

ImcTimeSec = ""

End If

End Function
 

JonesD

Registered User.
Local time
Yesterday, 22:17
Joined
Dec 4, 2018
Messages
11
I'm not sure it will help anyone else, but by using the module I was able to get a DateSerial to work:

Expr1: IMCDate([AssignDate])

And then in criteria:

Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)
 

plog

Banishment Pending
Local time
Yesterday, 21:17
Joined
May 11, 2011
Messages
11,613
I think that fails for January. This will work:

Code:
SELECT *
FROM Attendance
WHERE (((ImcDate(AssignDate)>=CDate(Month(DateAdd("m",-1,Date())) & "/1/" & Year(DateAdd("m",-1,Date()))) And (ImcDate(AssignDate))<CDate(Month(Date()) & "/1/" & Year(Date()))));
 

JonesD

Registered User.
Local time
Yesterday, 22:17
Joined
Dec 4, 2018
Messages
11
Thank you for the quick reply - Am I adding that into the criteria for the same Field and Table, if so it does not work - I have attached the SQL so you have an idea of the entire query:

SELECT IMCDate([AssignDate]) AS [Date], PersonnelFileInfo.LastName, PersonnelFileInfo.Rank, OfficerAttendance.StartTime, OfficerAttendance.EndTime
FROM OfficerAttendance INNER JOIN PersonnelFileInfo ON OfficerAttendance.ID = PersonnelFileInfo.ID
GROUP BY IMCDate([AssignDate]), PersonnelFileInfo.LastName, PersonnelFileInfo.Rank, OfficerAttendance.StartTime, OfficerAttendance.EndTime, IMCDate([AssignDate]), OfficerAttendance.Division, OfficerAttendance.DutyCode
HAVING (((PersonnelFileInfo.Rank)="Sergeant") AND ((IMCDate([AssignDate])) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((OfficerAttendance.Division)="HI") AND ((OfficerAttendance.DutyCode)="OI"))
ORDER BY PersonnelFileInfo.LastName;
 

plog

Banishment Pending
Local time
Yesterday, 21:17
Joined
May 11, 2011
Messages
11,613
It should go in the WHERE clause. In fact, nothing in your HAVING should be there. Just change HAVING to WHERE.

Actually, why is this an aggregate query at all? You use no aggregate functions (SUM, MAX, COUNT, etc).
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:17
Joined
Aug 30, 2003
Messages
36,118
2 points. Plog is correct that the HAVING clause would be more efficient as a WHERE, but it can't just be changed. It also needs to be moved ahead of GROUP BY. Second, while using the function will work, it could cause performance problems on large tables, since the function has to be applied to every record in the table.
 

Users who are viewing this thread

Top Bottom