formatting a date column of transferred data in VBA access

[Me as Zoe]

Hi There,

I have a large data base, that has been transferred by the below code to the access as row data:

DoCmd.TransferText TransferType:=acLinkDelim, SpecificationName:="GainSpec", tableName:="RawData", _
     FileName:=strPathFile, HasFieldNames:=False

In the other side, my new table has been build up based the below code :

createSQL = "CREATE TABLE " + tableName + " (SECURITYID STRING, SECURITYNAME STRING, ISIN STRING, SEDOL STRING, BLOOMBERGCODE  STRING, INSTRUMENTTYPECODE STRING, PRICEDATE String, Bloomberg DOUBLE, ICEExshare DOUBLE, Reuters DOUBLE, MidSpread DOUBLE, ID AUTOINCREMENT PRIMARY KEY);"

Now, i need to append info from row data with other quries info that i have to be into new table but i realised "pricing date" column in "Row Data" is based on text( and month name ) however the pricing date in other info are based on date( month number) and as they get group by pricing date, i'm not able to append them and i get "mismatch error".
Any thought would be appreciated

Cheers,
Zoe

[NeoPa]

Hi Zoe.

Welcome to Bytes.com.

Two thoughts occur immediately :

1. Create the table to match the actual data you have to store in it.
2. Load the data originally into a staging table, then populate the proper table with a Query based on the staging table.

The latter option is a concept that is very important and often required when dealing with the transfer of data. Particularly where you are not in control of the format of the data to be transferred.

[NeoPa]

I would also draw you attention to Before Posting (VBA or SQL) Code.

You're a new member so there are tricks and tips that not only protect us, but also help you get the best of the help that's available. The fact that you are not new to such work is also clear, so I expect you'll understand easily when I point out that without knowing such details as what you're using for a tableName we can only guess what the actual SQL string looks like.

Also, it probably mentions this in the linked article but you'll do yourself a favour by formatting the SQL in such a way that it can be seen easily on the web page of the forum. The easier you make it for other members and experts to read and understand your problem, the more chance you have that they will be invested in spending their time trying to help you.