Year Function - Type Mismatch error

[scroce]

Why doesn't this work?

If (Year([DepositDue])) = 2002 Then
  Do stuff
End if

I keep getting a "Type Mismatch" error during run time. It compiles OK however.

Also, I noticed that if I replace Year with the Month or Day function, the code works just fine -

What am I doing wrong?

Thanks Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[cmmrfrds]

Did you check that DepositDue is a valid date?

Debug.Print DepositDue

If (Year([DepositDue])) = 2002 Then
Do stuff
End if

Also could try.
If (Year([DepositDue])) = 02 Then
Do stuff
End if

 

[scroce]

i did try debug.print and set up a watch for it. It still threw back that error to me. Here's how I think I'm going to get around it -

Basically use a function called DatePart:

Dim datDepositDue As Date
Dim strDepositDue As String

datDepositDue = [DepositDue]
strDepositDue = DatePart("yyyy", datDepositDue)

If strDepositDue >= 2002 Then
  Do Stuff
End If

Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[cmmrfrds]

You would need quotes around a string field "2002"

This should do the same thing.
If DatePart("yyyy", DepositDue) >= 2002 Then
Do Stuff
End If

 

[JeremyNYC]

There's nothing wrong with the code the way you had it before. Something must be odd. Is that field a date field? Have you checked your references?

Try it with a fully qualified reference to the field, like
forms!frmYadda!NameOfControlHoldingTheDepositDueValue

and make sure your control is not named the same as the field.

Then what happens?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[scroce]

that field is in fact a date field in the underlying table -but i'm not sure what you mean about checking your references. How do I do that?

yes, i agree that something is definitely odd here - especially the fact the Month() and Day() work on the field as is, but Year() does not.

DatePart seems to work though. Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[JeremyNYC]

Go to the immediate window and choose Tools|References. If there is something that is MISSING (it will be all cap, I think), write down the name, unclick it, hit OK, and go back and click it again.

I doubt very much this is the cause, as the other date functions are working, but it couldn't hurt to check.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[scroce]

didn't see anything in there that was ALL CAPS. only a few things were checked on. I have never worked with references - What are they used for? Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[jiqjaq]

In Access, when you define a variable As Date you must compare in Date format, not strings or numbers. That menas you must use: If (Year([DepositDue])) = #2002#
The # characters around the value indicate that this value is to be considered to be in Date format.

 

[JeremyNYC]

jiqjaq,

Actually, that's not right. First, there's no variable here. Second, the year function returns an integer, so there is no need to offset a value it's tested against as a date.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[JeremyNYC]

scroce,

References point to other files that Access uses to execute functions, etc. You can point to DLLs, MDEs, MDBs, and probably lots of other stuff.

There's some info in the help files. Someone else could probably do better explaining it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.