search Form - date problem

[zSplash]

I've been struggling with this problem for some time, and can't figure it
out: My searchForm has an unbound txtBox1 for "DateClosed". This txtBox1
is supposed to get data of the format "123005" (which means 12/30/05, not
10/09/2236).

When I input 123005, and try to filter the underlying table's "ClosedDate"
field (which is identified as a date field in the table), I am required to
re-enter the "DateClosed" data previously entered in txtBox1. (my
underlying search string is: "([DateClosed] = 12/30/05)" )

Nothing is found. I know entries exist for 12/30/05, but none exist for
10/09/2236, so I'm thinking when I enter the data the second time around, it
is looking for the 10/09/2236 date.

I can't figure out why it's asking for input a second time. Also, how do I
make it search for the date 12/30/05. I hope this is clear enough for
someone to understand the problem. Excuse my faults, please.

TIA

 

[zSplash]

Thank you so much, Ken! Outstanding explanation, even for this dumbskull.
Too sweet!!
I can't stand it! : ) [You made by day/week!]

st.

The date/time data type in Access implements date/time values as a 64 bit
floating point number as an offset from 30 December 1899 00:00:00. Your
value 123005 is indeed interpreted as 9 October 2236. What you can do is
parse the string to return a true date time value. You can do this in
various ways, e.g. with the DateSerial function:

DateSerial(Right("123005",2),Left("123005",2),Mid("123005",3,2))

returns 30 December 2005.

You don't say how you are filtering the data, but if for instance its via
a
parameter on the Date Closed column in a query which references the text
box
on the form, then the parameter would be:

DateSerial(Right(Forms!YourForm!YourTextBox,2),Left(Forms!YourForm!YourTextBox,2),Mid(Forms!YourForm!YourTextBox,3,2))

Another way would be to use the Format property in the parameter to insert
the slashes into the value entered in the text box:

Format(Forms!YourForm!YourTextBox,"00/00/00")

but, using this, it would be prudent to declare the parameter in the query
as DateTime as otherwise it might be interpreted as an arithmetic
expression,
which with your example would evaluate to a date/time value of:

30 December 1899 01:01:12

which equates to the result of the arithmetical expression 12/30/05, which
is 0.08

BTW date literals must be delimited with the # character and in US short
date or an otherwise internationally unambiguous format, e.g.

[DateClosed] = #12/30/05#

Ken Sheridan
Stafford, England

I've been struggling with this problem for some time, and can't figure it
out: My searchForm has an unbound txtBox1 for "DateClosed". This
txtBox1
is supposed to get data of the format "123005" (which means 12/30/05, not
10/09/2236).

When I input 123005, and try to filter the underlying table's
"ClosedDate"
field (which is identified as a date field in the table), I am required
to
re-enter the "DateClosed" data previously entered in txtBox1. (my
underlying search string is: "([DateClosed] = 12/30/05)" )

Nothing is found. I know entries exist for 12/30/05, but none exist for
10/09/2236, so I'm thinking when I enter the data the second time around,
it
is looking for the 10/09/2236 date.

I can't figure out why it's asking for input a second time. Also, how do
I
make it search for the date 12/30/05. I hope this is clear enough for
someone to understand the problem. Excuse my faults, please.

TIA