Dates and Forms Wierd results

[Drayton T.]

Good afternoon all,
I'm having a rather strange issue with a form and dates....

I have the dates held in two text boxes, both with shortdate format and
unbound.
I use this to hold dates for reporting purposes. Here's the strange
part...If I enter in, say, 12/1/2007 as begin and 12/31/2007 as end, the
dates are supplied to the query and it runs, but If I cross the end of the
year and enter 1/1/2008 as begin and 1/31/2008 as end, I get either one of
two errors...1 being "Property Not Found" or 2 being "User Canceled
Operation". There is data in the base table that fits either set of date
criteria. I have no missing references that I can tell. I wanted to see the
actual values being passed by these two textboxes, so I created a query in
design view and entered in the following:
Begin:[forms]![frmReports]![txtbegindate] which should have passed my date,
but instead I get what appears to be two carrige returns (two small squares).
What Gives????

 

[J_Goddard via AccessMonster.com]

Hi -

How are you passing the data in the text boxes to the report (I assume it is
a report, since you said "reporting")

The query you wrote to check the values won't work if the form is not open
when you run the query. It might be easier to use the MsgBox function from
within the form to confirm the date values.

Remember, too, that the format property of the textboxes affects only how the
data is displayed in the form text box, not the format used when you
reference the textbox in code.

HTH

John

Good afternoon all,
I'm having a rather strange issue with a form and dates....

I have the dates held in two text boxes, both with shortdate format and
unbound.
I use this to hold dates for reporting purposes. Here's the strange
part...If I enter in, say, 12/1/2007 as begin and 12/31/2007 as end, the
dates are supplied to the query and it runs, but If I cross the end of the
year and enter 1/1/2008 as begin and 1/31/2008 as end, I get either one of
two errors...1 being "Property Not Found" or 2 being "User Canceled
Operation". There is data in the base table that fits either set of date
criteria. I have no missing references that I can tell. I wanted to see the
actual values being passed by these two textboxes, so I created a query in
design view and entered in the following:
Begin:[forms]![frmReports]![txtbegindate] which should have passed my date,
but instead I get what appears to be two carrige returns (two small squares).
What Gives????

 

[Drayton T.]

I guess some clarification is in order...although I said report, it is
actually just a query that gets exported to excel. The form remains open so
that the values are available. In this instance, I am not referring to these
values via code, but in the criteria line within the access queuy design
grid. The point is: When I use dates that occur in 2007, it works, when I use
dates that occur in 2008 it does not. Again, I have data that meets the
criteria either way, be the date in 2007 or 2008. I have been using this
method for over 8 years and never had this happen til now. Thanks for your
prompt response J!

D.

Hi -

How are you passing the data in the text boxes to the report (I assume it is
a report, since you said "reporting")

The query you wrote to check the values won't work if the form is not open
when you run the query. It might be easier to use the MsgBox function from
within the form to confirm the date values.

Remember, too, that the format property of the textboxes affects only how the
data is displayed in the form text box, not the format used when you
reference the textbox in code.

HTH

John

Good afternoon all,
I'm having a rather strange issue with a form and dates....

I have the dates held in two text boxes, both with shortdate format and
unbound.
I use this to hold dates for reporting purposes. Here's the strange
part...If I enter in, say, 12/1/2007 as begin and 12/31/2007 as end, the
dates are supplied to the query and it runs, but If I cross the end of the
year and enter 1/1/2008 as begin and 1/31/2008 as end, I get either one of
two errors...1 being "Property Not Found" or 2 being "User Canceled
Operation". There is data in the base table that fits either set of date
criteria. I have no missing references that I can tell. I wanted to see the
actual values being passed by these two textboxes, so I created a query in
design view and entered in the following:
Begin:[forms]![frmReports]![txtbegindate] which should have passed my date,
but instead I get what appears to be two carrige returns (two small squares).
What Gives????

 

[John W. Vinson]

Good afternoon all,
I'm having a rather strange issue with a form and dates....

I have the dates held in two text boxes, both with shortdate format and
unbound.
I use this to hold dates for reporting purposes. Here's the strange
part...If I enter in, say, 12/1/2007 as begin and 12/31/2007 as end, the
dates are supplied to the query and it runs, but If I cross the end of the
year and enter 1/1/2008 as begin and 1/31/2008 as end, I get either one of
two errors...1 being "Property Not Found" or 2 being "User Canceled
Operation". There is data in the base table that fits either set of date
criteria. I have no missing references that I can tell. I wanted to see the
actual values being passed by these two textboxes, so I created a query in
design view and entered in the following:
Begin:[forms]![frmReports]![txtbegindate] which should have passed my date,
but instead I get what appears to be two carrige returns (two small squares).
What Gives????

I've resolved this kind of spurious error by using the Parameters dialog in
the query design mode.

Open the query upon which the report is based in design view. Select View...
Parameters, or rightclick the background of the tables window and choose
Parameters. Copy and paste the exact parameter you're using into the left
column and choose Date/Time in the right.

If this doesn't help post back...

John W. Vinson [MVP]