Convert Numeric field to Date

[Guest]

Hello,

I've seen similar questions to this but need some help/clarification.

I have Access 2003 with linked tables to a DB2 database. The fields
I am querying are numeric in this format 20070810 (yyyymmdd)

What I am trying to do is display customer request date and
actual ship date in mm/dd/yyyy format on one query.
On another query I am trying to summarize item shipments by month.

I know next to nothing about this. Any help with baby steps would
be greatly appreciated.

 

[Ken Snell \(MVP\)]

Probably easiest to convert the numeric value to a string, then convert to a
date format for display. You say you're doing this in a query, so here is an
example of what the calculated field would be like:

DisplayRequestDate: Mid(CStr([DB2_DateField]), 5, 2) & "/" &
Right(CStr([DB2_DateField]),2) & "/" & Left(CStr([DB2_DateField]), 4)

 

[Guest]

Thanks for the quick reply Ken.

The field name is OARQDT so this is what I did:
Right click on the Query -> Design View
Right click on column -> Build
Put this into Expression Builder window:

DisplayRequestDate: Mid(CStr([OARQDT]), 5, 2) & "/" &
Right(CStr([OARQDT]),2) & "/" & Left(CStr([OARQDT]), 4)

Click OK then Run

I get this error message:

The expression you entered has an invalid . (dot) or ! operator or invalid
parentheses. You may have entered an invalid identifier or typed parentheses
following the Null constant.

What am I doing wrong?

Thanks again.

 

[John W. Vinson]

On Fri, 10 Aug 2007 12:50:00 -0700, Access Newb <Access

Hello,

I've seen similar questions to this but need some help/clarification.

I have Access 2003 with linked tables to a DB2 database. The fields
I am querying are numeric in this format 20070810 (yyyymmdd)

What I am trying to do is display customer request date and
actual ship date in mm/dd/yyyy format on one query.
On another query I am trying to summarize item shipments by month.

I know next to nothing about this. Any help with baby steps would
be greatly appreciated.

I'd suggest using the DateSerial() function to actually generate a date field:

MyDate: DateSerial([thisdate] \ 10000, [thisdate] \ 100 MOD 100, [thisdate]
MOD 100))

to mathematically extract the year, month and day portions. Note that it's the
integer divide operator \ not the more familiar /.

John W. Vinson [MVP]

 

[Ken Snell \(MVP\)]

Is it possible for the OARQDT field to contain a Null value? Assuming that
it is, try this:

DisplayRequestDate: IIf([OARQDT] Is Null, Null, Mid(CStr([OARQDT]), 5, 2) &
"/" &
Right(CStr([OARQDT]),2) & "/" & Left(CStr([OARQDT]), 4))

Also, you should not need to use the Expression Builder to enter this
expression. When you are in the query design view, click in the Field: cell,
and type the above expression into that cell.

 

[John Spencer]

One alternative

IIF(IsNumeric(yourField),CDate(Format(YourField,"0000/00/00")))

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

 

[Guest]

Ken,
Thanks for the clarification for where to put the expression.
Yours worked to change format (mm/dd/yyy) but the column
was still numeric and when I would sort by that field it would
place them in numeric order:
20060720 (07/20/2006) would be after
20070719 (07/19/2007)

John Vinson,
Your response gave a "The expression you entered has
too many closing parenthesis." error. I removed the last
parenthesis and it worked but in the short date format:
20070730 (07-30-07)

John Spencer,
Yours also worked with the short date format but it seems
to be easier to type in.

Is there a way to display the dates in mm/dd/yyyy format?


Thank you all for your help.

 

[John Spencer]

One a form or in a report use the format property to do this.

You can do it in a query by using the Format function, but then if you want
to sort the date, you will need to use a separate column. When you use the
format function, you change the data to a string. Strings sort differently
than dates

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

 

[John W. Vinson]

Your response gave a "The expression you entered has
too many closing parenthesis." error. I removed the last
parenthesis and it worked but in the short date format:
20070730 (07-30-07)

It's giving you a complete Date/Time field. You can set the Format property of
the control in which that field is displayed to any date format you like -
"mm/dd/yyyy" for 07/30/2007, or "dd mmmm, yyyy" for "30 July, 2007".

John W. Vinson [MVP]