Print Report from Form

[Guest]

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

 

[Guest]

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &

Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &

Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &

Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Does it happen that one of the fields is empty, but you need it to return all
the records in that case?

Try
strCriteria = "[Num1]=" & Me.Num1 & _
" And [Text1] Like'" & Nz(Me.Text1,"*") & "'" & _
" and [Text2] Like '" & Nz(Me.Text2,"*") & "'" & _
" and [Text3] Like '" & Nz(Me.Text3,"*") & "'" & _
" and [Num2]=" & Me.Num2 & _
" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

--
HTH, Good Luck
BS"D

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.





:

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

I just tried the code, and I got an error message "The search key was not
found in any record." All those fields are not empty. They always have
values.

Thanks.

Does it happen that one of the fields is empty, but you need it to return all
the records in that case?

Try
strCriteria = "[Num1]=" & Me.Num1 & _
" And [Text1] Like'" & Nz(Me.Text1,"*") & "'" & _
" and [Text2] Like '" & Nz(Me.Text2,"*") & "'" & _
" and [Text3] Like '" & Nz(Me.Text3,"*") & "'" & _
" and [Num2]=" & Me.Num2 & _
" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

--
HTH, Good Luck
BS"D

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D


:

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.





:

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Good morning Ofer,

I have a feeling that every line of the code is working, except the date
line. Based on the scenario that I gave to you, the report was only picking
up the 7/3/04 records because the 7/3/04 is the first date on the form. I
think the code is not recognizing the "Between And" code.

Thanks.

Does it happen that one of the fields is empty, but you need it to return all
the records in that case?

Try
strCriteria = "[Num1]=" & Me.Num1 & _
" And [Text1] Like'" & Nz(Me.Text1,"*") & "'" & _
" and [Text2] Like '" & Nz(Me.Text2,"*") & "'" & _
" and [Text3] Like '" & Nz(Me.Text3,"*") & "'" & _
" and [Num2]=" & Me.Num2 & _
" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

--
HTH, Good Luck
BS"D

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D


:

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.





:

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

It should recognize the between.
I just noticed, but mybe it just and example you gave, the between include
the same date "Me.Date1" should it be like that?

" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"
In that case you can just write
" and [Date1] = #" & Me.Date1 & "#"

In any case you can also try
" and [Date1] >= #" & Me.Date1 & "# And [Date1] < = #" & Me.Date1 & "#"
--
HTH, Good Luck
BS"D

Good morning Ofer,

I have a feeling that every line of the code is working, except the date
line. Based on the scenario that I gave to you, the report was only picking
up the 7/3/04 records because the 7/3/04 is the first date on the form. I
think the code is not recognizing the "Between And" code.

Thanks.

Does it happen that one of the fields is empty, but you need it to return all
the records in that case?

Try
strCriteria = "[Num1]=" & Me.Num1 & _
" And [Text1] Like'" & Nz(Me.Text1,"*") & "'" & _
" and [Text2] Like '" & Nz(Me.Text2,"*") & "'" & _
" and [Text3] Like '" & Nz(Me.Text3,"*") & "'" & _
" and [Num2]=" & Me.Num2 & _
" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

--
HTH, Good Luck
BS"D

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

:

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D


:

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.





:

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Good morning Ofer,

Thanks for your patience and your helps.

I only have one date field (do not have the date fields for beginning and
ending). In the query of my form control source, I have the following
criteria for the date field to prompt the user for the beginning and ending
dates.

Between nz([Please Enter A Beginning Date (Required)],">#01/01/1900#") And
nz([Please Enter An Ending Date(Required)],">#12/31/2500#")

For the text and # fields, I also use the similar criteria as date. For
example,

nz([Please Enter a Text1],"*")

In addition to having problem with date, I think I am having a similar
problem with text and # fields. For example, if I just type in one of the
text criteria (and leave everything blank), the form then shows all the
records associate with the text criteria that I type in. On the other hand,
the report only show the first records that equal to other fields.

Below is the filter criteria that I got from the report after I run it. I
don't know it will help you.

