Type mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I made a statement as following:
If Year(Rs("EncourageDate")) = Year(Now()) And Month(Rs("EncourageDate"))
= Me!Month

It shows the result as "Type mismatch"

Would you please give me some instruction on how to solve it?
 
The code looks fine but I prefer to use additional parentheses to make it
clear like:

If (Year(Rs("EncourageDate")) = Year(Now())) And _
(Month(Rs("EncourageDate")) = Me!Month) Then

(don't forget "Then" like what you posted).

Are you sure this is the like that errored out?

When you get the error, hit Ctrl + Break and select Debug and see which line
actually errors out.
 
Note also the Month() function returns Integer data type so the value of
your Control Month should be numeric and not Text like "Jan" / "January"
 
I have done like you instructed as following:
If (Year(Rs("EncourageDate")) = Year(Now())) And (Month(Rs("EncourageDate"))
= Me!Month) Then

But the result of debug is still errored out from this statement. Would you
please give me more instruction.

Thanks
Shell
 
If month is the name of a field or control on the form, try putting square
brackets around it. It is a reserved word, which should not be used as the
name of a control, field, or object.
What exactly is Rs("EncourageDate")?
 
Tom:
I have done like your instuction, but there was nothing show in Immediate
Window.

Thanks

Shell Hu
 
Rs("EncourageDate") is a date field. I have done like you instructed putting
square brackets around Month. But the result is the same.

Thanks
Shell
 
Hi Shell,

First, do you have Option Explicit as the second line of code in all of your
modules? If not, please refer to this article to learn why this is important:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Next, does your code compile without any errors? Click on Debug > Compile
ProjectName, where ProjectName is the name of your VBA project (likely the
same as the name of your database). If the code compiles okay, but you are
getting a run-time error, then you might find the solution in this article:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html


The fact that nothing prints to the Immediate window definately suggests a
problem. This indicates that the referenced fields are null. Try placing a
break point on the line in question, and hover your mouse over the various
parts to see what values are present.

Copy the entire procedure and paste it into a reply.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Tom:
When I debug the code and put the mouse on the emphasis code, it
shows:Month(Rs("EncourageDate")=Type mismatch

Would you please give me some ideas what that mean?

Thanks
Shell
 
Hi Shell,

What data type is EncourageDate?
From Access Help for the Month function:

Syntax
Month(date)

The required date argument is any Variant, numeric expression, string
expression, or any combination, that can represent a date. If date contains
Null, Null is returned.

Try inserting the following:

Dim varTest As Variant

varTest = rs1("EncourageDate")
Debug.Print "EncourageDate is a " & TypeName(varTest)


Also, can you post the code for your entire procedure?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
EncourageDate is a date field. I have tested the way you instructed. The
result is the same. I attached the complete code for your reference:
Private Sub Payment_Calculation_Click()
On Error GoTo Err_Payment_Calculation_Click
Dim ss As Single
Dim varTest As Variant
Dim STemp As String
Dim i As Integer
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
If IsNull(Me!EmployeeID) = True Then
MsgBox "Please input EmployeeID,it should not be empty", vbOKOnly,
"Input EmployeeID"
Me!EmployeeID.SetFocus
ElseIf IsNull(Me!Month) = True Then
MsgBox "Please input Month,it should not be empty", vbOKOnly, "Input
Month"
Me!Month.SetFocus
Else
STemp = "Select * From [Employee Encouragement]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
Me!Bonus = 0
For i = 1 To Rs.RecordCount

If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If (Year(Rs("EncourageDate")) = Year(Now())) And
(Month(Rs("EncourageDate")) = Me![Month]) Then
varTest = Rs("EncourageDate")
Debug.Print "EncourageDate is a" & TypeName(varTest)

Me!Bonus = Me!Bonus + Rs("EncouragePayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

STemp = "Select * From [Employee Punishment]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
For i = 1 To Rs.RecordCount
If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If Year(Rs("PunishDate")) = Year(Now()) And
Month(Rs("PunishDate")) = Me![Month] Then
Me!PunishTotal = Me!PunishTotal + Rs("PunishPayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

Me!TotalPayment = Me!Bonus + Me!BasicSalary + Me!UnstableSalary +
Me!ContractAllowance + Me!FoodAllowance + Me!HouseAllowance +
Me!TemperaryAllowance + Me!PositionSalary + Me!ServiceSalary + Me!AuditSalary
Me!TotalDeduct = Me!PunishTotal + Me!WaterElectrialCost +
Me!LeaveDeduct + Me!DutyDeduct + Me!Compo + Me!HouseAccumulation +
Me!MedicalInsurance + Me!SocialInsurance + Me!UnemployeementInsurance +
Me!PregnantInsurance
Me!SalaryTotal = Me!TotalPayment - Me!TotalDeduct
Me!Tax = IncomeTax(CSng("& Me!SalaryTotal &"))
Me!ActualSalary = Me!SalaryTotal - Me!Tax
MsgBox "All the salary result has been calculated successfully",
vbOKOnly, "Calculate Complete"
End If
Set Rs = Nothing




Exit_Payment_Calculation_Click:
Exit Sub

Err_Payment_Calculation_Click:
MsgBox Err.Description
Resume Exit_Payment_Calculation_Click

End Sub

Would you please give me more instruction?

Thanks

Shell
 
Hi Shell,
I have tested the way you instructed.
Click to expand...

Yes, but you did not include the results that were printed to the immediate
window for this statement:

Debug.Print "EncourageDate is a" & TypeName(varTest)

Also, this line of code should be inserted *prior to* the line of code that
is bombing out. You had it placed after the line of code that is bombing, so
you probably never saw the result printed.

A few more questions for you:
1.) Has this code ever worked, or are you trying to get it to work for the
first time

2.) What is the data type for the Month field? Please give some examples of
data values that are found in this field. Note: It must be a date/time,
numeric expression, string expression, or any combination, that can represent
a date.

3.) This prompt is expecting what type of entry from a user:
MsgBox "Please input Month, it should not be empty", vbOKOnly, "Input
Month"

A number (1 through 12), a string (January, February, etc.)?

I think you are definately getting bitten by a reserved word issue, which
Van pointed out earlier. Try renaming the Month field in your table to
anything else that is not considered a reserved word in Access or JET. Also,
rename the textbox to something like txtMonth in case it is named Month. Use
this list as a handy reference for names that you should avoid using:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Try the following modifications, and then copy the results from the
Immediate window and paste them into a reply:


Option Compare Database
Option Explicit

Private Sub Payment_Calculation_Click()
On Error GoTo Err_Payment_Calculation_Click
Dim ss As Single
Dim varTest As Variant
Dim STemp As String
Dim i As Integer
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

If IsNull(Me!EmployeeID) = True Then
MsgBox "Please input EmployeeID, it should not be empty.", _
vbCritical + vbOKOnly, "Input Employee ID."
Me!EmployeeID.SetFocus
ElseIf IsNull(Me!MonthX) = True Then
MsgBox "Please input Month, it should not be empty.", _
vbCritical + vbOKOnly, "Input Month."
Me!Month.SetFocus
Else
STemp = "Select * From [Employee Encouragement]"
Rs.Open STemp, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Rs.MoveFirst
Me!Bonus = 0
For i = 1 To Rs.RecordCount
varTest = Rs("EncourageDate")

Debug.Print "EncourageDate is a " & _
TypeName(varTest) & " = to: " & varTest
Debug.Print "Year = " & Year(Rs("EncourageDate"))
Debug.Print "Month = " & Month(Rs("EncourageDate"))
Debug.Print

If Rs("EmployeeID") = Me!EmployeeID And _
Rs("IntoSalary").Value = True Then
If (Year(Rs("EncourageDate")) = Year(Now())) And _
(Month(Rs("EncourageDate")) = Me![MonthX]) Then
varTest = Rs("EncourageDate")

Me!Bonus = Me!Bonus + Rs("EncouragePayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close
Stop


Notes:
I'm only showing the first half of the procedure. I added a Stop statement,
which you will need to remove once you get this much working properly. For my
testing, I renamed the Month field to MonthX and the text box to txtMonth.


Just a comment....you have a table or query named "Employee Punishment"?
Ouch! That doesn't sound like a great way of motivating employees.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Shell said:
EncourageDate is a date field. I have tested the way you instructed. The
result is the same. I attached the complete code for your reference:

Private Sub Payment_Calculation_Click()
On Error GoTo Err_Payment_Calculation_Click
Dim ss As Single
Dim varTest As Variant
Dim STemp As String
Dim i As Integer
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
If IsNull(Me!EmployeeID) = True Then
MsgBox "Please input EmployeeID,it should not be empty", vbOKOnly,
"Input EmployeeID"
Me!EmployeeID.SetFocus
ElseIf IsNull(Me!Month) = True Then
MsgBox "Please input Month,it should not be empty", vbOKOnly, "Input
Month"
Me!Month.SetFocus
Else
STemp = "Select * From [Employee Encouragement]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
Me!Bonus = 0
For i = 1 To Rs.RecordCount

If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If (Year(Rs("EncourageDate")) = Year(Now())) And
(Month(Rs("EncourageDate")) = Me![Month]) Then
varTest = Rs("EncourageDate")
Debug.Print "EncourageDate is a" & TypeName(varTest)

Me!Bonus = Me!Bonus + Rs("EncouragePayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

STemp = "Select * From [Employee Punishment]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
For i = 1 To Rs.RecordCount
If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If Year(Rs("PunishDate")) = Year(Now()) And
Month(Rs("PunishDate")) = Me![Month] Then
Me!PunishTotal = Me!PunishTotal + Rs("PunishPayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

Me!TotalPayment = Me!Bonus + Me!BasicSalary + Me!UnstableSalary +
Me!ContractAllowance + Me!FoodAllowance + Me!HouseAllowance +
Me!TemperaryAllowance + Me!PositionSalary + Me!ServiceSalary + Me!AuditSalary
Me!TotalDeduct = Me!PunishTotal + Me!WaterElectrialCost +
Me!LeaveDeduct + Me!DutyDeduct + Me!Compo + Me!HouseAccumulation +
Me!MedicalInsurance + Me!SocialInsurance + Me!UnemployeementInsurance +
Me!PregnantInsurance
Me!SalaryTotal = Me!TotalPayment - Me!TotalDeduct
Me!Tax = IncomeTax(CSng("& Me!SalaryTotal &"))
Me!ActualSalary = Me!SalaryTotal - Me!Tax
MsgBox "All the salary result has been calculated successfully",
vbOKOnly, "Calculate Complete"
End If
Set Rs = Nothing




Exit_Payment_Calculation_Click:
Exit Sub

Err_Payment_Calculation_Click:
MsgBox Err.Description
Resume Exit_Payment_Calculation_Click

End Sub

Would you please give me more instruction?

Thanks

Shell
Click to expand...
 
Tom:
Thanks for your help. I changed the field name. It is done
Thanks

Shell

Tom Wickerath said:
Hi Shell,
I have tested the way you instructed.
Click to expand...

Yes, but you did not include the results that were printed to the immediate
window for this statement:

Debug.Print "EncourageDate is a" & TypeName(varTest)

Also, this line of code should be inserted *prior to* the line of code that
is bombing out. You had it placed after the line of code that is bombing, so
you probably never saw the result printed.

A few more questions for you:
1.) Has this code ever worked, or are you trying to get it to work for the
first time

2.) What is the data type for the Month field? Please give some examples of
data values that are found in this field. Note: It must be a date/time,
numeric expression, string expression, or any combination, that can represent
a date.

3.) This prompt is expecting what type of entry from a user:
MsgBox "Please input Month, it should not be empty", vbOKOnly, "Input
Month"

A number (1 through 12), a string (January, February, etc.)?

I think you are definately getting bitten by a reserved word issue, which
Van pointed out earlier. Try renaming the Month field in your table to
anything else that is not considered a reserved word in Access or JET. Also,
rename the textbox to something like txtMonth in case it is named Month. Use
this list as a handy reference for names that you should avoid using:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Try the following modifications, and then copy the results from the
Immediate window and paste them into a reply:


Option Compare Database
Option Explicit

Private Sub Payment_Calculation_Click()
On Error GoTo Err_Payment_Calculation_Click
Dim ss As Single
Dim varTest As Variant
Dim STemp As String
Dim i As Integer
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

If IsNull(Me!EmployeeID) = True Then
MsgBox "Please input EmployeeID, it should not be empty.", _
vbCritical + vbOKOnly, "Input Employee ID."
Me!EmployeeID.SetFocus
ElseIf IsNull(Me!MonthX) = True Then
MsgBox "Please input Month, it should not be empty.", _
vbCritical + vbOKOnly, "Input Month."
Me!Month.SetFocus
Else
STemp = "Select * From [Employee Encouragement]"
Rs.Open STemp, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Rs.MoveFirst
Me!Bonus = 0
For i = 1 To Rs.RecordCount
varTest = Rs("EncourageDate")

Debug.Print "EncourageDate is a " & _
TypeName(varTest) & " = to: " & varTest
Debug.Print "Year = " & Year(Rs("EncourageDate"))
Debug.Print "Month = " & Month(Rs("EncourageDate"))
Debug.Print

If Rs("EmployeeID") = Me!EmployeeID And _
Rs("IntoSalary").Value = True Then
If (Year(Rs("EncourageDate")) = Year(Now())) And _
(Month(Rs("EncourageDate")) = Me![MonthX]) Then
varTest = Rs("EncourageDate")

Me!Bonus = Me!Bonus + Rs("EncouragePayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close
Stop


Notes:
I'm only showing the first half of the procedure. I added a Stop statement,
which you will need to remove once you get this much working properly. For my
testing, I renamed the Month field to MonthX and the text box to txtMonth.


Just a comment....you have a table or query named "Employee Punishment"?
Ouch! That doesn't sound like a great way of motivating employees.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Shell said:
EncourageDate is a date field. I have tested the way you instructed. The
result is the same. I attached the complete code for your reference:

Private Sub Payment_Calculation_Click()
On Error GoTo Err_Payment_Calculation_Click
Dim ss As Single
Dim varTest As Variant
Dim STemp As String
Dim i As Integer
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
If IsNull(Me!EmployeeID) = True Then
MsgBox "Please input EmployeeID,it should not be empty", vbOKOnly,
"Input EmployeeID"
Me!EmployeeID.SetFocus
ElseIf IsNull(Me!Month) = True Then
MsgBox "Please input Month,it should not be empty", vbOKOnly, "Input
Month"
Me!Month.SetFocus
Else
STemp = "Select * From [Employee Encouragement]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
Me!Bonus = 0
For i = 1 To Rs.RecordCount

If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If (Year(Rs("EncourageDate")) = Year(Now())) And
(Month(Rs("EncourageDate")) = Me![Month]) Then
varTest = Rs("EncourageDate")
Debug.Print "EncourageDate is a" & TypeName(varTest)

Me!Bonus = Me!Bonus + Rs("EncouragePayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

STemp = "Select * From [Employee Punishment]"
Rs.Open STemp, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Rs.MoveFirst
For i = 1 To Rs.RecordCount
If Rs("EmployeeID") = Me!EmployeeID And Rs("IntoSalary").Value =
True Then
If Year(Rs("PunishDate")) = Year(Now()) And
Month(Rs("PunishDate")) = Me![Month] Then
Me!PunishTotal = Me!PunishTotal + Rs("PunishPayment")
End If
Rs.MoveNext
Else
Rs.MoveNext
End If
Next i
Rs.Close

Me!TotalPayment = Me!Bonus + Me!BasicSalary + Me!UnstableSalary +
Me!ContractAllowance + Me!FoodAllowance + Me!HouseAllowance +
Me!TemperaryAllowance + Me!PositionSalary + Me!ServiceSalary + Me!AuditSalary
Me!TotalDeduct = Me!PunishTotal + Me!WaterElectrialCost +
Me!LeaveDeduct + Me!DutyDeduct + Me!Compo + Me!HouseAccumulation +
Me!MedicalInsurance + Me!SocialInsurance + Me!UnemployeementInsurance +
Me!PregnantInsurance
Me!SalaryTotal = Me!TotalPayment - Me!TotalDeduct
Me!Tax = IncomeTax(CSng("& Me!SalaryTotal &"))
Me!ActualSalary = Me!SalaryTotal - Me!Tax
MsgBox "All the salary result has been calculated successfully",
vbOKOnly, "Calculate Complete"
End If
Set Rs = Nothing




Exit_Payment_Calculation_Click:
Exit Sub

Err_Payment_Calculation_Click:
MsgBox Err.Description
Resume Exit_Payment_Calculation_Click

End Sub

Would you please give me more instruction?

Thanks

Shell
Click to expand...
Click to expand...
 
Hi Shell,

That's great. I'm glad we can close the book on this thread.

Please do consider downloading and running Access MVP Allen Browne's
Database Issue Checker Utility on your databases. This free utility will help
you identify reserved words and special characters that have been used in
field and table names. You'll save yourself a lot of grief in the future if
you make it a habit to avoid reserved words and special characters. You can
get this utility here:

http://allenbrowne.com/AppIssueChecker.html

Also, for your reference, here is a KB article on special characters that
you should avoid, including spaces in names:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems with a query; result to textbox. 2
Goto Record 1
Type Mismatch 14
run time error 3464 4
DateAdd and Month functions 7
Access 2003 code not working in 2007 4
baffling type mismatch error 7
Type Mismatch with OpenRecordset...what!? 9

Back
Top