Create Report By Data Criteria

[Dave_FFM]

Good Morning,

Our company bills its client on a monthly basis and once a particular Job is
complete the month billed is typed selected for that job. At the moment i am
creating a seperate report every month but i now know that by entering
[please select month] into the "Month Billed" Column a report can be
generated all on a single report instead of countless reports. However is it
possible to do this by either a button on a form or a dorp down box which
would appear before the report opens? I would rather do it this way because
of a smaller margin for error as the "Month billed" is in the format of "Jan
07", "Feb 07", "Mar 07" etc whereas the user may type January 07 into the
Parameter value box.

Many thanks

Dave

 

[Duane Hookom]

I always recommend using controls on forms for filtering forms or reports.
You can create a form with a button that will open your report. Add a combo
box to the form and set its Row Source property to something like:

SELECT Format([ADateField],"mmm yy")
FROM tblATable
GROUP BY Format([ADateField],"mmm yy");

You must provide the field and table names. Name this combo box "cboMMMYY".
Use the command button wizard to create a button that opens your report. Then
modify the code to something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboMMMYY) Then
strWhere = strWhere & " And [MthBilledField] = """ & _
Me.cboMMMYY & """"
End If
DoCmd.OpenReport "rptBilling", acPreview, , strWhere

 

[Dave_FFM]

Thanks for the help.
I've done what you said but after writing the code i get a Compile error
reading "End If without Block If"

I always recommend using controls on forms for filtering forms or reports.
You can create a form with a button that will open your report. Add a combo
box to the form and set its Row Source property to something like:

SELECT Format([ADateField],"mmm yy")
FROM tblATable
GROUP BY Format([ADateField],"mmm yy");

You must provide the field and table names. Name this combo box "cboMMMYY".
Use the command button wizard to create a button that opens your report. Then
modify the code to something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboMMMYY) Then
strWhere = strWhere & " And [MthBilledField] = """ & _
Me.cboMMMYY & """"
End If
DoCmd.OpenReport "rptBilling", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Good Morning,

Our company bills its client on a monthly basis and once a particular Job is
complete the month billed is typed selected for that job. At the moment i am
creating a seperate report every month but i now know that by entering
[please select month] into the "Month Billed" Column a report can be
generated all on a single report instead of countless reports. However is it
possible to do this by either a button on a form or a dorp down box which
would appear before the report opens? I would rather do it this way because
of a smaller margin for error as the "Month billed" is in the format of "Jan
07", "Feb 07", "Mar 07" etc whereas the user may type January 07 into the
Parameter value box.

Many thanks

Dave

 

[Duane Hookom]

You should have gotten the error. Post your code if you need help.
--
Duane Hookom
Microsoft Access MVP

Thanks for the help.
I've done what you said but after writing the code i get a Compile error
reading "End If without Block If"

I always recommend using controls on forms for filtering forms or reports.
You can create a form with a button that will open your report. Add a combo
box to the form and set its Row Source property to something like:

SELECT Format([ADateField],"mmm yy")
FROM tblATable
GROUP BY Format([ADateField],"mmm yy");

You must provide the field and table names. Name this combo box "cboMMMYY".
Use the command button wizard to create a button that opens your report. Then
modify the code to something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboMMMYY) Then
strWhere = strWhere & " And [MthBilledField] = """ & _
Me.cboMMMYY & """"
End If
DoCmd.OpenReport "rptBilling", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Good Morning,

Our company bills its client on a monthly basis and once a particular Job is
complete the month billed is typed selected for that job. At the moment i am
creating a seperate report every month but i now know that by entering
[please select month] into the "Month Billed" Column a report can be
generated all on a single report instead of countless reports. However is it
possible to do this by either a button on a form or a dorp down box which
would appear before the report opens? I would rather do it this way because
of a smaller margin for error as the "Month billed" is in the format of "Jan
07", "Feb 07", "Mar 07" etc whereas the user may type January 07 into the
Parameter value box.

Many thanks

Dave

 

[Dave_FFM]

Sorry. the code i wrote looks like this

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
strWhere = "1=1"

If Not IsNull(Me.ComboMMMYY) Then strWhere = strWhere & " And
[BILLED(MONTH)] = """ & Me.ComboMMMYY & """"

End If

DoCmd.OpenReport Test1, acPreview, , strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

You should have gotten the error. Post your code if you need help.
--
Duane Hookom
Microsoft Access MVP

Thanks for the help.
I've done what you said but after writing the code i get a Compile error
reading "End If without Block If"

I always recommend using controls on forms for filtering forms or reports.
You can create a form with a button that will open your report. Add a combo
box to the form and set its Row Source property to something like:

SELECT Format([ADateField],"mmm yy")
FROM tblATable
GROUP BY Format([ADateField],"mmm yy");

You must provide the field and table names. Name this combo box "cboMMMYY".
Use the command button wizard to create a button that opens your report. Then
modify the code to something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboMMMYY) Then
strWhere = strWhere & " And [MthBilledField] = """ & _
Me.cboMMMYY & """"
End If
DoCmd.OpenReport "rptBilling", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


:

Good Morning,

Our company bills its client on a monthly basis and once a particular Job is
complete the month billed is typed selected for that job. At the moment i am
creating a seperate report every month but i now know that by entering
[please select month] into the "Month Billed" Column a report can be
generated all on a single report instead of countless reports. However is it
possible to do this by either a button on a form or a dorp down box which
would appear before the report opens? I would rather do it this way because
of a smaller margin for error as the "Month billed" is in the format of "Jan
07", "Feb 07", "Mar 07" etc whereas the user may type January 07 into the
Parameter value box.

Many thanks

Dave

 

[Duane Hookom]

There should be a new line started after the "If ... Then". I also expect
Test1 is the actual name of your report so it must be enclosed in quotes. If
it is a variable, I have no idea where it is getting its value.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
strWhere = "1=1"

If Not IsNull(Me.ComboMMMYY) Then
strWhere = strWhere & " And [BILLED(MONTH)] = """ & _
Me.ComboMMMYY & """"
End If

DoCmd.OpenReport "Test1", acPreview, , strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

--
Duane Hookom
Microsoft Access MVP

Sorry. the code i wrote looks like this

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
strWhere = "1=1"

If Not IsNull(Me.ComboMMMYY) Then strWhere = strWhere & " And
[BILLED(MONTH)] = """ & Me.ComboMMMYY & """"

End If

DoCmd.OpenReport Test1, acPreview, , strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

You should have gotten the error. Post your code if you need help.
--
Duane Hookom
Microsoft Access MVP

Thanks for the help.
I've done what you said but after writing the code i get a Compile error
reading "End If without Block If"

:

I always recommend using controls on forms for filtering forms or reports.
You can create a form with a button that will open your report. Add a combo
box to the form and set its Row Source property to something like:

SELECT Format([ADateField],"mmm yy")
FROM tblATable
GROUP BY Format([ADateField],"mmm yy");

You must provide the field and table names. Name this combo box "cboMMMYY".
Use the command button wizard to create a button that opens your report. Then
modify the code to something like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboMMMYY) Then
strWhere = strWhere & " And [MthBilledField] = """ & _
Me.cboMMMYY & """"
End If
DoCmd.OpenReport "rptBilling", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


:

Good Morning,

Our company bills its client on a monthly basis and once a particular Job is
complete the month billed is typed selected for that job. At the moment i am
creating a seperate report every month but i now know that by entering
[please select month] into the "Month Billed" Column a report can be
generated all on a single report instead of countless reports. However is it
possible to do this by either a button on a form or a dorp down box which
would appear before the report opens? I would rather do it this way because
of a smaller margin for error as the "Month billed" is in the format of "Jan
07", "Feb 07", "Mar 07" etc whereas the user may type January 07 into the
Parameter value box.

Many thanks

Dave

 

[Dave_FFM]

There should be a new line started after the "If ... Then". I also expect
Test1 is the actual name of your report so it must be enclosed in quotes. If
it is a variable, I have no idea where it is getting its value.

Thanks Duane that fixed it. Many thanks for your help