asked on

Checking Date format and changing format for database insertion

Hi Experts,

I have a text box where a user can insert a date.

I have validation using the isDate() function to check it's a date.

However, if I insert a date like '02/15' (without the year) I would like it to automatically convert it to the correct format (MM/DD/YYYY) so that it will actually be inserted in to the database.

It is an Access Database back end, which I thought would automatically detect the date format and insert it.... but maybe it doesnt?

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

c0ldfyr3

The problem with if it's a two part date is there is no year specified so you need to specify one.

Alternatively, why not use the DatePicket control?

Dim sDate As String
 
sDate = txtDate.Text
 
If Len(sDate) < 10 Then
    sDate = sDate & "/2008"
    'Optional'
    'sDate = Format( sDate & "/2008", "mm/dd/yyyy" )'
End If
 
 
If IsDate(sDate) Then
    'Do your insert here'
End If

Open in new window

stellaartois

ASKER

cDate() doesnt appear to have any affect. I decided to put in in a MsgBox just to check... is this the right or wrong way to check it

c0ldfyr3

CDate will just make a date from a string, and as IsDate returns true already all it will do is display what you enter.
CDate simply creates a date from a string so if you input something that's not a date you will get a run time error.

GrahamSkan

On my system, cdate("02/15") returns a date - 15/02/2008, the fifteenth of February this year.

GrahamSkan

Yes you can do that in a message box.

MsgBox CDate("02/15")

c0ldfyr3

He's correct actually it does append the year.

GrahamSkan

This incorporates the IsDate check

    Dim strText As String
 
    strText = "02/15"
 
    If IsDate(strText) Then
        MsgBox CDate("02/15")
    Else
        MsgBox strText & " is not a date"
    End If

Open in new window

GrahamSkan

Mind you, I think it would be safer to reject the incomplete date with a further check such as the one on the string length as c0ldfyr3 suggests.

c0ldfyr3

Or use DatePicker control ;)