Access Database Query Help

[Catalyst159]

I have a better idea of what is happening now. Thanks for the explanation and the help.

Catalyst

[NeoPa]

Smiley:
PS. Its just convention that says that 0 is 30/12/1899.

It's not really a convention. It's just a date that MS decided to use.

NB. Because dates are stored as Doubles, they can also handle negative values so much older historical dates can also be represented by simply using negative numbers

?CDate(-1),CDate(-657434)
29/12/1899    01/01/100

Your update query could be simply (fundamentally similar to other suggestions but slightly shorter) :

UPDATE [Marriages]
SET    [Date of Marriage] = Format(CDate([Date of Marriage]),'m/d/yyyy')
WHERE  IsDate([Date of Marriage])

Assuming your actual requirement is to update all date strings to the same format as long as they can be so updated, then ADezii's suggestion (from post #3) is a perfect solution for you.

[Catalyst159]

Thanks for the input Neo. So far the update query is working for my situation. It is unusual how access handles the dates. I am definitely getting a better understanding of it though. Thanks again.

[Rabbit]

It's how all computers handle dates. An arbitrary date is chosen as the 0 date and every other date is relative to that date.

[sumon14]

UPDATE marriages SET marriages.[Date of Marriage] = Format(CDate([Date of Marriage]),'m/d/yyyy')
 
WHERE marriages.[Date of Marriage] Is Not Null OR marriages.[Date of Marriage]Or marriages.[CDate] <> 'VOID';

[NeoPa]

@Sumon14
I'm not sure about that. It adds nothing to previous solutions and misses various points already made. Above all, it won't work.