([Num1] >= 1And [text1] >= 'text'And [text2] = 'text'And [text3] >=
'text'And [Num2] >= 1And [date1] >= #7/3/2004# And [date1] <= #7/3/2004#)

For the above filter, I typed in text1, dates (7/1/04 - 7/31/04) and text 3.
The form had 8 records and the report had only 2 records. The report only
shows the records of 7/3/04.

([Num1] >= 1And [text1] >= 'text'And [text2] = 'text'And [text3] >=
'text'And [Num2] >= 1And [date1] >= #7/3/2004# And [date1] <= #7/3/2004#)

For the above filter, I typed in text1 and dates (7/1/04 - 7/3/04). The
form showed 87 records and the report showed 79 records. From my
observation, the form had two different types of text3. The report only
showed the records of one type of text3.

Sorry for the long message. Again, thanks for your helps.

It should recognize the between.
I just noticed, but mybe it just and example you gave, the between include
the same date "Me.Date1" should it be like that?

" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"
In that case you can just write
" and [Date1] = #" & Me.Date1 & "#"

In any case you can also try
" and [Date1] >= #" & Me.Date1 & "# And [Date1] < = #" & Me.Date1 & "#"
--
HTH, Good Luck
BS"D

Good morning Ofer,

I have a feeling that every line of the code is working, except the date
line. Based on the scenario that I gave to you, the report was only picking
up the 7/3/04 records because the 7/3/04 is the first date on the form. I
think the code is not recognizing the "Between And" code.

Thanks.

Does it happen that one of the fields is empty, but you need it to return all
the records in that case?

Try
strCriteria = "[Num1]=" & Me.Num1 & _
" And [Text1] Like'" & Nz(Me.Text1,"*") & "'" & _
" and [Text2] Like '" & Nz(Me.Text2,"*") & "'" & _
" and [Text3] Like '" & Nz(Me.Text3,"*") & "'" & _
" and [Num2]=" & Me.Num2 & _
" and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

--
HTH, Good Luck
BS"D


:

Yes, the control source of the report has no filter. It includes all the
records.

If I run the report independently (without thru form), it will show all the
records from the table where the records are.

Thanks.

:

Does the reprt record source has all the records selected, with no filter?
An extra filter might limit the amount of records before the WherCondition
of the report.

--
HTH, Good Luck
BS"D


:

Hi Ofer,

Thanks for your help. I still need a little more help. Below is the code
that I have. When I click to print the report, it gives me the report, but
the report does not have the same info as on the form. I will give you a
scenario at the end of code.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim strCriteria As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print.
MsgBox "No record to print."
Else
strCriteria = "[Num1]=" & Me.Num1 & _
"And [Text1]='" & Me.Text1 & "'" & _
"and [Text2]='" & Me.Text2 & "'" & _
"and [Text3]='" & Me.Text3 & "'" & _
"and [Num2]=" & Me.Num2 & _
"and [Date1] between #" & Me.Date1 & "# and #" & Me.Date1 & "#"

DoCmd.OpenReport "rptUnarchive_T&E_by_Client_ID", acViewPreview, ,
strCriteria
DoCmd.Close acForm, "frmUnarchive_T&E_by_Client_ID"
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Here is a scenario:

I run the form based on 3 criteria (out of 5): Text1, Text3 and Date1. The
form returns 8 records, but the report shows only 2 records (out of 8).

On the form, since I didn't type in Num1 as one of my criteria, it gives me
2 (the report has just 1). Also I picked the date range (Date1) from
07/01/04 to 07/31/04. The form shows 2 records for 07/03/04, 3 records for
07/10/04 and etc. On the other hand, the report only shows the 2 records for
07/03/04.

Apparently, the report is only picking up the first records that meet all
the criteria.

As far as the code for date range, there is only one field for date. I
don't have a field for Beginning Date and Ending Date (only Date1). I use
the "Between And" in my query to allow the user to type in a date range.

So in the code:

strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

The Beginning Date and Ending Date fields do I have them the same as the
DateField (Date1)?

Again, I really appreciate your help.





:

First try this link

http://www.databasedev.co.uk/report_from_form_record.html

To add to the filter in the example

For Number
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]

For Text add single quote
strCriteria = "[lngSalespersonID]= '" & Me![lngSalespersonID] & "'"

For Date add #
strCriteria = "[lngSalespersonID]= #" & Me![lngSalespersonID] & "#"

To filter on few fields
strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID] & " And
[TextField] = '" & Me![TextFieldName] & "'"

Or for two dates something like
strCriteria = "[DateField] Between #" & Me![Beginning Date] & "# And #" &
Me![Ending Date ] & "#"

--
HTH, Good Luck
BS"D


:

Hello all,

I have a report which prompts the user with 5 parameters (criteria) when the
form is opened. 5 parameters are: Student ID, Student Group, Beginning Date,
Ending Date and Grade. Beginning Date and Ending Date are the only two
required fields.

The form then would show the records based on the selected criteria. I also
have a report created, and I would like to have the report shows the exact
same records from the form. How can I write the code to connect the form and
report to show the exact same records from the form?

Thanks.

 

[Guest]

Try

Between format(nz([Please Enter A Beginning Date (Required)],"01/01/1900"),
"\#mm\/dd\/yyyy\#") And Format(nz([Please Enter An Ending
Date(Required)],"12/31/2500"), "\#mm\/dd\/yyyy\#")

Cheange the date format if you are using "\#dd\/mm\/yyyy\#"

For the text field try
Where [TextField] Like nz([Please Enter a Text1],"*")

 

[Guest]

Good morning Ofer,

I tried your criteria in the form query, and it didn't work for the date
criteria. I got an error message something like "too complex to run".

I am wondering how Access stores the beginning and ending dates parameters
without actual having two different fields. When we type in a beginning and
ending, the form would show the records of the date range. However, when we
try to link it to the report, the report is not recognizing.

Thanks.