Date/Time Issue with Text

[jjc3397]

I have a Text file that is being imported to access from the mainframe. I do not know why the programmer who created this file made the Issue_date field seven characters with the date appearing on the imported text field being 0101896 for 101896. I am having to strip this leading zero off with a update query so I will have the six digits I need. The problem I'm having is that Access is keeping the field Text and I need for this field to be a Date/Time data type so that I can do a Parameter Query and query by dates say for example 04/01/2006 to 06/30/2006. I need the licenses issued for this period of time.

I know I can make the Issue_date field Date/Time manually, but I won't to be hands free with this datebase and have Access do all of the work for me with update query's, append query's and delete query's if needed.

The License file updates constantly and I need for the user to just push a button and bring the most current up to date database in. I want Access to bring the text in through a specification that is saved with Text data type. I need for the data to come in as text first because I will be placing slashes in the date with a update query too. I will then strip the leading zero off too with an update query.

Anyhow, I need the text field to become date/time again so I can query the dates and have access know that the data is now date/time type and not text.

Any suggestions?

 

[JerryKlmns]

Well, if you place an extra Date/Time field in your table and convert the 7char-non-date field maybe you 'll get what you need!

UPDATE YourTableName
SET NewDateField = DateSerial(Right(NonDateField, 2), Mid(NonDateField, 2, 2), Mid(NonDateField, 4, 2))

 

[jjc3397]

The Issue_date is set as Text, because if I change this data type to Date/Time, spaces will come in and no data will show. I have added the field New Date and changed this data type to Date/time and used your coding, but I still get an error message, data/type mismatch on Criteria experssion in my update queryhen it is run. Maybe I have someth8ing wrong. I'm new to VB and so I could have something all wroing.

The Update Query will be called UPDATE-DATE-QRY

I have added the field NewDate with Date/Time Data Type in the original table, called TABLE-ADDRESS.

On the Update QUERY--UPDATE-DATE-QRY

I have brought the NEWDate Field into the grid and have typed the following in the update to part:

SET NEWDATE=DateSerial(Right(Issue_date,2),Mid(Issue_date,2,2),Mid(issue_date,4,2))