Using Format in On Click Event fails

[Middlemarch]

I'm converting code in an Excel workbook to Access and have struck something I don't understand.



This works in Excel (and an Access module) but Type Mismatch errors in the On Click event of an Access Form. Is there any reason/explanation for that ?

Dim x As String
x = "12/3/1967"
Debug.Print Format(x, "dddd, dd mmm yyyy")

Thanks for any help.

[June7]

I just tested your code and it works perfect for me.

[Middlemarch]

Thanks for replying June7, that is a bit odd it works differently. I solved it here (after a fashion) by
sending x to a Module Function and returning it formatted. Maybe it's an Access2007 thing? I hope I have this
right - I spent about an hour testing it.

[June7]

AFAIK, should be no different for 2007.

Why not declare the variable as a date type instead of string? Or maybe don't even use variable. If variable is not used again, why have one? Or use CDate() function.

[Middlemarch]

I agree it should work, and I spent several hours trying all sorts, because it seemed so illogical.

But the error is constant.

Private Sub Date_Entered_Click()
Dim x As String
x = "12/3/1967"
Debug.Print Format(x, "dddd, dd mmm yyyy")
End Sub

I do want to use Date_Entered (which is a date Datatype), but when this errored x was tried as a test.

This also returned the same Type mismatch Error
Debug.Print Format(Date_Entered, "dddd, dd mmm yyyy")

In the end this worked:

Private Sub Date_Entered_Click()
 Debug.Print DateFormatThis (.Date_Entered)
End Sub

Function DateFormatThis(d) As String
    DateFormatThis = Format(d, "dddd, dd mmm yyyy")
End Function

With the function in a module.

[June7]

What type of control is the Click event associated with?

[Middlemarch]

It's a text box bound to Date/Time field, and in continuous forms view.

[June7]

Before I tested with a command button. Just tested with bound and unbound textboxes. Works.

Are you using date picker?

More testing with your final code. This does not work. No error but no formatted date. Until I gave the textbox a name different from the field. Now it calculates formatted date.

[Middlemarch]

Not using date picker. I tried changing the textbox name but still a Type Mismatch.
I give up !! But the function solves the problem so I guess I'll stick with that.

[June7]

Bizarre. If you want to provide db for analysis, follow instructions at bottom of my post.

[Middlemarch]

Bizarre I'll say. It's OK in a new database.

But in this one





The database shows no sign of any other problem. It's beyond me.
Thanks for responding and helping though