You are using the client combo as the criteria for the date
strCondition = "[Date_Changed] = #" & Me.[cboRun_Rpt_Client] & "#"
Change "cboRun_Rpt_Client" to the date text box name, where the date is
needed as the criteria.
--
Good Luck
BS"D
:
Ofer,
It is working 50% now, the text part (first part of IF statement), but
getting an errot with the date portion. This is how my code looks now:
If Frame4.Value = 1 Then
strCondition = "[GroupBy_Site] Like '" & Me.[cboRun_Rpt_Client] & "'"
Else
strCondition = "[Date_Changed] = #" & Me.[cboRun_Rpt_Client] & "#"
Your line below
MyCondition = "[FieldNameInTable] = #" & Me.[TextBoxOrComboName] & "#"
End If
DoCmd.OpenReport strReportName, acPreview, , strCondition
The error message I am getting is: Syntax error in date in query expression
'([Date_Changed]=##)'
What's wrong with syntax? There is a value in the combobox formatted as
MM/YYYY using the Format property for the combobox. What needs to be
corrected?
Thanks for your help
Kenny A.
:
I actually misunderstood your question, but it happen sometimes.
There is no need to set the SQL of report because you want to change the
report criteria. Instead base the report on the table and then pass the
criteria to the report using the example I gave you.
You will change the SQL of the report if you want to change the Source table
(for example)
Also, you can see examples in this links
Printing the Record on the Microsoft Access Form to a Report:
http://www.databasedev.co.uk/report_from_form_record.html
Or
Print only the current record to a report:
http://www.mvps.org/access/reports/rpt0002.htm
The current record is the same as having a text box or combo in the form
--
Good Luck
BS"D
:
Ofer,
I am not just passing one field to the report, I create SQL in my code
to become the recordsource. Below is a sampling of my code. For ease of
viewing and not to clutter up this message I will only include a couple of
the fields:
If Frame4.Value = 1 Then
strSQL = "SELECT " & _
"tblNew_Removed_Implicit_History.GroupBy_Site, " & _
"tblNew_Removed_Implicit_History.RMTID, " & _
............
"FROM " & _
"tblNew_Removed_Implicit_History " & _
"WHERE " & _
"tblNew_Removed_Implicit_History.GroupBy_Site Like " & _
[Forms]![frmRun_Implicit_Data_History_Report].[cboRun_Rpt_Client].Value & _
" ORDER BY " & _
"tblNew_Removed_Implicit_History.GroupBy_Site; "
Else
strSQL = "SELECT " & _
"tblNew_Removed_Implicit_History.GroupBy_Site, " & _
..........
"tblNew_Removed_Implicit_History.Date_Changed, " & _
"tblNew_Removed_Implicit_History.Status " & _
"FROM " & _
"tblNew_Removed_Implicit_History " & _
"WHERE " & _
"tblNew_Removed_Implicit_History.Date_Changed Like " & _
[Forms]![frmRun_Implicit_Data_History_Report].[cboRun_Rpt_Date].Value & _
" ORDER BY " & _
"tblNew_Removed_Implicit_History.Date_Changed;"
End If
stReportName = "rptImplicit_Data_History"
DoCmd.OpenReport stReportName, acPreview
This Sql needs to be the recordsource for thre report. I'm not sure if you
misunderstood my question or I misunderstood your response. Could you please
clarify for me and help me solve my issue.
Thanks
Kenny A.
:
You need to add a Where Condition to the open report command line
Dim stReportName As String, MyCondition as String
stReportName = "rptImplicit_Data_History"
MyCondition = "[FieldNameInTable] = " & Me.[TextBoxOrComboName]
DoCmd.OpenReport stReportName, acPreview, , MyCondition
***************
If the condition is on a text type field change the condition to
MyCondition = "[FieldNameInTable] = '" & Me.[TextBoxOrComboName] & "'"
Adding single quote
****************
If the condition is on a date type field change the condition to
MyCondition = "[FieldNameInTable] = #" & Me.[TextBoxOrComboName] & "#"
Adding hash before and after
****************
--
Good Luck
BS"D
:
I have a form with 2 option buttons. Each option button has a combobox
associated with it. One option will allow report to run based on client
selection, the other will run same report by a date. I have a command button
to run the report. The code for this command button checks to see which
option has been selected and builds the SQL. The SQL is the same for both
options except one parms inton the Where condition the Client selected, the
other uses the date selected. This is fine. My problem is, how do I tell the
report to open in preview using the SQL that was built in the code? I
initially used the command button wizard to build the button and open the
report I wanted which has table as it's datasource. This is the correct
table, but now instead of just a dump direct from the table, I want to use
the SQL from the code to be its datasource. My variable containing the SQL
is strSQL and not sure where it fits into the code built by the wizard:
stReportName = "rptImplicit_Data_History"
DoCmd.OpenReport stReportName, acPreview
This just opens the report with all data from the table (based on fields
shown in report).
Thanks
Kenny A.
