type mismatch error

I have the code shown below on the open event on a form in Access 2003.  When opening the form, I get a type mismatch error on the line that says "If fyear > Year(Now()) Then".  I copied this code from another form where it works fine.  What could be the problem?

    Dim rs As DAO.Recordset
    Dim fyear As Integer
    Dim i As Integer
   
    DoCmd.OpenQuery "Clear FiscalYears"
    Set rs = CurrentDb.OpenRecordset("FiscalYears")
    fyear = 2000
    For i = 1 To 100
        fyear = fyear + 1
        If fyear > Year(Now()) Then
            Exit For
        End If
        rs.AddNew
        rs![fiscalyear] = Format(fyear, "0000")
        rs.Update
    Next i
   
    rs.Close
dsoderstromAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try doing a compact and repair of the db.

also, try placing the codes in the Load event of the form
0
Rey Obrero (Capricorn1)Commented:
hmm, in the VBA window, do a Debug > compile
correct any errors raised
0
peter57rCommented:
There is nothing obviously wrong in your code.
I have doubts over the statement :

 rs![fiscalyear] = Format(fyear, "0000")
This implies that Fiscalyear is a text field.
If it's number then you should not be using the format function.

Try Tools>References in the VBA window and make sure nothing is marked as missing.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

dsoderstromAuthor Commented:
I have done a compact and repair of the database.
I tried moving the code to the Load event of the form.
I did do a compile of the database and it compiled with no errors.
I still get the same error message
0
Rey Obrero (Capricorn1)Commented:
can you upload the db.
0
dsoderstromAuthor Commented:
Would be difficult to upload.  Very large and has links to several other Access and SQL databases.
0
joeywCommented:
If you open the immediate window and type ?now().  What is returned?
Also, type ?year(now()) and see what you get.
0
Rey Obrero (Capricorn1)Commented:

<I copied this code from another form where it works fine.  What could be the problem?> is the form wher you copied the codes from, in the same app.

try using Date instead of Now() , did u still get the error?
0
dsoderstromAuthor Commented:
When I type ?now() in the immediate window I get 3/31/2011 9:21:30 AM
When I type ?year(now()) I get 2011
0
Rey Obrero (Capricorn1)Commented:
how about
?date
?year(date())
0
dsoderstromAuthor Commented:
The form that I copied the code from is in this same Access database.
I get the same mismatch message whether I use Year(Now())  or Year(Date)
0
joeywCommented:
add an option explicit outside your modules and see if you get any new errors trying to run the code.
0
joeywCommented:
sorry meant add

option explict

before the subs and functions (just at the top of the module)
0
joeywCommented:
also, if you run this in debug mode, what is the value of fyear when the error occurs.
0
Rey Obrero (Capricorn1)Commented:
what joeyw is saying is

Option Compare Database
Option Explicit  '< Add this line in all your modules (Form and regular)

then run a DEBUG > Compile again
0
dsoderstromAuthor Commented:
when I type ?date in the immediate window I get 3/31/2011
when i type ?year(date) i get 2011
when i type ?year(date()) i get 2011
When I add option explicit I still get just the same mismatch error when opening the form and it compiles with no errors.
the value of fyear in debug mode after the error occurs is 2001.
0
Rey Obrero (Capricorn1)Commented:
try creating a new form, and place the codes in the load/ or open event of the new form, see if you still get the error
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joeywCommented:
this shouldn't be necessary, but grasping at straws:

try fyear > cint(year(now())  or cint(year(date)
0
dsoderstromAuthor Commented:
The cint option solved the problem.  Hope it's okay with you joeyw if i split the points between you and capricorn1.  You both had a lot of input on this.
I appreciate the help
0
joeywCommented:
No worries. Glad it worked. Crazy though. Year is supposed to return an integer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.