Retrieving all records for Quarter/Half/Year

[Maxi2011]

Hi

I am currently making a databse which stores contracts. One of the main functions I want it to perform is to retrieve all the contracts due to expire within each quarter (running from Jan-Mar, Apr-June etc), each half (Jan-Jun and Jul-Dec) and each year (Jan-Dec). These time periods will remain fixed e.g. it would not be possible for a 'quarter' to be Nov-Jan.

All i need in a nutshell is to be able to get all the records for which the 'Expiration Date' falls within the relevant time period (quarter, year, half etc)

I may be missing something obvious but I am having some real issues figuring out a good way to do this (I'm still pretty new to all this!) but I am A) not sure how I can get access to recognise the various time periods I want it to and B) Get it to display that info in a report or query (which I would then run a report off) C) What the best way to have the user select the time period they want to retireve the info for and how to link this to the query and/or VB Module relevant to it.

Thanks in advance for any help.....

Maxi

 

[scottn]

You can use a where clause in a SQL query to filter the proper records. It would go something like this:

SELECT ...... FROM ......
WHERE ExpirationDate >= #1/1/04# AND ExpirationDate <= #3/31/04#

That would get you everything that expired in the first quarter. Obviously you'll need to manipulate those dates depending on exactly what period you want. I imagine you'll be collecting that information from the user. Exactly how you do this and what you do with it depends on your application, but probably you'll need a little form to ask the user for the date range of interest, and then you'll use that information to compose a query like the one above, using VBA code.

You say you're pretty new at this, so here's a little more detailed walk-through. Hope I'm not boring you w/ stuff you already understand or don't need. Let's say you want a report to show all contracts expiring during a period defined by the user.

Design a little form with two text fields (txtStartDate and txtEndDate, let's call them), and an OK button. This form will be used as a pop-up to ask the user what date range they're interested in.

In your reports Open event procedure, open the form as a pop-up. When you open a form as a pop-up, execution halts until that form is hidden or closed. This is a great way to make your code (in the report Open event) stop and wait for the user to provide input. Open a form as a popup like this:

DoCmd.OpenForm "MyForm", , , , , acDialog

Now, notice I said that execution is halted until the form is closed or hidden. After execution resumes, we'll want to look in those textboxes to see what the user entered, so we will hide the form when the user is done as opposed to closing it. Attach code to the OK button's Click event like this:

Me.Visible = False 'Hides the form when user clicks ok

Now, back in the report's Open event, once execution resumes, we can look in those textboxes, use their values to build the query, and then assign the recordsource of the report to that query. So your Open event will look something like this:

DoCmd.OpenForm "MyForm", , , , , [B]acDialog[/B]

'Execution is now paused until user clicks OK.  Once user clicks OK,
'execution resumes below:

Me.Recordsource = "SELECT ..... FROM ..... WHERE ExpirationDate >= #" & Forms("MyForm").txtStartDate & "# AND ExpirationDate <= #" & Forms("MyForm").txtEndDate & "#"

DoCmd.Close acForm, "MyForm"

Notice now that you're responsible for closing the form.

There's a lot of holes you have to fill in here. For example, you need to make sure the user enters valid dates in those text fields (try an input mask). You need to make sure the txtEndDate comes after txtStartDate or you won't get any data. You need to make sure that either the user can't close the form themselves or that you check to make sure it's still open before you make references to its component text fields -- otherwise if the user closes the form instead of clicking OK, your code will crash. (By the way, one nicety you can add is allowing the user to close the form and using that to indicate that the user wishes to cancel the running of the report. You can even add a Cancel button that closes the form. Then in your report's open even code, check to see if the form is still open after execution resumes and if not, set the cancel parameter to 1, which will cancel the report.) But anyway, that's the basic idea.

You can make your popup form as nice as you want. For example, if you didn't want your user to have to put in two dates, but instead be able to ask for "Quarter 1, 2004", you could make a drop down that had choices like "Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4", a text box or drop-down for the year, and put code behind the form to convert those user choices into two dates. The user interface is up to you, but eventually you will need to have two dates so that you can compose that WHERE clause.

 

[Maxi2011]

Grouping the Dates

Thanks so much for the help Scott. I wondered if you could just eleborate on one more thing for me.

Ideally I did want to be able to do what you mentioned at the end of the post, which was to group the dates into 'Quarter 1, 2003' or 'Half 2 2005' etc and then have this as a drop down box which the user can select the time period and year which it falls in from.

What I am confused about with this is how I make VBA recognise the time period I want in dates from the users input from the drop down list. E.g. what's the code I have to use to make VBA realise that when the user selects Quarter 1 2004, It's being asked to retireve all records which have an expiration date between 1st Jan 2004 and 31st March 2004?

The time which the quarters, halfs and years from from and to will always remain the same (e.g the first quarter will always be jan-mar) so i'm sure if I could just get the hang of this I would be able to apply it to all the time periods I am trying to get.

Sorry if i'm not getting this as quick as I should!

Thanks again
Maxi2011

 

[scottn]

On your form, make a drop-down for the user to select the quarter they want (let's call it cmbPeriod). Set the Row Source Type property to "Value list". In the Row Source property you'll enter a list of values like:

"Quarter 1";"Quarter 2";"Quarter 3";"Quarter 4"

The quotes are important -- these are Strings. VBA doesn't have any concept of quarters so we'll use strings and then write the code to "recognize" our own strings and "convert" them to dates.

You can use a text box or another drop-down for the year. I prefer to make a drop-down and take a moment to enter a value list for the years, because this way you don't have to validate the user's typing as you would in a text box. It will only take you a moment to type in a value list including all years out to 2050 or so.

Now, in the code I showed you before, you just have to make changes so that when you build the query string, you take a look at the user's selections and determine the start and end dates from them. One thing you'll notice is that the user's choice of quarter determines the month and day of both the start and end dates, while the user's choice of year is the year for both start and end dates. So you're going to have to build the two dates from pieces, something like this (this is the same code I showed you before for your report's Open event -- just modified):

Dim startDate as String
Dim endDate as String

DoCmd.OpenForm "MyForm", , , , , acDialog

'Execution is now paused until user clicks OK.  Once user clicks OK,
'execution resumes below:

'Figure out the date range
Select Case Forms("MyForm").cmbPeriod
    Case "Quarter 1"
        startDate = "1/1/" & Forms("MyForm").cmbYear
        endDate = "3/31/" & Forms("MyForm").cmbYear
    Case "Quarter 2"
        '...you get the idea...
    Case "Quarter 3"
        '...
    Case "Quarter 4"
        '...
End Select

'Now build the query
Me.Recordsource = "SELECT ..... FROM ..... WHERE ExpirationDate >= #" & startDate & "# AND ExpirationDate <= #" & endDate & "#"

DoCmd.Close acForm, "MyForm"

Something like that ought to work. You can, of course, extend the idea to work for half-years and years or whatever you want.

 

[Maxi2011]

That's great

Thank you so much Scott

Maxi2011