Type mismatch on year function

[Guest]

What am I missing here. I feel stupid for asking, but I'm trying to use a
search criteria on some code where the current year is the filter criteria.

It never allows me to assign the current year -

Private Sub List0_AfterUpdate()
Dim RID, Yr, Today
Today = Year(Now)
End Sub

I get a "type mismatch" error. Why?

 

[Guest]

Works fine from a command button. You may be returning a date value to an
incompatible control type?? My 2 cents

 

[Brendan Reynolds]

While I would recommend explicitly declaring the data types of your
variables, that doesn't seem to be the immediate problem here - I copied and
pasted your code, and it runs without error for me. But presumably this is
not all of your code (as it doesn't do anything). Perhaps the problem is in
the part of the code that you didn't post?

 

[Guest]

Nope, it's 100% assuredly that piece of the code.

I had a "where" statement buried in a "DLookup" that included " AND [Year] =
" & Year(Now).

I started getting errors. When I cut that small piece of code out, the
errors stopped.

Then, instead of adding the code back in, I started using that "Year"
Function, declaring specific data types, etc. to try and figure it out
myself. I was using popup message windows to look at how the variables were
being defined in my subroutine.

That specific statement is where the errors appeared every time, and any
time I cut it out, I get no errors.

Also, before posting here, I ran the subroutine, just as posted, and got the
errors I mentioned.

If that's not causing errors for you guys, then I hope it's just some quirk.
I'll do the Windows thing and close down vBasic, Access, log off my
computer, reboot it and see if that fixes it. If not.... I'll be back.

 

[Guest]

It occurs to me that I'm calling a function "Year", and I have fields in
affected tables called "Year"..... be back with name changing results.....

 

[Guest]

I see your problem. In the procedure below, you left out an important step:
1. Close VBA Editor
2. Close Access
3. Log off Computer
4. Reboot Computer
5. Go to pub
6. Consume double Scotch
7. Go back to Computer and retry
8. Repeat 1 though 7 until either it works or you don't care any more

If that's not causing errors for you guys, then I hope it's just some quirk.
I'll do the Windows thing and close down vBasic, Access, log off my
computer, reboot it and see if that fixes it. If not.... I'll be back.

Nope, it's 100% assuredly that piece of the code.

I had a "where" statement buried in a "DLookup" that included " AND [Year] =
" & Year(Now).

I started getting errors. When I cut that small piece of code out, the
errors stopped.

Then, instead of adding the code back in, I started using that "Year"
Function, declaring specific data types, etc. to try and figure it out
myself. I was using popup message windows to look at how the variables were
being defined in my subroutine.

That specific statement is where the errors appeared every time, and any
time I cut it out, I get no errors.

Also, before posting here, I ran the subroutine, just as posted, and got the
errors I mentioned.

If that's not causing errors for you guys, then I hope it's just some quirk.
I'll do the Windows thing and close down vBasic, Access, log off my
computer, reboot it and see if that fixes it. If not.... I'll be back.

While I would recommend explicitly declaring the data types of your
variables, that doesn't seem to be the immediate problem here - I copied and
pasted your code, and it runs without error for me. But presumably this is
not all of your code (as it doesn't do anything). Perhaps the problem is in
the part of the code that you didn't post?

 

[Guest]

Looks like vBasic isn't recognizing the "Year" function. I changed my field
names in the tables, and now I get "Microsoft Access can't find the field
'Year' referred to in your expression.

???

I'm working in VisualBasic 6.0, by the way, if that makes any difference at
all.

 

[Guest]

Now THAT'S a helpful post....

I see your problem. In the procedure below, you left out an important step:
1. Close VBA Editor
2. Close Access
3. Log off Computer
4. Reboot Computer
5. Go to pub
6. Consume double Scotch
7. Go back to Computer and retry
8. Repeat 1 though 7 until either it works or you don't care any more

If that's not causing errors for you guys, then I hope it's just some quirk.
I'll do the Windows thing and close down vBasic, Access, log off my
computer, reboot it and see if that fixes it. If not.... I'll be back.

Nope, it's 100% assuredly that piece of the code.

I had a "where" statement buried in a "DLookup" that included " AND [Year] =
" & Year(Now).

I started getting errors. When I cut that small piece of code out, the
errors stopped.

Then, instead of adding the code back in, I started using that "Year"
Function, declaring specific data types, etc. to try and figure it out
myself. I was using popup message windows to look at how the variables were
being defined in my subroutine.

That specific statement is where the errors appeared every time, and any
time I cut it out, I get no errors.

Also, before posting here, I ran the subroutine, just as posted, and got the
errors I mentioned.

If that's not causing errors for you guys, then I hope it's just some quirk.
I'll do the Windows thing and close down vBasic, Access, log off my
computer, reboot it and see if that fixes it. If not.... I'll be back.

While I would recommend explicitly declaring the data types of your
variables, that doesn't seem to be the immediate problem here - I copied and
pasted your code, and it runs without error for me. But presumably this is
not all of your code (as it doesn't do anything). Perhaps the problem is in
the part of the code that you didn't post?

--
Brendan Reynolds (MVP)


What am I missing here. I feel stupid for asking, but I'm trying to use a
search criteria on some code where the current year is the filter
criteria.

It never allows me to assign the current year -

Private Sub List0_AfterUpdate()
Dim RID, Yr, Today
Today = Year(Now)
End Sub

I get a "type mismatch" error. Why?

 

[Guest]

Okay, thanks for helping me out, folks. I created a small "test" database
with a couple of fields, and ran the code successfully.

This is a database that I inherited, and I'm guessing that a number of the
tables in this database have fields called "Year", and that's the problem I'm
having here, even if it's not bound to the form or the tables affected by
this specific subroutine.

 

[Marshall Barton]

I don't think a field name conflict can occur if the form is
not bound to a table/query that doesn't have the field.

Unfortunately, the only othr thing I can think that might
cause that error is if somewhere in your application,
someone declared a user defined function named Year. Try a
Current Project search (Edit - Find menu) from any code
module.

 

[Guest]

Thanks for the idea... I did the search on the whole project, the only match
for "year" was the code listed originally where the error popped up.

I'm going an analyzer on it, and will see if any of my SQL selects are using
that name, as well.

I did go through and changed all the "Year" names in the tables... no
difference to the error message, as you indicated would be the case.

I don't think a field name conflict can occur if the form is
not bound to a table/query that doesn't have the field.

Unfortunately, the only othr thing I can think that might
cause that error is if somewhere in your application,
someone declared a user defined function named Year. Try a
Current Project search (Edit - Find menu) from any code
module.
--
Marsh
MVP [MS Access]

Okay, thanks for helping me out, folks. I created a small "test" database
with a couple of fields, and ran the code successfully.

This is a database that I inherited, and I'm guessing that a number of the
tables in this database have fields called "Year", and that's the problem I'm
having here, even if it's not bound to the form or the tables affected by
this specific subroutine.

 

[Tim Ferguson]

Thanks for the idea... I did the search on the whole project, the only
match for "year" was the code listed originally where the error popped
up.

I suppose you have looked at the References window and checked for any
"missing" lines?


Tim F

 

[Guest]

No luck. However, what I was trying to do can be done in other ways.

Since the "Year" function works as an Access expression, instead of using
the function as a filter/limiting variable, I created a local, non-visible
field on my form that carries the current year, and then I just use that
field instead of deriving the value on the fly like I was trying to before.

I'm sure there's a reference buried somewhere in this database that screwing
things up, but for what I'm trying to do, it's not worth that much effort.
It would be nice in an FYI kind of way, but there's no need for me to bog
down when there are a number of workarounds that can be done.

Thanks to everyone for trying to help me solve this one.