By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,275 Members | 1,899 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

formatting a date column of transferred data in VBA access

P: 1
Hi There,

I have a large data base, that has been transferred by the below code to the access as row data:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText TransferType:=acLinkDelim, SpecificationName:="GainSpec", tableName:="RawData", _
  2.      FileName:=strPathFile, HasFieldNames:=False
In the other side, my new table has been build up based the below code :
Expand|Select|Wrap|Line Numbers
  1. 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
4 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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.
4 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,660
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.
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.