Month Value Between Dates

[Dirtrider929]

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]

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

 

[Dirtrider929]

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

 

[theDBguy]

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:

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

 

[Dirtrider929]

One way to try it is to use something like:

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]

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.

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

 

[Dirtrider929]

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

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

 

[theDBguy]

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

It happens sometimes. Try it this way then:

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

 

[Dirtrider929]

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]

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]

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]

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]

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]

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]

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.