Re: Convert Numeric field to Date

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Fri, 10 Aug 2007 12:50:00 -0700, Access Newb <Access
Newb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

Relevant Pages

  • Re: Append/Update Query
    ... convert the information to mm/dd/yyyy format as Date/Time. ... to number before running the update query. ... Then run an update query to change the date from yyyymmdd ... Finally I run an append query to add the daily records to the ...
    (microsoft.public.access.queries)
  • Re: Unbound Form making an ODBC call to a DB2 database?
    ... front-end to a DB2 database and i'm trying to create a query which ... will return results from the DB2 database between certain date ... specify them as DateTime types you might be able to eliminate the CDate() ... functions around the form references. ...
    (microsoft.public.access.queries)
  • Re: Duplicate Records Question
    ... you must use mm/dd/yyyy format. ... The "List by date" parameter query below I'm told will give results but ... For dtmCurr = Me.txtDateFrom To Me.txtDateTo ... I have a duplicate button on my form, so if the same visitor is expected ...
    (microsoft.public.access.gettingstarted)
  • Re: Convert Numeric field to Date
    ... University of Maryland Baltimore County ... Access Newb wrote: ... What I am trying to do is display customer request date and actual ship date in mm/dd/yyyy format on one query. ...
    (microsoft.public.access.queries)
  • Re: EXTRACT RECORDS BASED ON A DATE FIELD
    ... Try using the mm/dd/yyyy format. ... Dan Artuso, Access MVP ... > nothing appears in my query. ...
    (microsoft.public.access.gettingstarted)