Month Value Between Dates (1 Viewer)

Dirtrider929 · May 22, 2019

I have a combo box, cboMonth, on a form the contains the calendar month name and numeric values. I am running a query,"qrySLR_Master_Calendar", within the form, "frmSLR_Master_Calendar", that contains data including a "StartDate" and "EndDate" which could span 3-11 months. I would like to be able to pick a month from my combo box and have it filter all records to return for that month selected. I am currently stumped on how to accomplish this and it seems like such an easy task.

theDBguy · May 22, 2019

Hi. You said you have two date columns in your data, which one did you want the filter to apply? Both?

Dirtrider929 · May 22, 2019

Yes, I need it to tell me what month values are available between the start and end date values.

theDBguy · May 22, 2019

Dirtrider929 said:
Yes, I need it to tell me what month values are available between the start and end date values.

One way to try it is to use something like:

Code:

WHERE Month([startdate])=YourSelectedMonth OR Month([enddate])=YourSelectedMonth

Dirtrider929 · May 22, 2019

theDBguy said:
One way to try it is to use something like:

Code:

WHERE Month([startdate])=YourSelectedMonth OR Month([enddate])=YourSelectedMonth

This will give value to the months in between the two dates?

Say I have a program that is available Jan 1 through May 31 and I use my combo box to select April as the month, will the code above populate the program as being available for the month of April?

theDBguy · May 22, 2019

Dirtrider929 said:
This will give value to the months in between the two dates?

Say I have a program that is available Jan 1 through May 31 and I use my combo box to select April as the month, will the code above populate the program as being available for the month of April?

Oh, no. I see I didn't completely understand your requirement. Instead, let's try it this way then.

Code:

WHERE DateSerial(Year([startdate),YourMonthSelection,Day([startdate])) Between [startdate] And [enddate]

Dirtrider929 · May 22, 2019

Getting and "Expected : end of statement error on "Between".

WHERE DateSerial(Year([Start]), cboMonth, Day([StartDate])) Between([StartDate]) And ([EndDate])

theDBguy · May 22, 2019

Dirtrider929 said:
Getting and "Expected : end of statement error on "Between".

It happens sometimes. Try it this way then:

Code:

WHERE DateSerial(Year([startdate),YourMonthSelection,Day([startdate])) >= [startdate] And DateSerial(Year([startdate),YourMonthSelection,Day([startdate])) <= [enddate]

Dirtrider929 · May 22, 2019

Now its on the "Where". Did I do something wrong?

WHERE DateSerial(Year([StartDate]), cboMonth, Day([StartDate])) >= [StartDate] And DateSerial(Year([StartDate]), cboMonth, Day([StartDate])) <= ([EndDate])

theDBguy · May 22, 2019

Dirtrider929 said:
Now its on the "Where". Did I do something wrong?

WHERE DateSerial(Year([StartDate]), cboMonth, Day([StartDate])) >= [StartDate] And DateSerial(Year([StartDate]), cboMonth, Day([StartDate])) <= ([EndDate])

Hi. Just to be clear, you simply added the WHERE statement I provided to your query's SQL statement, correct?

Dirtrider929 · May 22, 2019

No, I had a laps in brain function after working on this for so long and trying so many different things.

After adding it to the proper place I get a syntax error.

Dirtrider929 · May 22, 2019

Query SQL code:

SELECT MasterCalendar.Supplier AS Supplier_Name, MasterCalendar.Description AS Offer_Description, MasterCalendar.Offer, MasterCalendar.Start, MasterCalendar.End, MasterCalendar.Guidelines, MasterCalendar.Comments, MasterCalendar.[X-Recinded], MasterCalendar.RecindedDate, MasterCalendar.SCAN, Month([start]) AS Expr1, Year([End]) AS Expr2
FROM MasterCalendar
GROUP BY MasterCalendar.Supplier, MasterCalendar.Description, MasterCalendar.Offer, MasterCalendar.Start, MasterCalendar.End, MasterCalendar.Guidelines, MasterCalendar.Comments, MasterCalendar.[X-Recinded], MasterCalendar.RecindedDate, MasterCalendar.SCAN, Month([start])
HAVING (((MasterCalendar.Supplier) Like "*" & [Forms]![frmSLR_DashboardMain]![txtSearchSuppliers].[Text] & "*") AND ((Month([start]))=[Forms]![frmSLR_DashboardMain]![cboMonth]) AND ((Year([End]))=[Forms]![frmSLR_DashboardMain]![cboYear]))
ORDER BY MasterCalendar.Supplier, MasterCalendar.Description, MasterCalendar.Start;

theDBguy · May 23, 2019

Dirtrider929 said:
No, I had a laps in brain function after working on this for so long and trying so many different things.

After adding it to the proper place I get a syntax error.

Hi. The WHERE clause goes before the GROUP BY clause and you have to use the actual names of your start and end date fields where I used startdate and enddate, because I thought those were the names of your date fields based on what you said earlier.

Dirtrider929 · May 23, 2019

I moved the argument to before the Group By and you are correct, the names are StartDate and EndDate so I left them in as you wrote it and I am still getting an error.

I've tried changing arguments, column names, month selections to get it to work but I must be overlooking something.

theDBguy · May 23, 2019

Dirtrider929 said:
I moved the argument to before the Group By and you are correct, the names are StartDate and EndDate so I left them in as you wrote it and I am still getting an error.

I've tried changing arguments, column names, month selections to get it to work but I must be overlooking something.

Oh, you're also supposed to replace the "YourMonthSelection" part with a reference to how you're actually selecting the month for your query. If you can post a sample db, we can show you how to do it.

Month Value Between Dates (1 Viewer)

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

Attachments

Dirtrider929

Registered User.

theDBguy

I’m here to help

Dirtrider929

Registered User.

Attachments

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread