Picking up Date on Import

[DonL]

Hi all

I have a DB that automatically imports an excel file and appends the data to a current table I have created. All working fine. The import currently deletes the first six rows as these weren't required - it's header information such as the date of the report. I'm firstly taking the full excel file into a temp database table. In that table I delete the first few rows and then append to the main data table.

A new requirement is in to now pick up and store the date. This is causing me issues as the date is in the header of the report and not in the core part of the data. I've attached a sanitized screen of the excel for reference.

Is there anyway I can:

• Pick up on the date in the 5th row? - I'm thinking I could assign the 4th row to a variable and pick out the substring of it to locate only the date part (remove 'Pricing Date:') . Would this be correct, is it possible and how would I do it?
• Once the date is picked up, can I add the date to a new 'Date' field in access and populate each record with the date? So the core data will have an additional field called Date containing the date in the header of the report?

Hope this makes sense!

Thanks

Donal

[NTC]

it seems to me you almost answered your question in your post;

if you can find the date in the data - meaning that it is always in the same column/row - such that you can have query: FindDate - result being one row, one field.

next make a query that will find the records to be adding the date to 'FindRecordsForDate'

then you can have an UpdateQuery 'SetDate' that uses these 2 queries - and applies the date to them....

hope it helps.

[rpeare]

What is your code for importing the excel file?

Depending on how you're currently doing your import it should be possible to pull a portion of a field and add it to your field list.

[DonL]

What is your code for importing the excel file?

Depending on how you're currently doing your import it should be possible to pull a portion of a field and add it to your field list.

Hi rpeare. I've actually gone the query root and have successfully added the date as a new column to the data.

i have a new issue though. Now that I have the data in, I need functionality to compares it to another set of data. This comparison is based on ID and date. The results will be those ID's that match that also match in terms of their date. The issue is around the date format:

• The date field in the first set of data (which is also automatically imported), is coming in as the number of days after 01/01/1900.
• The second set of data (the set referenced above) is being stored as a string in the following format DDMMMYY e.g. 30JUN11.

Any ideas on how to get the 2 fields into the same format for the comparison to be successful?

Thanks

Donal

[rpeare]

are they both stored as dates or is one of them stored as text? If they're both stored as date you should be able to do any date function you want on them. If the 30Jun11 is a text field you can convert it with this type of function:

CDate(Left([fieldname],2) & "-" & Mid([fieldname],3,3) & "-" & Right([fieldname],2))

All I'm doing with this particular example is rearranging your string and adding some hyphens to put your date into "Medium Date" format then performing a cdate (change to date) function on it. You can do this in a query or in vb code to convert your string.