How do you convert number field to date field? 1

[djgolds]

I have a table where the birthdate was entered as a number field rather than a date field when designing the table. An example is a birthdate of July 7, 1960 is entered as 07071960. How can I convert this to a date field and maintain the correct numbers? I've tried just changing the field, but then it puts in crazy numbers. Thanks in advance for any help you can give on this!

 

[raskew]

Assuming that your numbers are provided in a consistent manner, try this in the debug window:

widget = 07071960 <enter>
widget = str(widget) <enter>
widget = left(widget, 2) &amp; &quot;/&quot; &amp; mid(widget, 3,2) &amp; &quot;/&quot; &amp; right(widget, 4) <enter>
? datevalue(widget) <enter>

should print:
7/7/1960

 

[djgolds]

Thanks for the fast response, but I'm dense. Could you please provide step-by-step instructions as I don't typically work with codes or debugging. Thank you - - and sorry!

 

[skfoster]

If you do not know code, try use table copy and append. Every time change destination table data type from number to text, from text to date. After data type changed to text, need update query as raskew show above to add &quot;/&quot;, mm/dd/yyyy.

 

[raskew]

Sherman

I was out of the net. Thanks for jumping in.

Bob

 

[djgolds]

I'm almost there! I'm just having problems with the last line raskew mentioned - ? datevalue(widget)

I get expression contains invalid syntax. Hope you don't mind helping me out 1 more time!!! Thanks!

 

[raskew]

We need to know what Widget looks like when you attempt datevalue(widget). It should have been transformed into a string, i.e. &quot;07/07/1960&quot;, if not you need to review the process. It would probably be clearer if you were to first follow the debug window example. To open the debug window, press control-G, when it opens, just type the statements exactly as shown, pressing enter at the end of each line.

Post back with the results.

 

[djgolds]

I typed the statement in the debug window and it worked great - it returned a date of 7/7/60 but what's the next step to get it to apply to the table - do I need to create a module or what? I'm so sorry I'm being so slow about this and can't begin to say how much I appreciate your help. I tried experimenting with copying it from the window and posting it to the &quot;update to field&quot; in the update query, but then I get the same message I got previously - that it was a invalid syntax.

 

[raskew]

It just dawned on me that what you identified as a number field is, in all probability, a text field. Reason--unless you've got formatting which provides for leading zeros, 07071960 entered into a number field will appear as 7071960. That is probably what was causing errors.

To actually update your table:

(1) Add a new date field to your table. Name it newDOB and set the format to shortDate.

(2) Create a new query that includes both DOB and newDOB fields. When you initially run the query, field newDOB will be blank. Now, in query design view, change the query type from Select to Update.

(3) In the Update cell under the newDOB field, copy/paste this:

datevalue(Left([dob],2) &amp; &quot;/&quot; &amp; Mid([dob],3,2) &amp; &quot;/&quot; &amp; Right([dob],4))

(4) Run the query. If all goes well, field newDOB will be populated with the properly formatted date.

(5) View the table. Once you're satisfied with the results, delete field DOB from the table, and rename field newDOB to DOB.

 

[djgolds]

You're right - it was eliminating the first 0. I tried 3 times exactly as you had it and I get dialog box that says can't update due to violations. If I go ahead and click yes, it deletes the BirthDate column and my newDOB is blank. Sorry, you must be wanting to beat your head against the wall by now. If you have any other suggestions, I'd appreciate it. You've been very helpful and generous in sticking with me through this trying to find an answer!!

 

[raskew]

The part about yourBirthdate Column not being visible is correct--that's supposed to happen. Your query SQL should look like this, except for the table/field names:
UPDATE Query2 SET Query2.newdob = Left([txtdob],2) &amp; &quot;/&quot; &amp; Mid([txtdob],3,2) &amp; &quot;/&quot; &amp; Right([txtdob],4);

If it doesn't, try changing yours. If you still get the key violation's message, post the contents of the message.

 

[djgolds]

This is the last time - I promise! If I don't get it on this one, we'll just forget it. I'm a bit confused by the SQL as in one message it included datevalue, and the new message didn't. I should have done this from the start, but my table name is Test2. This table includes a BirthDate field which is text (used to be number), and another field called newDOB which is a date/time field and formatted to a short date. I then create a select query including columns for BirthDate and newDOB and run it - all looks good - newDOB is blank. I then run an Update query and under the column for newDOB in the Update To field I've typed in
datevalue(Left([newDOB],2) &amp; &quot;/&quot; &amp; Mid([newDOB],3,2) &amp; &quot;/&quot; &amp; Right([newDOB],4)). I run it and get a dialog box stating records lost due to a type conversion failure. I proceed to run it and that's when I get the blank newDOB. For this last time and my simple mind, please let me know using my table and field names how it should look. Again, thank you sooooo much!

 

[raskew]

You want to fill the NewDOB with info you obtain from the BirthDate field. Your update statement should read:

datevalue(Left([BirthDate],2) &amp; &quot;/&quot; &amp; Mid([BirthDate],3,2) &amp; &quot;/&quot; &amp; Right([BirthDate],4))

 

[djgolds]

Don't faint - but it worked . . . for at least the first line. In my test2 table I'm only using 6 entries and it converted the first entry, but the rest were blank. If you want to give up on me at this point - I understand!!! In case you don't, I copied and pasted the SQL in the &quot;update to&quot; field under my newDOB column. When I ran it, I got the message &quot;didn’t update 5 fields due to a type conversion failure&quot; - but again, the first field turned out great! It's the most progress I've made so far! Thanks!