Criteria Select passed to Report

[Guest]

I have a purchase log and a user that would like to see all of the parts
purchased up to and including a certain date. I created a query where it
prompts him for a date. Once he enters the date, the report displays all
parts purchased up to and including the date selected.

What I would like to do is somehow pass that date selection to the report so
that the report header reads 'Parts Purchased as of mm/dd/yy with mm/dd/yy
being the date he entered as criteria for the query.

Your kind assistance and insight would be most appreciated.

 

[Joan Wild]

If you mean you set a parameter in the query, you can just repeat the
parameter in a textbox on the report. In the Control Source of the textbox,
something like
="Parts Purchased as of " & Format([Enter Date], "mm/dd/yy")

May I suggest though that you change the format to mmm/dd/yyyy. Dates can
be ambiguous using 2-digits for the month/day and year, for that matter.

Also you need to understand that the date the user enters may be interpreted
by Access in a different way than the user intended.

I'm not sure if 01/08/06 is August 1 or Jan 8, or what? Access does not
make random decisions about this, it uses the short
date settings on your computer to assume what you mean by your entry.

Go into Control Panel, Regional Settings, Date tab, and see what your
computer is set to. If it is
m/d/y and you enter 08/05/06, it will assume you mean Aug 5. However if you
enter 13/05/06, that
doesn't conform to your Regional Settings (there is no 13th month). Access
will still enter a date
for you though - it 'helpfully' spins your entry around until it can make
sense of it - May 13,2003.
This is why it can be very confusing to users, as it would appear that
Access arbitrarily changes
your date entries. It's really only doing this after the 12th of the month.

For more information on dates see
http://members.iinet.net.au/~allenbrowne/ser-36.html

By using the mmm/dd/yyyy format, the user will see right away how Access
interpreted their entry. This is especially useful if the user is unaware
of their Regional Settings.

 

[fredg]

I have a purchase log and a user that would like to see all of the parts
purchased up to and including a certain date. I created a query where it
prompts him for a date. Once he enters the date, the report displays all
parts purchased up to and including the date selected.

What I would like to do is somehow pass that date selection to the report so
that the report header reads 'Parts Purchased as of mm/dd/yy with mm/dd/yy
being the date he entered as criteria for the query.

Your kind assistance and insight would be most appreciated.

If the query criteria is [FromDate], then add an unbound control to
the report header.
Set it's control source to:
= "Parts Purchased as of " & [FromDate]

The text within the brackets must be identical to the bracketed
parameter prompt in the query.

 

[Guest]

I am running Access Project with SQL Server tables, views and functions on
the backend. In my Function, I am using the statement <= @AsofDate to
query the user to enter a date. I then created a control on my report and in
the control source I entered: = "Parts Purchased as of " & [AsofDate].
When I ran it, it prompted me for the 'AsofDate' and then gave me an error
message 'invalid column name 'AsofDate'. Is ADP different from MDB?

I have a purchase log and a user that would like to see all of the parts
purchased up to and including a certain date. I created a query where it
prompts him for a date. Once he enters the date, the report displays all
parts purchased up to and including the date selected.

What I would like to do is somehow pass that date selection to the report so
that the report header reads 'Parts Purchased as of mm/dd/yy with mm/dd/yy
being the date he entered as criteria for the query.

Your kind assistance and insight would be most appreciated.

If the query criteria is [FromDate], then add an unbound control to
the report header.
Set it's control source to:
= "Parts Purchased as of " & [FromDate]

The text within the brackets must be identical to the bracketed
parameter prompt in the query.

 

[Guest]

Thanks, Joan.

I'm not sure it if matters but I am using Access Project with a SQL backend.
My FUNCTION uses <=@AsofDate to prompt the user for a date. I entered
="Parts Purchased as of" & Format([AsofDate], "mm/dd/yy") in the Control
Source of a text box and got the error message [Invalid Column Name
'AsofDate']. Is ADP different than MDB regarding reports and data sources?

If you mean you set a parameter in the query, you can just repeat the
parameter in a textbox on the report. In the Control Source of the textbox,
something like
="Parts Purchased as of " & Format([Enter Date], "mm/dd/yy")

May I suggest though that you change the format to mmm/dd/yyyy. Dates can
be ambiguous using 2-digits for the month/day and year, for that matter.

Also you need to understand that the date the user enters may be interpreted
by Access in a different way than the user intended.

I'm not sure if 01/08/06 is August 1 or Jan 8, or what? Access does not
make random decisions about this, it uses the short
date settings on your computer to assume what you mean by your entry.

Go into Control Panel, Regional Settings, Date tab, and see what your
computer is set to. If it is
m/d/y and you enter 08/05/06, it will assume you mean Aug 5. However if you
enter 13/05/06, that
doesn't conform to your Regional Settings (there is no 13th month). Access
will still enter a date
for you though - it 'helpfully' spins your entry around until it can make
sense of it - May 13,2003.
This is why it can be very confusing to users, as it would appear that
Access arbitrarily changes
your date entries. It's really only doing this after the 12th of the month.

For more information on dates see
http://members.iinet.net.au/~allenbrowne/ser-36.html

By using the mmm/dd/yyyy format, the user will see right away how Access
interpreted their entry. This is especially useful if the user is unaware
of their Regional Settings.

--
Joan Wild
Microsoft Access MVP

I have a purchase log and a user that would like to see all of the
parts purchased up to and including a certain date. I created a
query where it prompts him for a date. Once he enters the date, the
report displays all parts purchased up to and including the date
selected.

