Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2018, 11:34 PM   #1
Spackle
Newly Registered User
 
Join Date: Jul 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Spackle is on a distinguished road
.OpenRecordset and Month()

This was working yesterday, but I've obviously changed something that was seemingly unrelated.

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)

Spackle is offline   Reply With Quote
Old 07-12-2018, 11:55 PM   #2
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,532
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: .OpenRecordset and Month()

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)
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 07-13-2018, 12:35 AM   #3
Spackle
Newly Registered User
 
Join Date: Jul 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Spackle is on a distinguished road
Re: .OpenRecordset and Month()

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.

Spackle is offline   Reply With Quote
Old 07-13-2018, 12:50 AM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,133
Thanks: 68
Thanked 1,377 Times in 1,298 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: .OpenRecordset and Month()

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.
Galaxiom is online now   Reply With Quote
Old 07-13-2018, 01:14 AM   #5
Spackle
Newly Registered User
 
Join Date: Jul 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Spackle is on a distinguished road
Re: .OpenRecordset and Month()

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.
Spackle is offline   Reply With Quote
Old 07-13-2018, 01:22 AM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,173
Thanks: 2
Thanked 1,919 Times in 1,877 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: .OpenRecordset and Month()

Maybe a typo but you're missing a "
Quote:
Originally Posted by Spackle View Post
T

Code:
 
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())));"

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query to pull out 2 month, 6 month and 12 month extracts based on a previous query Cark Queries 7 05-24-2017 06:29 AM
Combo box to select month, then use it to filter that month for report annika05 Modules & VBA 4 03-05-2013 04:24 PM
Function / Update Query - change Month text to Month Number (Aug -> 8) Rx_ Code Repository 1 09-19-2012 10:43 AM
Placeing Start of month data and End of month date in Report Robert M Modules & VBA 4 11-10-2010 07:31 AM
carrying a previous month's balance to current month icemonster General 2 03-16-2010 04:34 AM




All times are GMT -8. The time now is 04:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World