import date

[slimjen]

All, using access 2003; I am trying to import a xls spreadsheet into a temp table with a date format in the spreadsheet as text 20111212 to a date field in access table 12-12-2011. I get type mismatch. What can I do please.
Thanks

[Robeen]

Can you do a Format Cells in Excel and make that 20111212 column a Date column?

I ran into something like this a few years ago.
What I decided to do [since my project required numerous automated imports every morning from Excel to Access] was just do an initial import from Excel into a new Table - and let Access decide what kind of field to create for each Excel column.
After that - I created Macros that use delete queries to empty the tables each morning and import fresh data from Excel.

Then - in Access - I do my type-conversions [or just use the Format() function] in queries or VBA to take what came in to the table and make it what the end-user needs.

Hope this helps.

[slimjen]

UM. The auto import is done by the user. Maybe I can try the to setup a specification and do the formating there. What do you think? I have to find how to do it. It's been a while since I have done the specification import. Thanks

[slimjen]

Having a problem with the import specification. Does not allow me to change data type. What I'm I doing wrong please. Seems like I did this import specification from excel a while back. Is this possible.

[Robeen]

I have had mixed fortunes with using the Import Specifications.
They 'Work' . . . but are 'temperamental' and 'picky'.

That's why I try not to depend on them.

And that's why I was leaning away from messing with the Import process.

It's a LOT easier to work with the data once it is in an Access table - regardless of what format it is in.

Would working with the data in Access - not be a viable option for you?

[Robeen]

I'll try and duplicate your scenario if I get a chance today - but I'm working with Access 2010 - so there might be slight differences there too.

[slimjen]

The user would be doing the import. Heres a scenrio: The user clicks a import button on a form: I have a spreadsheet going into a temp table before appending to perm table. I just need to clean up the data in the temp table before appending it to the perm table such as converting date from 20111212 to 12-12-2011 and zip from 237071495 to 23707 or 7398 to 07398 also to make caps where not like small to SMALL. How can I do this? Thanks

[Robeen]

Do you already have your append query?

[slimjen]

Yes; I do.

INSERT INTO tblSP_SHIPMENT_temp_TEST ( INVOICENUM, TRACKINGNUM, SHIPDATE, TRANSCHG, SALESORDNUM, SENDERNAME, SENDERCITY, SENDERSTATE, SENDERZIP, RECIPIENTNAME, RECIPIENTCITY, RECIPIENTSTATE, RECIPIENTZIP, WEIGHT, SERVICETYPE )
SELECT SMALLPKG.[Invoice Number], SMALLPKG.[Express or Ground Tracking ID], SMALLPKG.[Shipment Date], SMALLPKG.[Net Charge Amount], SMALLPKG.[Original Customer Reference], SMALLPKG.[Shipper Company], SMALLPKG.[Shipper City], SMALLPKG.[Shipper State], SMALLPKG.[Shipper Zip Code], SMALLPKG.[Recipient Company], SMALLPKG.[Recipient City], SMALLPKG.[Recipient State], SMALLPKG.[Recipient Zip Code], SMALLPKG.[Actual Weight Amount], SMALLPKG.[Service Type]
FROM SMALLPKG;

The field in SMALLPKG.[Shipment Date] is formatted as text 20111209. The field in INSERT INTO tblSP_SHIPMENT_temp_TEST SHIPDATE is short date format.
Thanks

[pbaldy]

Does this work?

INSERT...
SELECT... DateSerial(Left([Shipment Date], 4), Mid([Shipment Date], 5, 2), Mid([Shipment Date], 7, 2))...

Which assumes the text field is yyyymmdd.

[slimjen]

where do I put this? When I put it in the format field of the shipdate properties; it gets all weird and i can't change it

d"ate"s"erial(Left(["sh"ip"n\en"t "d"ate], 4), "m\id"(["sh"ip"n\en"t "d"ate], 5, 2), "m\id"(["sh"ip"n\en"t "d"ate], 7, 2))..."

[pbaldy]

In the SELECT statement, like I showed.

INSERT INTO tblSP_SHIPMENT_temp_TEST ( INVOICENUM, TRACKINGNUM, SHIPDATE, TRANSCHG, SALESORDNUM, SENDERNAME, SENDERCITY, SENDERSTATE, SENDERZIP, RECIPIENTNAME, RECIPIENTCITY, RECIPIENTSTATE, RECIPIENTZIP, WEIGHT, SERVICETYPE )
SELECT SMALLPKG.[Invoice Number], SMALLPKG.[Express or Ground Tracking ID], DateSerial(Left([Shipment Date], 4), Mid([Shipment Date], 5, 2), Mid([Shipment Date], 7, 2)), SMALLPKG.[Net Charge Amount], SMALLPKG.[Original Customer Reference], SMALLPKG.[Shipper Company], SMALLPKG.[Shipper City], SMALLPKG.[Shipper State], SMALLPKG.[Shipper Zip Code], SMALLPKG.[Recipient Company], SMALLPKG.[Recipient City], SMALLPKG.[Recipient State], SMALLPKG.[Recipient Zip Code], SMALLPKG.[Actual Weight Amount], SMALLPKG.[Service Type]
FROM SMALLPKG;

[slimjen]

I tried this. I get an error: Number of query values and destination fields are not the same.
Thanks

[slimjen]

I had a field in the query twice. It worked! Thanks so much

[pbaldy]

No problem!