Convert Numeric field to Date


G

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.
 
Ad

Advertisements

K

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)
 
G

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.
 
J

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]
 
K

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.
 
J

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
'====================================================
 
Ad

Advertisements

G

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.
 
J

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
..
 
Ad

Advertisements

J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top