Message Box Pop-Up - Quarter Query

[Robby19]

I created a query that displays data based off of Quarter 1 using

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?

 

[Micron]

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.

 

[Robby19]

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?

 

[Robby19]

So

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.

 

[Micron]

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.

 

[welshgasman]

I would have thought you could use.

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

 

[xenou]

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.

 

[Robby19]

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?"

 

[welshgasman]

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?

 

[xenou]

It is also possible there is a problem with m/d/yyyy and d/m/yyyy date interpretation.