query from a form Date as number YYYYMMDD

[AngieSD]

I have a clients who's dates in thier database are all stored as numbers
formatted YYYYMMDD (always 8 characters) I would like to run a query from a
form with the date parameter entered by the user as a date then convert it in
the query as the number format needed. I can convert the number to date with
DateSerial but I don't know how to convert the date to number with the
correct format.

Thanks!

Angie

 

[Klatuu]

Rather than try to derive the number of the date, format the number as a date.
In this example, x = the numeric field that carries the date(in about the
strangest way I have ever seen a date carried)

dateserial(left(cstr(x),4),mid(cstr(x),5,2),right(cstr(x),2))

 

[AngieSD]

Thanks for the fast response, and of course that would be the easy way to do
it, but it is a large ERP application and all of the dates in all the tables
are formatted this way, and I guess I could do a make table changing the
dates first but there are thousands of records.

I'm trying to find a way to take the date entered in the form and get it
back to a number formatted YYYYMMDD

 

[Duane Hookom]

You can take a date value and convert it like:
Year([Date Expression]) * 10000 + Month([Date Expression])* 100 + Day([Date
Expression])

 

[AngieSD]

I reformatted the form fields with this and it worked perfectly!! Thanks so
much!

You can take a date value and convert it like:
Year([Date Expression]) * 10000 + Month([Date Expression])* 100 + Day([Date
Expression])

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thanks for the fast response, and of course that would be the easy way to do
it, but it is a large ERP application and all of the dates in all the tables
are formatted this way, and I guess I could do a make table changing the
dates first but there are thousands of records.

I'm trying to find a way to take the date entered in the form and get it
back to a number formatted YYYYMMDD

 

[George Nicholson]

One approach:

Format(Year([Enter Date]),"0000") & Format(Month([Enter Date]),"00") &
Format(Day([Enter Date]),"00")

Note: since you can't (reliably) do math on the contents of this field, you
might consider stop thinking of it as a number. It isn't. Its text. An
alpha-numeric representation of a date - that just doesn't happen to contain
any alpha characters. It will sort like a dream, just like a dictionary, but
that's it. This belongs in the same class as other "numbers that are really
text" like social security numbers, credit card numbers, zip codes, phone
numbers, etc.

I agree that you are probably better off leaving the field in the clients'
format. If you find that you need to do math on the contents you will have
to write your own functions that convert the contents to actual date values
(using DateSerial), do the desired calculation with DateDiff() or its ilk,
and return the result.

 

[John Spencer]

I might point out that you can convert a date to a long fairly easily
CLng(Format(SomeDateField,"yyyymmdd"))

Now that will fail if SomeDate is null, so you need to test for nulls. In a
query you can use an immediate if (IIF), in VBA you would have to use an If
construct.

IIF(SomeDateField is Null, Null, CLng(Format(SomeDateField,"yyyymmdd"))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

One approach:

Format(Year([Enter Date]),"0000") & Format(Month([Enter Date]),"00") &
Format(Day([Enter Date]),"00")

Note: since you can't (reliably) do math on the contents of this field,
you might consider stop thinking of it as a number. It isn't. Its text. An
alpha-numeric representation of a date - that just doesn't happen to
contain any alpha characters. It will sort like a dream, just like a
dictionary, but that's it. This belongs in the same class as other
"numbers that are really text" like social security numbers, credit card
numbers, zip codes, phone numbers, etc.

I agree that you are probably better off leaving the field in the clients'
format. If you find that you need to do math on the contents you will have
to write your own functions that convert the contents to actual date
values (using DateSerial), do the desired calculation with DateDiff() or
its ilk, and return the result.

--
HTH,
George

Thanks for the fast response, and of course that would be the easy way to
do
it, but it is a large ERP application and all of the dates in all the
tables
are formatted this way, and I guess I could do a make table changing the
dates first but there are thousands of records.

I'm trying to find a way to take the date entered in the form and get it
back to a number formatted YYYYMMDD