Macro or CODE to change data format in a table for user

  • Thread starter Thread starter FergusonH
  • Start date Start date
F

FergusonH

Hi. I have a macro that imports a xls as a table and runs several queries,
etc. for users. I need to have a field in that xls. converted to a date.
When I manually change the field from numeric to date/time in table design,
it works fine. But I want this to happen via a "button" for my users. I
tried to run the below sql, but then I get a conversion error and the field
contents are deleted.
The table name is "BadDebt" the field i'm trying to format is "Process"

UPDATE BadDebt SET BadDebt.PROCESS = Format(([BadDebt]![Process]),"dd mmmm
yyyy");

Thanks in advance!
 
Ferguson,

What is the data type of the Process field in the imported data? Can
you please give an example or two of what it looks like and what you
want to change it to?
 
Hi Steve,
Thanks for the help. It's formated in excel as a date "05/01/2008" it gets
changed to numeric during the import so it looks like "38326" .

I tried changing the process in xls that loads the file to store it as a
long date instead of short date- the import seems to be working now but if
there's a way to code a button to change the data type to date/time that
would be great -I'm sure I'll run into this again.

thanks!

Steve Schapel said:
Ferguson,

What is the data type of the Process field in the imported data? Can
you please give an example or two of what it looks like and what you
want to change it to?

--
Steve Schapel, Microsoft Access MVP
Hi. I have a macro that imports a xls as a table and runs several queries,
etc. for users. I need to have a field in that xls. converted to a date.
When I manually change the field from numeric to date/time in table design,
it works fine. But I want this to happen via a "button" for my users. I
tried to run the below sql, but then I get a conversion error and the field
contents are deleted.
The table name is "BadDebt" the field i'm trying to format is "Process"

UPDATE BadDebt SET BadDebt.PROCESS = Format(([BadDebt]![Process]),"dd mmmm
yyyy");

Thanks in advance!
Click to expand...
Click to expand...
 
Fergusson,

If you import into an existing table in Access, in which the field in
question is a Date/Time data type, I would expect it to just slide in
without problem. Are you importing to an existing table, or is the
import creating a new table? What are the details of the macro you are
using? If you are making a new table with the import, and the data type
of the Process field is Number, then you can use an Append Query to
write that imported data into an existing table, with the Process field
set as Date?Time data type, and it should be good.
 
Back
Top