Link to home
Start Free TrialLog in
Avatar of dsoderstrom
dsoderstrom

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try doing a compact and repair of the db.

also, try placing the codes in the Load event of the form
hmm, in the VBA window, do a Debug > compile
correct any errors raised
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.
Avatar of dsoderstrom
dsoderstrom

ASKER

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
can you upload the db.
Would be difficult to upload.  Very large and has links to several other Access and SQL databases.
If you open the immediate window and type ?now().  What is returned?
Also, type ?year(now()) and see what you get.

<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?
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
how about
?date
?year(date())
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)
add an option explicit outside your modules and see if you get any new errors trying to run the code.
sorry meant add

option explict

before the subs and functions (just at the top of the module)
also, if you run this in debug mode, what is the value of fyear when the error occurs.
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
No worries. Glad it worked. Crazy though. Year is supposed to return an integer