Criteria

[Guest]

I hope I can explain this correctly. I have a query that has a Date Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off to
the report. I can't use the query as the record source in my report because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass that
[date] from the query to the report? Also, is there a better way to do this
than what I am trying?
Thanks!

 

[Tom Ellison]

Dear Theitman:

Could you not create a copy of your query, add the additional columns needed
for the report, and make that the recordsource for the report? The existing
query that you use to export to a text file.

Or have I not understood your question?

Tom Ellison

 

[Guest]

I will get prompted for the [date] twice then when I run the macro, since the
macro runs the query and the report. I'm trying to eliminate that.
Thanks

Dear Theitman:

Could you not create a copy of your query, add the additional columns needed
for the report, and make that the recordsource for the report? The existing
query that you use to export to a text file.

Or have I not understood your question?

Tom Ellison

I hope I can explain this correctly. I have a query that has a Date
Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro
to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off
to
the report. I can't use the query as the record source in my report
because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass
that
[date] from the query to the report? Also, is there a better way to do
this
than what I am trying?
Thanks!

 

[Guest]

Hi.

Also, is there a better way to do this
than what I am trying?

The most common approach is to have the user enter the value on a form, then
click a button to run the query and the report. The query could use the
following syntax:

SELECT *
FROM tblMyTable
WHERE (SomeDate = Forms!MyForm.FromDate);

.... where tblMyTable is the name of the table, SomeDate is the date field in
the table, and MyForm is the name of the open form, and FromDate is the field
the form is bound to which has the date displayed.

Here'e an example of the VBA code for the button to run the query and report:

Private Sub RunQryAndRptBtn_Enter()

On Error GoTo ErrHandler

DoCmd.OpenQuery "qryMyQuery", acViewNormal

' Next, call whatever method you used to export your text file.
' A query is recommeded, but there are many ways.

DoCmd.OpenReport "rptMyReport", acViewPreview, , _
"SomeDate = #" & Me!txtFromDate.Value & "#"

Exit Sub

ErrHandler:

MsgBox "Error in RunQryAndRptBtn_Enter( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.... where qryMyQuery is the name of the query, rptMyReport is the name of
the report, and txtFromDate is the name of the text box displaying the
desired date.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I hope I can explain this correctly. I have a query that has a Date Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off to
the report. I can't use the query as the record source in my report because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass that
[date] from the query to the report? Also, is there a better way to do this
than what I am trying?
Thanks!

 

[Guest]

My 2 cents worth....


Notice Gunny code has "FromDate" and "SomeDate".

"Date" is a reserved word (the Date() function) and should not be used as an
object name. Search Google Groups for "Reserved Words"; you should find
hundreds of posts about why this is a no-no.

Also, it is not a very descriptive name; is it HireDate, FireDate,
EffectiveDate, StartDate, EndDate, .... , etc...???

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi.

Also, is there a better way to do this
than what I am trying?

The most common approach is to have the user enter the value on a form, then
click a button to run the query and the report. The query could use the
following syntax:

SELECT *
FROM tblMyTable
WHERE (SomeDate = Forms!MyForm.FromDate);

... where tblMyTable is the name of the table, SomeDate is the date field in
the table, and MyForm is the name of the open form, and FromDate is the field
the form is bound to which has the date displayed.

Here'e an example of the VBA code for the button to run the query and report:

Private Sub RunQryAndRptBtn_Enter()

On Error GoTo ErrHandler

DoCmd.OpenQuery "qryMyQuery", acViewNormal

' Next, call whatever method you used to export your text file.
' A query is recommeded, but there are many ways.

DoCmd.OpenReport "rptMyReport", acViewPreview, , _
"SomeDate = #" & Me!txtFromDate.Value & "#"

Exit Sub

ErrHandler:

MsgBox "Error in RunQryAndRptBtn_Enter( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

