Forms date format error

[peachmelba]

Can anyone help with this.

I have a field on a form which is a short date (dd/mm/yy) format. When I input 31/06/04 there is a check on the value to make sure it is a date using Isdate funstion. This returns true as it thinks that the date is actually 04/06/31.

I can't think of a way to sort this and say that this is not a date!

Peachmelba

 

[LNBruno]

You could split it out into MM DD YYYY checks:

iMM = cint(left([datefield]),2)
select case iMM
    case 1,2,3,4,5,6,7,8,9,10,11,12
        '--is ok
    else
        '--not okay; take some action
        .............................
end select

although there's probably an easier, softer way ;-)

< M!ke >

 

[peachmelba]

Yeah tried cutting up the value into left(mydate,2) & "/" & mid(mydate,4,2) & "/" & right(mydate,2) which should give me 31/06/04.

But gives me 04/06/31.

So it obviously is changed as soon as it is entered!

Any other ideas?

Peachmelba

 

[TonyJollans]

Hi peachmelba,

When you say it's a date, Access tries its best to interpret the input as a Date and format it as per your request, but you should be able to trap it. What event is your code in?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[peachmelba]

Tony

I have put it in the BeforeUpdate event.

Peachmelba

 

[TonyJollans]

Hi peachmelba,

In the BeforeUpdate Event ..

[blue]Me.DateField.Value[/blue] should contain 04/06/31 and ...

[blue]Me.DateField.[red]Text[/red][/blue] should contain 31/06/04

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

Access uses "mm/dd/yy" (i.e. US date format) internally regardless of the locale setting. You may want to explicitly force your interpretation with
[tt]
On Error Resume Next
theDate = DateSerial ( right(mydate,2), _
mid(mydate,4,2), _
left(myDate,2) )
If Err.Number = 0 then
' Date is Valid
Else
' Invalid Date
End If
[/tt]

 

[LNBruno]

Dates! Gotta love 'em.

Could you change the field to a string and then do the "left(mydate,2) & "/" & mid(mydate,4,2) & "/" & right(mydate,2)" you already tried?

< M!ke >

 

[peachmelba]

Cheers guys

Used both of the info you gave me and it worked!!!!

Yeah I hate dates as well.

Thanks

Peachmelba