MSAC2K - Form to select year and quarterly period for report...

[David Dubroff of Mr. PC]

Question for Microsoft Access 2000:

What is the best way for someone to select a year and one of its quarterly
periods
(3 month period) in order to generate and print a quarterly report for the
desired year? Q1=Jan-Mar Q2=Apr - Jun Q3=Jul - Sep Q4=Oct - Dec

I have a report already created that is based upon some queries whereby I
manually enter the date ranges desired as criteria within the queries.

I need to have information on a form converted into criteria within a query.
I need a year typed on a form and a quarterly period selection (Q1 Q2 Q3 Q4)
to become a date range for that quarterly period as criteria within a query.
For example, if "2003" is typed in as the year and "Q1" is selected, I need
the criteria in the query to be: Between #01/01/2003# and #03/31/2003#

How do I get the criteria in the query to be based upon the form year typed
in and the quarterly period selected?

I would like to have the end-user type in a year on a form and select the
quarterly period desired on the same form (Q1 Q2 Q3 or Q4), and have the
report automatically generated for the quarter and year that was specified
on the form. I would also like the report to be titled accordingly and
labeled within the page footer as being the quarterly report for Year "X"
Quarter "Y".

Please advise....
Dave

 

[Allen Browne]

Use DateSerial() and DateAdd() to generate the dates.

The example below calculates the date the quarter starts, and creates a
string to use in the WhereCondition of the OpenReport action. It might be
more efficient to use "Between" as you suggest, but this example uses "less
than the start of the next quarter"; that way if the field has a time
component, the records for the last day of the quarter are not missed. The
explicit formatting ensures the code works regardless of the user's Regional
Settings for dates.

This assumes a text box named "txtYear" and another named "txtQtr". Set
their Format property to "General Number" or similar, and use a validation
rule to ensure the range is valid.

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1, dtStartDate), _
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If

 

[David Dubroff of Mr. PC]

Allen....

I am not sure what you meant in the example that you posted.

On a form, I want the user to type in a year (2003 for example). Once the
year is entered, I want the user to select a quarter from a list box (Q1,
Q2, Q3 or Q4). Based upon the year typed in and the quarter selected, I
want to open a report that is based upon compound queries. Two of the
underlying queries must get their criteria from the year and quarter
specified on the entry form.

Please advise....
Dave

Use DateSerial() and DateAdd() to generate the dates.

The example below calculates the date the quarter starts, and creates a
string to use in the WhereCondition of the OpenReport action. It might be
more efficient to use "Between" as you suggest, but this example uses "less
than the start of the next quarter"; that way if the field has a time
component, the records for the last day of the quarter are not missed. The
explicit formatting ensures the code works regardless of the user's Regional
Settings for dates.

This assumes a text box named "txtYear" and another named "txtQtr". Set
their Format property to "General Number" or similar, and use a validation
rule to ensure the range is valid.

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1, dtStartDate), _
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Question for Microsoft Access 2000:

What is the best way for someone to select a year and one of its quarterly
periods
(3 month period) in order to generate and print a quarterly report for the
desired year? Q1=Jan-Mar Q2=Apr - Jun Q3=Jul - Sep Q4=Oct - Dec

I have a report already created that is based upon some queries whereby I
manually enter the date ranges desired as criteria within the queries.

I need to have information on a form converted into criteria within a query.
I need a year typed on a form and a quarterly period selection (Q1 Q2 Q3 Q4)
to become a date range for that quarterly period as criteria within a query.
For example, if "2003" is typed in as the year and "Q1" is selected, I need
the criteria in the query to be: Between #01/01/2003# and #03/31/2003#

How do I get the criteria in the query to be based upon the form year typed
in and the quarterly period selected?

I would like to have the end-user type in a year on a form and select the
quarterly period desired on the same form (Q1 Q2 Q3 or Q4), and have the
report automatically generated for the quarter and year that was specified
on the form. I would also like the report to be titled accordingly and
labeled within the page footer as being the quarterly report for Year "X"
Quarter "Y".

Please advise....
Dave

 

[David Dubroff of Mr. PC]

Allen...

Is there some way to have the year entered and the quarter selected become
the date range in another control box on the form?

Perhaps I could reference the control box on the form that contains the
desired date range as my criteria in the underlying queries.

How do I put a date range into a control box based on a year being entered
in an unbound control and a quarterly period being selected in a list box?

Allen....

I am not sure what you meant in the example that you posted.

On a form, I want the user to type in a year (2003 for example). Once the
year is entered, I want the user to select a quarter from a list box (Q1,
Q2, Q3 or Q4). Based upon the year typed in and the quarter selected, I
want to open a report that is based upon compound queries. Two of the
underlying queries must get their criteria from the year and quarter
specified on the entry form.

Please advise....
Dave

Use DateSerial() and DateAdd() to generate the dates.

The example below calculates the date the quarter starts, and creates a
string to use in the WhereCondition of the OpenReport action. It might be
more efficient to use "Between" as you suggest, but this example uses "less
than the start of the next quarter"; that way if the field has a time
component, the records for the last day of the quarter are not missed. The
explicit formatting ensures the code works regardless of the user's Regional
Settings for dates.

This assumes a text box named "txtYear" and another named "txtQtr". Set
their Format property to "General Number" or similar, and use a validation
rule to ensure the range is valid.

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1, dtStartDate), _
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Question for Microsoft Access 2000:

What is the best way for someone to select a year and one of its quarterly
periods
(3 month period) in order to generate and print a quarterly report for the
desired year? Q1=Jan-Mar Q2=Apr - Jun Q3=Jul - Sep Q4=Oct - Dec