... where qryMyQuery is the name of the query, rptMyReport is the name of
the report, and txtFromDate is the name of the text box displaying the
desired date.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I hope I can explain this correctly. I have a query that has a Date Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off to
the report. I can't use the query as the record source in my report because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass that
[date] from the query to the report? Also, is there a better way to do this
than what I am trying?
Thanks!

 

[Guest]

First I'll suggest remaining the Date to another name, Date is a reseved name
in access.

To display the Date of the criteria in a field in the report, create a field
and in the control source write
=[date]

 

[Guest]

Please ignore my post, I'm totally off track.
--
I hope that helped
Good luck

First I'll suggest remaining the Date to another name, Date is a reseved name
in access.

To display the Date of the criteria in a field in the report, create a field
and in the control source write
=[date]

--
I hope that helped
Good luck

I hope I can explain this correctly. I have a query that has a Date Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off to
the report. I can't use the query as the record source in my report because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass that
[date] from the query to the report? Also, is there a better way to do this
than what I am trying?
Thanks!

 

[John Spencer (MVP)]

Dear Mr Ellison,

Been a long time. Welcome back.

John Spencer

Dear Theitman:

Could you not create a copy of your query, add the additional columns needed
for the report, and make that the recordsource for the report? The existing
query that you use to export to a text file.

Or have I not understood your question?

Tom Ellison

I hope I can explain this correctly. I have a query that has a Date
Entered
field. The criteria for date entered is [date], so that when you run the
query you will be prompted for the date. That works. I created a macro
to
call the query, export to a text file, then print a report. That works.

My problem is that I don't know how to pass the [date] from the query off
to
the report. I can't use the query as the record source in my report
because
it does not have the same fields as my query contains. I cannot add the
fields on the report to the query because I don't want the fields from the
report to be exported to my text file. Is there some way for me to pass
that
[date] from the query to the report? Also, is there a better way to do
this
than what I am trying?
Thanks!

 

[Duane Hookom]

I second that! I hope you are well...
We'll raise a glass to you this week at the Summit...

--
Duane Hookom
MS Access MVP
--

Dear Mr Ellison,

Been a long time. Welcome back.

John Spencer

Dear Theitman:

Could you not create a copy of your query, add the additional columns
needed
for the report, and make that the recordsource for the report? The
existing
query that you use to export to a text file.

Or have I not understood your question?

Tom Ellison

I hope I can explain this correctly. I have a query that has a Date
Entered
field. The criteria for date entered is [date], so that when you run
the
query you will be prompted for the date. That works. I created a
macro
to
call the query, export to a text file, then print a report. That
works.

My problem is that I don't know how to pass the [date] from the query
off
to
the report. I can't use the query as the record source in my report
because
it does not have the same fields as my query contains. I cannot add
the
fields on the report to the query because I don't want the fields from
the
report to be exported to my text file. Is there some way for me to
pass
that
[date] from the query to the report? Also, is there a better way to do
this
than what I am trying?
Thanks!

 

[Tom Ellison]

Wish I were there!

Tom

I second that! I hope you are well...
We'll raise a glass to you this week at the Summit...

--
Duane Hookom
MS Access MVP
--

Dear Mr Ellison,

Been a long time. Welcome back.

John Spencer

Dear Theitman:

Could you not create a copy of your query, add the additional columns
needed
for the report, and make that the recordsource for the report? The
existing
query that you use to export to a text file.

Or have I not understood your question?

Tom Ellison

I hope I can explain this correctly. I have a query that has a Date
Entered
field. The criteria for date entered is [date], so that when you run
the
query you will be prompted for the date. That works. I created a
macro
to
call the query, export to a text file, then print a report. That
works.

My problem is that I don't know how to pass the [date] from the query
off
to
the report. I can't use the query as the record source in my report
because
it does not have the same fields as my query contains. I cannot add
the
fields on the report to the query because I don't want the fields from
the
report to be exported to my text file. Is there some way for me to
pass
that
[date] from the query to the report? Also, is there a better way to
do
this
than what I am trying?
Thanks!