HELP! - Type mismatch error (1 Viewer)

[Cotty42]

Hi All - hope you can help.

I am trying to get a year value from the current date, the code is:-

[INDENT]Dim CurrentYear as Long
 
CurrentYear = Year(Date)
[/INDENT]

However every time I try to run the code I get a Type mismatch error(error code 13). The really frustating part is that I have two similar forms in the database and the code works fine in one form but not in the other.

Any ideas gratefully accepted.

Thanks

Dave

 

[pr2-eugin]

Hello Cotty42, Welcome to AWF

Your error does not lie on the above two lines, check again.

 

[Cotty42]

Thanks for the prompt response Paul

It looks like the Year() code is not being recognised, when I hover the cursor over date is shows 'Date = 03/03/2014' (as you would expect) but when I hover over the Year(Date) statement is shows 'Year(Date) = <Type mismatch>' - which doesn't make any sense!

Cheers

Dave

 

[pr2-eugin]

Try this,

Dim funnyYear As Long
MsgBox "Today's Date : " & Date()
MsgBox "Year is : " & Year(Date())
funnyYear = Year(Date())
MsgBox "Funny Year is : " & funnyYear

See if you get all the three message boxes.

 

[Cotty42]

No - I only get the first message box and then the type mismatch error.

I have even tried using CDate(Date) and Str$(Date) but still get the type mismatch error.

 

[pr2-eugin]

That is very strange. Do you happen to have a user defined function called Year in your VBA project? That accepts another data type? Which might be overriding the built in Year function?

As an alternative try using DatePart function instead?

Dim funnyYear As Long
MsgBox "Today's Date : " & Date()
MsgBox "Year is : " & DatePart("YYYY", Date())
funnyYear = DatePart("YYYY", Date())
MsgBox "Funny Year is : " & funnyYear

 

[Cotty42]

I have a field in the database called 'PubYear' and the control on the form has the same name, there is also a field called 'DateCreated' with a corresponding control on the form. I don't think these should conflicty.

However - on a bright note the DatePart function works fine and, whilst I hate being beaten by this, I think I have now spent enough time trying to resolve it.

Thanks very much for your help Paul.

Regards

Dave

 

[pr2-eugin]

No problem, glad you have a work around, but I would advice you to check the root cause of the problem, as it might come back and bite you in a later stage. If you'd like for us to examine the code, upload a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

Good Luck !

 

[gemma-the-husky]

cotty42

maybe this is a reserved word issue

maybe you have a control/field on your form called YEAR, or a function or variable called YEAR somewhere

this will override and replace the access function year()

Is this possible?

 

[pr2-eugin]

cotty42

maybe this is a reserved word issue

maybe you have a control/field on your form called YEAR, or a function or variable called YEAR somewhere

this will override and replace the access function year()

Is this possible?

I thought I already covered that?

.... Do you happen to have a user defined function called Year in your VBA project? That accepts another data type? Which might be overriding the built in Year function? ...

 

[gemma-the-husky]

yep - hadn't noticed that part of your reply, paul.

 

[Cotty42]

Thanks for all your help.

I'll re-visit this when I have a bit more time to see if I can figure out where the conflict is. But as for now I'll make do with the DatePart function.

Cheers
Dave