I have a report already created that is based upon some queries

whereby

I Q3
Q4)

 

[David Dubroff of Mr. PC]

Allen...

I am fairly new to coding so I am trying to understand what everything is
doing in your code.

For starters, where and when do I execute your code? Do I put your code
into a specific event on my form? Also, where is the end date generated or
calculated in your code?

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"


** What does the Const line above do or mean **



** Explain the code below, if possible. What is txtYear and what is
SomeDate? I do not know what everything is doing step by step **


If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1, dtStartDate), _
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If



Thanks for your assistance...
Dave

No problem. The suggested code used a text box (txtQtr), but no problem with
a list box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen....

I am not sure what you meant in the example that you posted.

On a form, I want the user to type in a year (2003 for example). Once the
year is entered, I want the user to select a quarter from a list box (Q1,
Q2, Q3 or Q4). Based upon the year typed in and the quarter selected, I
want to open a report that is based upon compound queries. Two of the
underlying queries must get their criteria from the year and quarter
specified on the entry form.

Please advise....
Dave

Use DateSerial() and DateAdd() to generate the dates.

The example below calculates the date the quarter starts, and creates a
string to use in the WhereCondition of the OpenReport action. It might be
more efficient to use "Between" as you suggest, but this example uses "less
than the start of the next quarter"; that way if the field has a time
component, the records for the last day of the quarter are not missed. The
explicit formatting ensures the code works regardless of the user's Regional
Settings for dates.

This assumes a text box named "txtYear" and another named "txtQtr". Set
their Format property to "General Number" or similar, and use a validation
rule to ensure the range is valid.

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1,

dtStartDate),

_ for
the whereby

Q2

Q3 select
the Year
"X"

 

[Allen Browne]

Choose an event that's suitable for you. For example, you could use the
Click event of a command button on your form. It could write the start date
and end date onto the text boxes on your form, and then perform the
OpenReport. When the report opens, it runs its query, and the query reads
the values from the form.

The end date is calculated by adding one quarter onto the start date. In the
original reply, that happened in the middle of the line starting strWhere =
.... (Actually there are 3 physical lines that are really one line because of
the underscore line-continuation character at the end, and it happens in the
middle line.) The code was written to create a WhereCondition to use as part
of the OpenReport action. If that's a bit complex, we can take a different
approach.

Let's rewrite this on the assumption that your form has:
1. a text box where the user enters a year, with these properties:
Name txtYear
Format General Number
Validation Rule Between 1900 And 2100
Validation Text Enter a 4-digit year
2. a list box where the user chooses the quarter, with these properties:
Name lstQtr
RowSourceType Value List
RowSource 1; 2; 3; 4
3. a text box where the program will write the starting date:
Name txtStartDate
Format General Date
Visible No
4. a text box where the program writes the ending date:
Name txtStartDate
Format General Date
Visible No
5. a command button for opening the report:
On Click [Event Procedure]

Click the Build button (...) beside the On Click property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, paste these 3 lines:

Me.txtStartDate = DateSerial(Me.txtYear, 3 * (Me.lstQtr - 1)+1,1)
Me.txtEndDate = DateAdd("q", 1, Me.txtStartDate) - 1
DoCmd.OpenReport "MyReport", acViewPreview

(For the sake of simplicity, I've removed the error checking, but that code
will fail if the user clicks the button when nothing is chosen in the
listbox, or no year is entered.)

This all assumes that the date field in the query that feeds your report has
this in the Criteria row:
Between [Forms]![MyForm]![txtStartDate] And
[Forms]![MyForm]![txtEndDate]

Note: Replace "MyForm" and "MyReport" above with the names of your form and
your report.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen...

I am fairly new to coding so I am trying to understand what everything is
doing in your code.

For starters, where and when do I execute your code? Do I put your code
into a specific event on my form? Also, where is the end date generated or
calculated in your code?

Dim strWhere As String
Dim dtStartDate As Date
Const conJetDate = "\#mm\/dd\/yyyy\#"


** What does the Const line above do or mean **



** Explain the code below, if possible. What is txtYear and what is
SomeDate? I do not know what everything is doing step by step **


If Me.txtYear > 1900 And Me.txtYear < 2100 And _
Me.txtQtr >=1 And Me.txtQtr <=4 Then
dtStartDate = DateSerial(Me.txtYear, 3 * (Me.txtQtr - 1) + 1, 1)

strWhere = "([SomeDate] >= " & Format(dtStartDate, conJetDate) _
& ") AND ([SomeDate] < " & Format(DateAdd("q", 1, dtStartDate), _
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If



Thanks for your assistance...
Dave

No problem. The suggested code used a text box (txtQtr), but no problem with
a list box.

creates

a might
be missed.
The

1,

1)

strWhere = "([SomeDate] >= " & Format(dtStartDate,

conJetDate)

_

& ") AND ([SomeDate] < " & Format(DateAdd("q", 1,

dtStartDate),

_
conJetDate) & ")"
Else
MsgBox "Invalid date"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Question for Microsoft Access 2000:

What is the best way for someone to select a year and one of its
quarterly
periods
(3 month period) in order to generate and print a quarterly report for
the
desired year? Q1=Jan-Mar Q2=Apr - Jun Q3=Jul - Sep Q4=Oct - Dec

I have a report already created that is based upon some queries whereby
I
manually enter the date ranges desired as criteria within the queries.

I need to have information on a form converted into criteria

within

a Q2

within

a

selected,

I have
the accordingly
and