date 40-April-2006, must be wrong

BartekSollie · Nov 5, 2006

Hi,

Could someone give me any idea to solve this problem:
When you enter, for example, this date 40-Apr-06, the has to come a msgbox with a message like this: incorret date or daynumber is'n possible.

Can i do this with vba idate() or datepart()

Any idea is welcome, to solve this.

RuralGuy · Nov 5, 2006

Many developers avoid this type of parsing by using a pop up calendar. Calendar Links

raskew · Nov 5, 2006

40 April. Amazing ---Please tell us about it.

Thanks, Bob.

ChrisO · Nov 5, 2006

According to Access that is a valid date.

IsDate("40-Apr-06") returns True
CDate("40-Apr-06") returns 4/6/1940 (US) 6/04/1940 (AUS)

Makes a pop up calendar sound like a good idea.

gemma-the-husky · Nov 5, 2006

nice one chris - even my trusty format(mydate,."long date") won't fix that one!

raskew · Nov 6, 2006

Hi Boys and Girls -

How do we do dates in Access? Do we use Quotes "" or hashmarks ##? ChrisO -- "40 April". Please tell us about it.

Best Wishes, Bob

ChrisO · Nov 6, 2006

Bob.

I read the original question to be about entering a date which could be via a text box or InputBox.
Seldom would our users surround the date with octothorpes so we would initially start with a string. If they did enter the octothorpes, with the date, it would probably raise an error.

The code below is something that people can play with to see just how much trouble dates can be in Access and that is not really considering regional settings.

Behind a Form: -

Code:

Private Sub txtDateEntry_AfterUpdate()
    
    MsgBox CDate(Me.txtDateEntry) [color=green]'  40-Apr-06[/color]
    
    If IsDate(Me.txtDateEntry) Then
        MsgBox "True"
    Else
        MsgBox "False"
    End If

End Sub

Or in a standard module: -

Code:

Sub Test()
    Dim strX As String
    
    strX = InputBox("Please enter a date")  [color=green]'  40-Apr-06[/color]
    
    MsgBox CDate(strX)
    
    If IsDate(strX) Then
        MsgBox "True"
    Else
        MsgBox "False"
    End If
    
    MsgBox SQLDate(strX)
    
End Sub


Function SQLDate(varDate As Variant) As String
    [color=green]'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.[/color]
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

The problem is that Access will attempt to convert the string to a valid date but it doesn’t always come up with the correct result. When we add the time factor and take into consideration regional settings it can be so bad that simply using doubles becomes viable.

I had to re-write this demo in Sample Databases in order for it to comply…
http://www.access-programmers.co.uk/forums/showthread.php?t=107870

The only thing I can suggest is that people be very careful when entering dates and a good date picker will remove some of the problems.

Regards,
Chris.

date 40-April-2006, must be wrong

BartekSollie

New member

RuralGuy

AWF VIP

raskew

AWF VIP

ChrisO

Registered User.

gemma-the-husky

Super Moderator

raskew

AWF VIP

ChrisO

Registered User.

Similar threads

Users who are viewing this thread