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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.