What I would like to do is somehow pass that date selection to the
report so that the report header reads 'Parts Purchased as of
mm/dd/yy with mm/dd/yy being the date he entered as criteria for the
query.

Your kind assistance and insight would be most appreciated.

 

[Joan Wild]

Sorry, I have no experience with ADP's.

Another approach is to create an unbound form with textboxes to gather user
input. If you can reference these in your function instead, you can
reference them on your report as well.
="Parts Purchased as of " & Format(Forms!frmWhatever!NameOfTextbox,
"mm/dd/yy")


--
Joan Wild
Microsoft Access MVP

Thanks, Joan.

I'm not sure it if matters but I am using Access Project with a SQL
backend. My FUNCTION uses <=@AsofDate to prompt the user for a date.
I entered ="Parts Purchased as of" & Format([AsofDate], "mm/dd/yy")
in the Control Source of a text box and got the error message
[Invalid Column Name 'AsofDate']. Is ADP different than MDB
regarding reports and data sources?

If you mean you set a parameter in the query, you can just repeat the
parameter in a textbox on the report. In the Control Source of the
textbox, something like
="Parts Purchased as of " & Format([Enter Date], "mm/dd/yy")

May I suggest though that you change the format to mmm/dd/yyyy.
Dates can be ambiguous using 2-digits for the month/day and year,
for that matter.

Also you need to understand that the date the user enters may be
interpreted by Access in a different way than the user intended.

I'm not sure if 01/08/06 is August 1 or Jan 8, or what? Access does
not make random decisions about this, it uses the short
date settings on your computer to assume what you mean by your entry.

Go into Control Panel, Regional Settings, Date tab, and see what your
computer is set to. If it is
m/d/y and you enter 08/05/06, it will assume you mean Aug 5.
However if you enter 13/05/06, that
doesn't conform to your Regional Settings (there is no 13th month).
Access will still enter a date
for you though - it 'helpfully' spins your entry around until it can
make sense of it - May 13,2003.
This is why it can be very confusing to users, as it would appear
that Access arbitrarily changes
your date entries. It's really only doing this after the 12th of
the month.

For more information on dates see
http://members.iinet.net.au/~allenbrowne/ser-36.html

By using the mmm/dd/yyyy format, the user will see right away how
Access interpreted their entry. This is especially useful if the
user is unaware of their Regional Settings.

--
Joan Wild
Microsoft Access MVP

I have a purchase log and a user that would like to see all of the
parts purchased up to and including a certain date. I created a
query where it prompts him for a date. Once he enters the date,
the report displays all parts purchased up to and including the date
selected.

What I would like to do is somehow pass that date selection to the
report so that the report header reads 'Parts Purchased as of
mm/dd/yy with mm/dd/yy being the date he entered as criteria for the
query.

Your kind assistance and insight would be most appreciated.

 

[Guest]

Thanks anyway, Joan. The unbound form does not work in the Function. I have
another open post in the SQL forum asking if there is a SQL equivalent to the
forms!frmWhatever!NameOfTextBox statement in an Access Query. Oh well. I'll
keep trying. Thanks again.

Sorry, I have no experience with ADP's.

Another approach is to create an unbound form with textboxes to gather user
input. If you can reference these in your function instead, you can
reference them on your report as well.
="Parts Purchased as of " & Format(Forms!frmWhatever!NameOfTextbox,
"mm/dd/yy")


--
Joan Wild
Microsoft Access MVP

Thanks, Joan.

I'm not sure it if matters but I am using Access Project with a SQL
backend. My FUNCTION uses <=@AsofDate to prompt the user for a date.
I entered ="Parts Purchased as of" & Format([AsofDate], "mm/dd/yy")
in the Control Source of a text box and got the error message
[Invalid Column Name 'AsofDate']. Is ADP different than MDB
regarding reports and data sources?

If you mean you set a parameter in the query, you can just repeat the
parameter in a textbox on the report. In the Control Source of the
textbox, something like
="Parts Purchased as of " & Format([Enter Date], "mm/dd/yy")

May I suggest though that you change the format to mmm/dd/yyyy.
Dates can be ambiguous using 2-digits for the month/day and year,
for that matter.

Also you need to understand that the date the user enters may be
interpreted by Access in a different way than the user intended.

I'm not sure if 01/08/06 is August 1 or Jan 8, or what? Access does
not make random decisions about this, it uses the short
date settings on your computer to assume what you mean by your entry.

Go into Control Panel, Regional Settings, Date tab, and see what your
computer is set to. If it is
m/d/y and you enter 08/05/06, it will assume you mean Aug 5.
However if you enter 13/05/06, that
doesn't conform to your Regional Settings (there is no 13th month).
Access will still enter a date
for you though - it 'helpfully' spins your entry around until it can
make sense of it - May 13,2003.
This is why it can be very confusing to users, as it would appear
that Access arbitrarily changes
your date entries. It's really only doing this after the 12th of
the month.

For more information on dates see
http://members.iinet.net.au/~allenbrowne/ser-36.html

By using the mmm/dd/yyyy format, the user will see right away how
Access interpreted their entry. This is especially useful if the
user is unaware of their Regional Settings.

--
Joan Wild
Microsoft Access MVP

Ernie Sersen wrote:
I have a purchase log and a user that would like to see all of the
parts purchased up to and including a certain date. I created a
query where it prompts him for a date. Once he enters the date,
the report displays all parts purchased up to and including the date
selected.

What I would like to do is somehow pass that date selection to the
report so that the report header reads 'Parts Purchased as of
mm/dd/yy with mm/dd/yy being the date he entered as criteria for the
query.

Your kind assistance and insight would be most appreciated.