Date Format

[paulS30berks]

The date format from one of my access tables is as follows:

11051977
9101978

and I need to amend to following formats within my select query:

11/05/1977
09/10/1978

I have tried using formula:

Date of Birth: DateValue(Mid([DOB],7,2) & "/" & Mid([DOB],5,2) & "/" & Mid([DOB],1,4))

that results in an #Error messages

Can anyone help?

Thanks

 

[Smart]

format(datefield,"dd/mm/yyyy")

 

[paulS30berks]

Still receive #Error message.

Thanks

 

[raskew]

Hi -

Give this a try from the debug (immediate) window:

x = "9101978"
y = dateserial(right(x,4),left(x,1),mid(x,2,2))
? y
9/10/78

HTH - Bob

 

[Brianwarnock]

Is one problem that you have here is that some of your days are represented by 1 character and some by 2, you are going to need two formulae based on the string length, to go back to your original method as well as editing the strings to insert / you need to convert the edited string to a date data type with CDate.

I think Bob's method also works, again 2 formulae are needed to allow for the different positions of the data in the different length strings.

Brian

 

[raskew]

Since it appears that the string would be either 7 or 8 characters, think this will pick-up either possibility:

x = "11051977" 
y = dateserial(right(x,4),left(x,iif(len(x)=8,2,1)),mid(x,2,2))
? y

Bob

 

[Brianwarnock]

Since it appears that the string would be either 7 or 8 characters, think this will pick-up either possibility:

x = "11051977" 
y = dateserial(right(x,4),left(x,iif(len(x)=8,2,1)),mid(x,2,2))
? y

Bob

Ah! yes of course, retirement is slowing the old grey matter.

Brian

 

[Brianwarnock]

Actually I think it should be


y=DateSerial(Right([tdate],4),Mid([tdate],IIf(Len([tdate])=8,3,2),2),Left([tdate],IIf(Len([tdate])=8,2,1)))

Brian

 

[raskew]

Brian -

Your right of course. Spotted that as I was heading out the door and didn't have time to post a correction.

Thanks for that.

Bob