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("F iscalYears ")
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
Dim rs As DAO.Recordset
Dim fyear As Integer
Dim i As Integer
DoCmd.OpenQuery "Clear FiscalYears"
Set rs = CurrentDb.OpenRecordset("F
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
hmm, in the VBA window, do a Debug > compile
correct any errors raised
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.
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.
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
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.
ASKER
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.
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?
ASKER
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
When I type ?year(now()) I get 2011
how about
?date
?year(date())
?date
?year(date())
ASKER
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)
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)
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
Option Compare Database
Option Explicit '< Add this line in all your modules (Form and regular)
then run a DEBUG > Compile again
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I appreciate the help
No worries. Glad it worked. Crazy though. Year is supposed to return an integer
also, try placing the codes in the Load event of the form