How do I make SQL within Code become report source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.

Ofer Cohen said:
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


Kenny A. said:
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.
Click to expand...
Click to expand...
 
I think you misunderstood my wild card question. I am able to enter an * in
the client combobox and this brings me back all records on the table ( * is a
wild card and this is ok). I was just wondering if there is a wild card for
use in a date field, so
you could select all from the dtae field?

Kenny

Ofer Cohen said:
Try in the date

strCondition = "[Date_Changed] = " & Format(Day,"\#mm\/dd\/yyyy\#")

Also, if the date field has time in it, use DateValue function to remove the
time

strCondition = "DateValue([Date_Changed]) = " & Format(Day,"\#mm\/dd\/yyyy\#")

About the empty field you can check before the report is open if the text
box is empty, and don't set the WhereCondition

If IsNull(Me.[TextBoxName]) Then
strCondition = ""
Else
strCondition = "[GroupBy_Site] Like '" & Me.[cboRun_Rpt_Client] & "'"
End if

--
Good Luck
BS"D


Kenny A. said:
DUH don't know how I missed that LOL. I fixed that, but now just getting
a blank report. Also another quick question dealing with this. I can place
an * in the text one and brings back all records (good as an option). Is
there a way to enter a wild card in a date field to bring back all records?

Thanks again for all the help you are giving me.
Kenny A.

Ofer Cohen said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Ofer Cohen said:
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


Kenny A. said:
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.

Ofer Cohen said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


Kenny A. said:
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.

Ofer Cohen said:
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


Kenny A. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Try in the date

strCondition = "[Date_Changed] = " & Format(Day,"\#mm\/dd\/yyyy\#")

Also, if the date field has time in it, use DateValue function to remove the
time

strCondition = "DateValue([Date_Changed]) = " & Format(Day,"\#mm\/dd\/yyyy\#")

About the empty field you can check before the report is open if the text
box is empty, and don't set the WhereCondition

If IsNull(Me.[TextBoxName]) Then
strCondition = ""
Else
strCondition = "[GroupBy_Site] Like '" & Me.[cboRun_Rpt_Client] & "'"
End if

--
Good Luck
BS"D


Kenny A. said:
DUH don't know how I missed that LOL. I fixed that, but now just getting
a blank report. Also another quick question dealing with this. I can place
an * in the text one and brings back all records (good as an option). Is
there a way to enter a wild card in a date field to bring back all records?

Thanks again for all the help you are giving me.
Kenny A.

Ofer Cohen said:
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


Kenny A. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
DUH don't know how I missed that LOL. I fixed that, but now just getting
a blank report. Also another quick question dealing with this. I can place
an * in the text one and brings back all records (good as an option). Is
there a way to enter a wild card in a date field to bring back all records?

Thanks again for all the help you are giving me.
Kenny A.

Ofer Cohen said:
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


Kenny A. said:
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.

Ofer Cohen said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Instead of rebuilding the whole query statement, you can just use the
WhereCondition if the field you are filtering on is in the report.

Here's an example of how to filter by an ID number:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

Here's an example of how to filter by a date:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If you do need to rebuild the SQL statement, you must do this in the Open
event of the report. Typically you test if the form IsLoaded, and if so,
read the values from the form, concatenate them into the WHERE clause of the
SQL string, and then assign it to the RecordSource of the report.

Take care with this approach: Access can crash if a bound field disappears
from the source or even if it changes data type (as calculated fields may
do.)
 
Back
Top