default date field to 01/07/yyyy

[Dave_epic]

Hi!
Help needed please. I have a date field which look like this:

test1​

Field3​

00/00/1939
00/00/1993
00/00/1966
00/09/1997
00/00/1976
00/03/1998
00/00/1991


What I am attempting to do is to update all the days and months to 01/07 but leaving the year intact.
I have thought of the following query using date value:

IIf(Left([Field3],6)="00/00/",DateValue("01/07/" & Right([Field3],4)))

This works obviously only for the dates where the day and month are "00/00/" but where the day and month are not "00/00/" the record is simply deleted as below which I don't want. As i said I want to change everything to "01/07/" + year. Can you help. Thanks

test​

Field3​

01/07/1939
01/07/1993
01/07/1966

01/07/1976

01/07/1991

 

[SimonB1978]

Hi,

You are not specifying any values if the condition is not met. I suppose it will use "null" in that case. Change your IIf to this:

IIf(Left([Field3],6)="00/00/",DateValue("01/07/" & Right([Field3],4)), [Field3])

Hope that helps,

Simon B.

 

[Dave_epic]

Yes, thanks this helps. I also found out that date serial works.

DateSerial(Right([Field3],4),7,1)

Cheers