Year(Now) Type Mismatch error 2

[Secretgeek]

Before I start to beat my face against the desk I sincerely hope someone can help me out.

It would seem like the simplest thing in the world, I'm trying to fill a variable with the current year to use elsewhere in my code however

Year(Now)

is producing a type mismatch. 'Month(Now)' and 'Day(Now)' are returning values fine.

I can see absolutley no reason for this error and it's driving me nuts. Any suggestions?

 

[jrbarnett]

Try

Year(Date)

instead.

Using Date rather than Now is slightly faster if you don't need time as well as date.

John

 

[Remou]

That looks ok. Are you writing to a variable? How is the variable defined? Perhaps you could post a little code?

 

[Secretgeek]

Thanks. Tried Year(Date) and got the same mismatch error.

The subroutine is:

Private Sub PeriodReport_Click()
On Error GoTo Err_PeriodReport_Click

Dim stDocName As String
Dim MyDay As Integer
Dim MyMonth As Integer
Dim MyYear As Integer


MyDay = Day(Now)
MyMonth = Month(Now)
MyYear = Year(Now) 'Or Year(Date)

stDocName = "QuarterReport"

DoCmd.TransferSpreadsheet acExport, , stDocName, "T:\IGdatabases\Andrew Database working folder\" & MyDay & MyMonth & MyYear & "exporttest.xls", -1

Exit_PeriodReport_Click:
Exit Sub

Err_PeriodReport_Click:
MsgBox Err.Description
Resume Exit_PeriodReport_Click

End Sub

The error crops up right at the point where it's supposed to pick the year from the date and is independent of the MyYear variable. Using a breakpoint to look at the various variable states shows that it is the actual Year(Now) part that is producing the error. Unless I'm being particularly dull about use of the Year() function I really can't see what's wrong with it.

 

[jrbarnett]

Try instead

DoCmd.TransferSpreadsheet acExport, , stDocName, "T:\IGdatabases\Andrew Database working folder\" & Format (Date, "ddmmyyyy") & "exporttest.xls", -1

You should be able to comment out the defining of individual variables.

John

 

[Remou]

Neither can I. It seems that the problem must be with Year. Is it possible that you have a Year sub or function elsewhere? Do you have the same problem with year in a blsnk database?

 

[Secretgeek]

Outstanding! Works a charm thanks. I did think of using DatePart("y",Date) to extract the year but strangely it returned 9 instead of 8. Anyway, not that I care now that the code works (and is more elegant). Thanks again jrbarnett.

No doubt I'll be back when I do need to use the Year() function!

 

[Secretgeek]

Hmmm...will try the blank database option as I'm working qwith something that was here 18 months before I was.

 

[Secretgeek]

Just as a final note. Tried the code in a blank Database and the Year() function works absolutely fine.

Seems like some naughty individual has used Year elsewhere and I've got some hunting to do.

Thank you everyone for your help.

 

[jrbarnett]

Open the VBA editor, Ctrl F, choose Search All modules then type Year and keep going through until you find it.

Alternative: get V Tools for your access version from

http://www.skrol29.com

then use the Deep Search functionality.

John

 

[Secretgeek]

Thanks for the tip John. Already had a quick look and found that some clever soul had used Year as a field name.

Have changed that but a bit of test code that I'm running to see if the issue is still around is coming back with the error that it can't find the field 'Year'!

My head is starting to hurt. :-(

 

[Remou]

You may need to decompile and to check references.

http://www.granite.ab.ca/access/decompile.htm

 

[PHV]

To avoid error when a field/control is named Year:
MyYear = VBA.Year(Now)

To get the year instead of the julian date:
DatePart("yyyy",Date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrbarnett]

Still use V tools to search for references to Year.

John

 

[Secretgeek]

Have been through the decompile - compact process. Error still there.

PHV's tip works fine though.

As for V-Tools - I'll have to fight through my IT dept to get this installed. Was a nice idea though.

Still, with everyone's assistance I've managed to find other ways around this problem. Thanks again.

Just wish I knew what the hell was going on with that function.

 

[boblarson]

Have been through the decompile - compact process. Error still there"

What I've found is that you need to first make sure that you have gotten all references to Year (the field that was badly named) fixed. Then you must import everything into a new, blank database file. It really doesn't seem to work to just fix the bad names. You need to give it a clean start. But it won't work if you have any of those rogue names hanging around anywhere.




Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[Secretgeek]

And the crowd go wild!

Thanks very much Bob, that tip worked a treat.