Date Function

[majhl]

Hello all,

A requirement for a database I'm working on is that the date fields consist of the three constituent date parts - the day (dd), the month (mm) and the year (yyyy). I've written the following code to check the values entered in the day and month fields (below).

My problem is that there are numerous of these dates. I don't want to have to repeat the code every time I have a date field. I'd rewrite this code as a function that I can call when I need to but need some help with it. Any ideas if this can be done?

TIA,



----------------------------CODE------------------------------------

If (Me.txtmm = 1) Or (Me.txtmm = 3) Or (Me.txtmm = 5) Or (Me.txtmm = 7) _
Or (Me.txtmm = 8) Or (Me.txtmm = 10) Or (Me.txtmm = 12) Then

If (txtdd > 31) Or (txtdd < 1) Then
MsgBox ("Please check day."), vbOKOnly, "Error"
txtdd.SetFocus
End If

ElseIf (Me.txtmm = 4) Or (Me.txtmm = 6) Or (Me.txtmm = 9) Or (Me.txtmm = 11) Then

If (txtdd > 30) Or (txtdd < 1) Then
MsgBox ("Please check day."), vbOKOnly, "Error"
txtdd.SetFocus
End If

ElseIf (Me.txtmm = 2) Then

If (txtdd > 29) Or (txtdd < 1) Then
MsgBox ("Please check day."), vbOKOnly, "Error"
txtdd.SetFocus
End If

End If

 

[namliam]

Just dump the code into a function taking the 3 fields as parameters...

I think this can be done easier too...

As simple function like this:

Function fnCheckDate(txtYear as integer, txtMM as integer, txtDD as integer) as integer
    if Format(Dateserial(txtYear, txtMM, txtDD),"YYYYMMDD") <> txtYear & txtMM & txtDD then
        msgbox "Invalid date" 
    endif
end function

Try and use code tages (like I did) when you post code. Just quote my post to see how it works.

 

[majhl]

Thanks very for the fast reply namliam.

When I call the function from the exit event my year field I get an 'invalid date' error whether the date is valid or not.

I changed the function so that it now reads:

Function fnCkDate(txtyyyy As Integer, txtmm As Integer, txtdd As Integer) As Integer

    If Not IsDate(txtdd & "/" & txtmm & "/" & txtyyyy) Then
    
        MsgBox "Invalid date"
        
    End If

End Function

It appears to work.

Thanks for your help.

 

[namliam]

That should offcourse be:

Function fnCheckDate(txtYear as integer, txtMM as integer, txtDD as integer) as integer
    if Format(Dateserial(txtYear, txtMM, txtDD),"YYYYMMDD") <> txtYear & format(txtMM,"00") & format(txtDD,"00") then
        msgbox "Invalid date" 
    endif
end function

Isdate should work to, but I am somewhat doubtfull about the DD/MM/YYYY notation, usually dates in Access require US formating... but yes it seems to work for all dates...
Above should work also...

Edit:
If you just want the msgbox there, then you dont have to have a function. You can just make it a sub.
You will want a function if you want to return something to the original form, that way you can make special msgbox-es for different dates and say "Invalid contract start date" for example...

 

[majhl]

Thanks for your help again.