.OpenRecordset and Month() (1 Viewer)

Spackle

Registered User.
Local time
Today, 22:23
Joined
Jul 12, 2018
Messages
12
This was working yesterday, but I've obviously changed something that was seemingly unrelated.:banghead::banghead::banghead:

When using an SQL string with .OpenRecordset, I can use

"WHERE [COLLATEDATE]<=Date();"

without issue, but when I use

"WHERE (Month([COLLATEDATE])<=Month(Date()));"

my code crashes.

I've confirmed that the 'COLLATEDATE' field is definitely saved as a date datatype, and I've checked that my string works when I use it in the SQL view of a normal Access query, but I can't think of any other esoteric issues that might be causing this to fail.

Here's what the complete recordset query is meant to be:

Code:
Dim StrSQL as String
 
StrSQL = "SELECT tbl_SHAPETemp.COLLATEDATE AS TestDate, tbl_SHAPETemp.WBS, tbl_SHAPETemp.[RAW HOURS] AS TestDuration " & _
FROM tbl_SHAPETemp " & _
WHERE (((Month([COLLATEDATE]))<=Month(Date())) AND ((Year([COLLATEDATE]))<=Year(Date()))) OR (((Month([COLLATEDATE]))>Month(Date())) AND ((Year([COLLATEDATE]))<Year(Date())));"

 
Creates query containing all older entries
Set OlderRecords = CurrentDb.OpenRecordset(strSQL)
 

isladogs

MVP / VIP
Local time
Today, 12:23
Joined
Jan 14, 2017
Messages
18,186
I think your bracketing is incorrect but as I'm answering on a tablet I can't check the suggested code below.
Is your date format mm/dd/yyyy? If not may need to deal with that also

Anyway, try this

Code:
Dim StrSQL as String
 
StrSQL = "SELECT tbl_SHAPETemp.COLLATEDATE AS TestDate, tbl_SHAPETemp.WBS, tbl_SHAPETemp.[RAW HOURS] AS TestDuration " & _
FROM tbl_SHAPETemp " & _
WHERE (((Month([COLLATEDATE])<=Month(Date())) AND ((Year([COLLATEDATE])<=Year(Date()))) OR (((Month([COLLATEDATE])>Month(Date())) AND ((Year([COLLATEDATE])<Year(Date())));"
 
Creates query containing all older entries
Set OlderRecords = CurrentDb.OpenRecordset(strSQL)
 

Spackle

Registered User.
Local time
Today, 22:23
Joined
Jul 12, 2018
Messages
12
Thanks Ridders. I did try your solution, but no dice I'm afraid.

The issue doesn't appear to be the brackets as I've reduced the 'where' statement to a single criteria: "WHERE (Month([COLLATEDATE])<=Month(Date()));" and this still crashes.

I've been messing around with dates for a while now trying to crack this issue, and the exact format doesn't seem to matter. As long as the field is of 'Date' datatype, Access can find the month, year parts using the appropriate function calls.

The issue seems to be the Month() function. For some reason "RecordDate < Date()" works, but changing to "Month(RecordDate) < Month(Date())" results in a crash. Like I mentioned, I did previously have this recordset working, but now it's not, and I don't know what might be affecting the Month() function.

None of the usual literature mentions any issues for either the Month() function, or how the OpenRecordset may run SQL queries differently to Access, since my strSQL string is taken straight from the SQL view of a working 'Select' query.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:23
Joined
Jan 20, 2009
Messages
12,849
Best avoid selecting through a process where a function needs to be applied to every record. Far better to express the criteria as a simple date range.

Code:
WHERE Datefield BETWEEN

rather than

Code:
WHERE Month(datefield) = Month(somedate)

It is really important to realise that the simplest expression to write is not always the best solution. It can literally make orders of magnitude of difference to the time and resources required for a task. You won't notice the difference on a handful of records when done the wrong way but the query will slow to a crawl as the number of records climbs.

WHERE field BETWEEN can select using an index. The database response is nearly instant.

WHERE function(field) = function(criteria) means every record must be processed before selection. Add ORDER BY and you won't see anything until the Select is completed. Very poor user experience.
 

Spackle

Registered User.
Local time
Today, 22:23
Joined
Jul 12, 2018
Messages
12
Ok, maybe I need to go back to first principles. What I’m doing is pulling out all records from employee timesheets that haven’t yet been extracted for SAP. The problem is that SAP has a couple of requirements:

* All records must be in the current month.
* the cumulative duration for any date can’t exceed 24 hours.

The report needs to be run a few days before the end of the month, so each report will have some records from the previous month that need to be dealt with.

What I’m doing is using nested loops. I create three recordsets in subsequent loops that contain a list of all employees in the report, all older records for each employee, and the total duration for each date for each employee. Each recordset is updated on each loop iteration. I’m finding all unextracted records, and looping through each record not in the current month to find the first date in the current month where the sum of the record duration and the existing total is less than 24 hours. The record date is then changed to this date on the report.

The SQL in my initial post is to create the recordset of all older records for a given employee. If there’s a better way to do this, I’m open to suggestions.
 

JHB

Have been here a while
Local time
Today, 13:23
Joined
Jun 17, 2012
Messages
7,732
Maybe a typo but you're missing a "
T

Code:
StrSQL = "SELECT tbl_SHAPETemp.COLLATEDATE AS TestDate, tbl_SHAPETemp.WBS, tbl_SHAPETemp.[RAW HOURS] AS TestDuration " & _
[B][COLOR=Red]"[/COLOR][/B]FROM tbl_SHAPETemp " & _
[B][COLOR=Red]"[/COLOR][/B]WHERE (((Month([COLLATEDATE]))<=Month(Date())) AND ((Year([COLLATEDATE]))<=Year(Date()))) OR (((Month([COLLATEDATE]))>Month(Date())) AND ((Year([COLLATEDATE]))<Year(Date())));"
 

Users who are viewing this thread

Top Bottom