Changing Field format in external table

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97
I am creating a table in an external MDB using

DoCmd.RunSQL "SELECT tbldata.JobId , tbldata.CustomerId, tbldata.[Date
Received], tbldata.OrderNo INTO tblHistory From tbldata WHERE
(((tbldata.[Date Received])>=DateSerial(Year(Date()),Month(Date())-6,1)));"

(as a contiguous string)

This works fine.
But the format of the {Date Received] field in the source table is
dd/mm/yy hh:nn:ss. I would like the result to be dd-mmm-yy (or at least
without the Hours / Mins / Seconds).

Can I prescribe the format at the time of creating the table or change
the format in the external table after the event?
I've looked at TableDef information in the Help file but.....

Thanks for your help.

WSF
 
Use the built-in DateValue function to remove time from the value.

DoCmd.RunSQL "SELECT tbldata.JobId , tbldata.CustomerId,
DateValue(tbldata.[Date Received]), tbldata.OrderNo INTO tblHistory From
tbldata WHERE (((tbldata.[Date
Received])>=DateSerial(Year(Date()),Month(Date())-6,1)));"
 
Thanks Douglas,
You answer generated an error along the lines of needing a destination
from the query - I've forgotten the exact wording. However I tried the
ghastly Google and came up with a newsgroup thread of quite some time
ago - which coincidently had you as a contributor!) and gleaned the
following which works:

DoCmd.RunSQL "UPDATE tbldata SET [Date Received] = DateValue([Date
Received])"

Happy New Year

Regards,
Bill
 
WSF said:
... However I tried the
ghastly Google and came up with a newsgroup thread of quite some time
ago - which coincidently had you as a contributor!) and ...
Click to expand...

That's not much of a coincendence ;-)

A quick Google for Doug's contributions over the last eight
years yielded more than 28,000 responses. Doug is a very
busy guy ;-)
 
I reckon I'm still learning but would not be anywhere near where I am
right now without the help of this forum and the people who contribute.
It is appreciated.

Where else could you post a question and have an expert answer,
sometimes within minutes, and all given freely and in good faith!

WSF
 
You make an excellent point. I have never seen anything
even remotely similar to the world wide cooperation found in
newsgroups. The vision of the DARPA folks that dreamed up
newsgroups so long ago (eventually leading to the internet)
is truely amazing.
 
Back
Top