Message Box Pop-Up - Quarter Query

Robby19

Board Regular
Joined
Mar 19, 2018
Messages
227
I created a query that displays data based off of Quarter 1 using
Code:
DatePart("q",[Report Date])=1
in the Criteria section.

However, what would I need to do if I wanted a message box to pop-up when I ran the query or report asking which quarter I would like to display?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
That's called a parameter query. How do you want to provide the date? Access can prompt you with an input box with your custom prompt, but bad or no user input will not stop the query from running. [Type in a value] in a query field will cause the query to display an input box with the directive "Type in a value". This is the simplest approach, but the least powerful.

Another way is to use a textbox formatted to date/time type, which should present a calendar when the control is entered. User picks a date, and the query references the form control containing that date. More experienced developers might execute a vba procedure (code) that builds the query and runs it after validating the input (e.g. making sure it's a date, that it's not blank, that it's not in the past, whatever).

Then instead of vba code there's macros, which I don't use.
 
Last edited:
Upvote 0
Another way is to use a textbox formatted to date/time type, which should present a calendar when the control is entered. User picks a date, and the query references the form control containing that date.

I was really just going for a "Select the Quarter" 1, 2, 3, or 4.

But, I think this would be pretty nifty. Would it be like a calendar drop down to select start and end date?
 
Upvote 0
So
Code:
Between [Start Date] and [End Date]
isn't the best option, as you've stated. This also does not give me access to a drop-down calendar. :confused:
 
Upvote 0
Would it be like a calendar drop down to select start and end date?
Yes but you'd need one for each date. Using the property sheet in form design view, set data format to date for both. If user types in an entry, pretty sure it has to resemble a date so there'd be a slight advantage there. These controls should not be bound to any field (have a record source) since they are only to be used as inputs for query parameters.
 
Upvote 0
I would have thought you could use.
Code:
DatePart("q",[Report Date])=[Which Quarter]

Then the parameter dialog would open asking for [Which Quarter]

Rough and ready, but it works. Else create a form to supply the parameter.?

HTH
 
Upvote 0
Note that both 1/1/2018 and 1/1/2019 are in the same quarter, even though they are not in the same year. So if your data will span more than one year you might need more logic to distinguish different quarters and different years.
 
Last edited:
Upvote 0
I appreciate the advice! I am noticing however that the quarters are not exactly accurate. When I use Quarter 1 as the parameter, I see data from Nov-17 through Jan-18 instead of Jan-18 through Mar-18. Is there a logical reason for this or is this "user error?"
 
Upvote 0
I appreciate the advice! I am noticing however that the quarters are not exactly accurate. When I use Quarter 1 as the parameter, I see data from Nov-17 through Jan-18 instead of Jan-18 through Mar-18. Is there a logical reason for this or is this "user error?"

All depends on your quarters?
If the financial year starts in November then that would be correct? Only you know what quarter value matches to what dates?
As xenou has mentioned you would likely need to take into account the year as well?
 
Upvote 0
It is also possible there is a problem with m/d/yyyy and d/m/yyyy date interpretation.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top