Type Mismatch Error for date

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Type Mismatch Error for date

Post by adam »

Hi anyone,

I have a column in my access database with the name ABCD. The format of the column is set to short date format. I have a textbox in my excel userform where I would sometimes write the date in the textbox and other times leave it empty.
The code works fine if a date is written in the textbox. but if the textbox is empty i get the error "Type Mismatch" highlighting the line

Code: Select all

.Fields("ABCD") = Me.txtMyDate.Value
How could I avoid this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type Mismatch Error for date

Post by HansV »

Code: Select all

    If IsDate(Me.txtMyDate.Value) Then
        .Fields("ABCD") = Me.txtMyDate.Value
    End If
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Type Mismatch Error for date

Post by adam »

Thankyou very much for the help Hans.

However, something weird is happening.

When the user adds a date in the userform text box txt.MyDate where the date format is the system's date format(dd/mm/yy); it gets saved to the table as mm/dd/yy

So, for example, if the date entered is 10/05/2021 (10th May 2021) it goes in as 05/10/21 (5th October), and comes out as that whenever it's retrieved thereafter.

Format for access table column is set to Short Date.

How can I solve this?

Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type Mismatch Error for date

Post by HansV »

Does this work?

Code: Select all

    If IsDate(Me.txtMyDate.Value) Then
        .Fields("ABCD") = CDate(Me.txtMyDate.Value)
    End If
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Type Mismatch Error for date

Post by adam »

Thanks for the help Hans. But I'm sorry, it's not making a diffrence.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type Mismatch Error for date

Post by HansV »

Then you'll have to parse the year, month and day:

Code: Select all

    Dim a() As String
    If IsDate(Me.txtMyDate.Value) Then
        a = Split(Me.txtMyDate, "/")
        .Fields("ABCD") = DateSerial(a(2), a(1), a(0))
    End If
Best wishes,
Hans