Date Conversion through Update Query

[Steven.Allman]

Long story as short as possible....
I have created a very robust database the tracks employees with 'issues'
Said database tracks hire date, contract termination date, pay grade, etc.. We also use it to do demographic data extrapolation and as I get request for this and that, it grows...
I recently got permissions to the larger corporate personnel database, and have created a ‘update query’ that will pull from theirs, import the info to mine, and auto fill most of my form fields. What i am missing however is the DATE...The original database holds dates in ‘number fields’ as YYYYMMDD
I utilize “date fields” in my database and wonder if there is something I can write in the SQL or design to have the update query convert the 20130129 to 29-JAN-13. The query is built and works, just need to figure out the conversion portion. I can post my code on here if needed, but due to information involved, I cannot post specifics about who, what I track...
sorry



Currently I use the LostFocus and some code to each field so when I tab through it does a proper case update, can I automatically set that to formclose and it update each field on the current record?

[pbaldy]

You should be able to use the DateSerial() function along with the Mid() function to pull out each appropriate portion.

[Bob Fitz]

The following expression will do the required conversion:
Format(Right([YourDateField],2) & "/" & Mid([YourDateField],5,2) & "/" & Left([YourDateField],4),"dd/mmm/yy")

[Steven.Allman]

The following expression will do the required conversion:
Format(Right([YourDateField],2) & "/" & Mid([YourDateField],5,2) & "/" & Left([YourDateField],4),"dd/mmm/yy")

When throwing this into my query, it literally disappears upon saving.
Format(Right([SIDPERS.PEBD],2) & "/" & Mid([SIDPERS.PEBD],5,2) & "/" & Left([SIDPERS.PEBD],4),"dd/mmm/yy")

Here is the SQL from my update query
Not sure where an expression would fit in here. This is my first forray into the expressions in a query that isnt simple "Age" or Date Range stuff..
UPDATE [Positive People] INNER JOIN SIDPERS ON [Positive People].SSN = SIDPERS.SSN SET [Positive People].[Last] = [sidpers.last], [Positive People].[First] = [sidpers.first], [Positive People].Middle = [sidpers.Mi], [Positive People].UIC = [sidpers.uic], [Positive People].Grade = [sidpers.rank], [Positive People].Gender = [sidpers.sex], [Positive People].PEBD = [sidpers.pebd], [Positive People].DOB = [sidpers.dob], [Positive People].HOR_street = [sidpers.street], [Positive People].HOR_City = [sidpers.city], [Positive People].HOR_State = [sidpers.state], [Positive People].HOR_Zip = [sidpers.zip]
WHERE ((([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn] And ([forms]![Positive people]![SSN])=[sidpers].[ssn]));

[Steven.Allman]

And I see how that works if I put that into an expression in my form, but the 1200 records i currently have I are already uzing the other format, so my issue is creating the Update Query that will take from
SIDPERS:dob in format of ######## and put that in POSTIVE_PEOPLE:dob in DD-MMM-YY format
is it possible to do this in a single query? Once I get this I can probably figure out the 30 other converstions I need, like grade, other dates, etc...

[pbaldy]

Either should work fine in a query. What was the SQL of the query that failed? Or can you post the db here?

[Steven.Allman]

Actually i got this to work like a charm!!! I was adding too much to the SQL, trying to make it do the import, THEN the conversion, instead of converting then importing.. This is sweet! Got the capitalization set up correctly and everything..
now, LAST drama with this import...
gender...
BIG DADDY DATABASE says M and F
Mine says Male and Female.
Granted I could just alter mine, run an update and start using M and F, but can i set some string that converts like a ifwhen statement?

[Steven.Allman]

EVERYTHING WORKS!!!
Thanks GUYS!!!

[pbaldy]

Glad you got it sorted out!