Running report from input form

T

Tony Williams

I have a form that the user chooses date parameters to run a report. The
code behind the OK button is
Private Sub cmdok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "rptMainSenseCheck"
strField = "txtmonth"

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtenddate,
conDateFormat)
End If
Else
If IsNull(Me.txtenddate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtstartdate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub


However I want them to also have the option of choosing another parameter
which will be a company name from a combo box called cmbselectcompany. The
combo box will be based on the control cmbCompany in a table tblcompany. Can
anyone help me with the additional code I need to add this choice. If the
user doesn't choose a company then I want the report to show all company's.

TIA
Tony Williams
 
M

Marshall Barton

Tony said:
I have a form that the user chooses date parameters to run a report. The
code behind the OK button is
Private Sub cmdok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "rptMainSenseCheck"
strField = "txtmonth"

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtenddate,
conDateFormat)
End If
Else
If IsNull(Me.txtenddate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtstartdate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub


However I want them to also have the option of choosing another parameter
which will be a company name from a combo box called cmbselectcompany. The
combo box will be based on the control cmbCompany in a table tblcompany. Can
anyone help me with the additional code I need to add this choice. If the
user doesn't choose a company then I want the report to show all company's.
Click to expand...


That's very nice code you have there, so I'm having trouble
reconciling it with the question you're asking. Adding
another criteria is just a matter of doing more of the same
kind of thing for the combo box:

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then
strWhere = strWhere & "AND " & strField _
& " < " & Format(Me.txtenddate,conDateFormat)
End If
Else
If IsNull(Me.txtenddate) Then
strWhere = strWhere & "AND " & strField _
& " > " & Format(Me.txtstartdate,conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & "AND " & strField _
& " Between " _
& Format(Me.txtstartdate,conDateFormat) _
& " And " _
& Format(Me.txtenddate,conDateFormat)
End If
End If

If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = " _
& Me.cmbselectcompany
End If
strWhere = Mid(strWhere, 6) 'get rid of extra AND
' Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
T

Tony Williams

Thanks Marsh. I can't take the credit for the code as I have to admit that
it's not my code. Someone posted this on the newsgroup and I was very
thankful for that. However being a newbie I didn't fully understand what was
going on only that it worked hence the reason for the extra question. Now
that you've added that extra bit I think I now understand what's happening.
This is a great way to learn!
Tony Williams
 
M

Marshall Barton

OK, I should have guessed that's what was going on. Glad to
hear that you're gaining an understanding of this stuff.

Did you get it working the way you want?
 
T

Tony Williams

Sorry to take so long to get back to you Marsh but had to spend a couple of
days on other priories!
No it didn't work I'm afraid I get run time error 3075 and the message

Syntax error (missing operator) in query expression '(xtmonth
Between#03/31/02# And #06/30/04 AND cmbCompany=CapitalBank)'.

When I open the VBA window and hold the cursor over the last value of string
it shows
strwhere ='xtmonth Between#03/31/02# And #06/30/04 AND
cmbCompany=CapitalBank'

where is the xtmonth coming from?
Can you help
Tony
Marshall Barton said:
OK, I should have guessed that's what was going on. Glad to
hear that you're gaining an understanding of this stuff.

Did you get it working the way you want?
--
Marsh
MVP [MS Access]



Tony said:
Thanks Marsh. I can't take the credit for the code as I have to admit that
it's not my code. Someone posted this on the newsgroup and I was very
thankful for that. However being a newbie I didn't fully understand what was
going on only that it worked hence the reason for the extra question. Now
that you've added that extra bit I think I now understand what's happening.
This is a great way to learn!

tblcompany.
Can
Click to expand...
Click to expand...
 
M

Marshall Barton

xtmonth is the truncated value of txtMonth that's assigned
to the strField variable. The initial t has been removed by
the Mid function because there is a missing space in front
of the AND in two places. Please make sure that each of the
assignments look like:
strWhere = strWhere & " AND " & strField _

Another problem is that apparently the cmbCompany field in
the table is a Text type field. (I won't go into how
strange it is to have a table field named cmbCompany.) If
the field is, in fact, a Text field, then the last If should
be:

If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = """ _
& Me.cmbselectcompany & """"
End If
 
T

Tony Williams

Thanks Marsh that worked great,. Thanks for your time and patience
Tony
Marshall Barton said:
xtmonth is the truncated value of txtMonth that's assigned
to the strField variable. The initial t has been removed by
the Mid function because there is a missing space in front
of the AND in two places. Please make sure that each of the
assignments look like:
strWhere = strWhere & " AND " & strField _

Another problem is that apparently the cmbCompany field in
the table is a Text type field. (I won't go into how
strange it is to have a table field named cmbCompany.) If
the field is, in fact, a Text field, then the last If should
be:

If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = """ _
& Me.cmbselectcompany & """"
End If
--
Marsh
MVP [MS Access]


Tony said:
Sorry to take so long to get back to you Marsh but had to spend a couple of
days on other priories!
No it didn't work I'm afraid I get run time error 3075 and the message

Syntax error (missing operator) in query expression '(xtmonth
Between#03/31/02# And #06/30/04 AND cmbCompany=CapitalBank)'.

When I open the VBA window and hold the cursor over the last value of string
it shows
strwhere ='xtmonth Between#03/31/02# And #06/30/04 AND
cmbCompany=CapitalBank'

where is the xtmonth coming from?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine Code 2
Adding another Text Box for a Report 1
Help with one of allen browne's tips for limiting a report to dates 6
visual basic 1
How do I get a filter to apply to all my subreports? 1
No data report 2
Syntax Error 3075 1
Date error 1

Top