Date Format

[Ken Hudson]

I have the following code as part of a macro:

Dim strTest As String
strTest = Date
strTest = Format(strTest, "mm-dd-yy")

When I compile the code I get the following error:

Compile error:

Wrong number of arguments or invalid property assignment.

I am getting a headache staring at this and trying to figure out the obvious
error, but can't.

 

[Mike H]

Ken,

The error is somewhere else, that works fine but unless you have a reason I
prefer to dim as date

Dim strTest As Date
strTest = Date
strTest = Format(strTest, "mm-dd-yy")


Mike

Mike

 

[Rick Rothstein]

The "m", "d", and "y" meta-characters only work if the value (first
argument) is a *real* date value... your strTest is a String value. You
could do this...

strTest = Format(CDate(strTest), "mm-dd-yy")

but I am wondering why you went through the intermediate step of assigning
Date to a variable in the first place, just use it directly...

strTest = Format(Date, "mm-dd-yy")

 

[Rick Rothstein]

The "m", "d", and "y" meta-characters only work if the value (first

argument) is a *real* date value

I take this statement back... Format happily converts the String date back
to a *real* date value automatically. The code you posted works fine, so
that is not the source of your error.

but I am wondering why you went through the intermediate step of assigning
Date to a variable in the first place, just use it directly...

strTest = Format(Date, "mm-dd-yy")

The above comment still holds though.

 

[Ken Hudson]

Hi Mike,
Thanks for the reply.
When I dim the variable as a date, the format doesn't change it to
"mm-dd-yy". It keeps it as mm/dd/yy.
????

 

[Ken Hudson]

Mike and Rick,
Thanks for the replies. I commented out all the other code in the macro and
I can get it to work. (I do have to use a string variable for the date.)

I guess I'll go back through the code and un-comment sections until I find
the culprit.

 

[Rick Rothstein]

Why not post your code and get many eyes looking for your problem?

 

[Ken Hudson]

The code was so long, I was reluctant to foist on others to pore over.

I just found the problem. It's embarassing, but I need to fess up anyway.
Way down in the code I had set up a subroutine and named it "Format."
Another lesson learned.