Code is going to debug because of filed change from a number to text

[jcallavini]

Hi,

I had a module created for me several years ago that calculated a payroll system. I hade to make a change to one of the fields used in the calculation (Bus) that went from a number to a text field. This has created an error code of Data type mismatch in criteria expression. I know my field change has created this error, but I do not know how to fix it. Every table that uses a refrence to this field I have change from a number to a text, so I know that is not the problem. The code being used is below:

My question is where in the code does it reference a number vs a text field, and how can I fix this problem. Thank you for looking at my post and trying to help.

Function updatepay()
Dim dbs As Database, rst As Recordset
Dim strSQL, strbus As String

' Return reference to current database.
If Not (IsNull(Me!BUS)) Then
strbus = CStr(Me!BUS)
Set dbs = CurrentDb
strSQL = "SELECT * FROM [Employee Pay Schedule Pay]"
strSQL = strSQL + "WHERE [Employee Pay Schedule Pay].[BUS Number]= " + strbus
strSQL = strSQL + "AND [Employee Pay Schedule Pay].Type= '" + Me![DCODE] + "'"


Set rst = dbs.OpenRecordset(strSQL)

If Not (rst.EOF) Then
Me![HOURS] = rst![HOURS]
Me![SPEDRoutes] = rst![SPEDRoutes]
End If
rst.Close
Set dbs = Nothing
End If

and the module code is:

Function PayPerEvent()

Dim dbs As Database
Dim rst As Recordset
Dim empRst As Recordset
Dim rateRst As Recordset

Dim looping As Integer

Dim startDate As Date
Dim strSQL As String
Dim strStart As String
Dim strEnd As String
Dim NUMOFDAYS As Integer

Dim currentEmp As Single
Dim sumOfHours As Single
Dim base As Single
Dim payfactor As Single
Dim RATE As Single
Dim PAY As Single
Dim ot As Boolean

DoCmd.Hourglass True
startDate = [Forms]![WeeklyPayroll]![Start] - 1

For looping = 1 To 2
If looping = 1 Then
NUMOFDAYS = Forms![WeeklyPayroll]![Week1Days]
Else
NUMOFDAYS = Forms![WeeklyPayroll]![NUMOFDAYS]
End If

startDate = startDate + 1
strStart = CStr(startDate)
startDate = startDate + 6
strEnd = CStr(startDate)

'Return reference to current database.
Set dbs = CurrentDb
strSQL = "SELECT [tbl employ post data].* FROM [tbl employ post data] "
strSQL = strSQL + "WHERE (([tbl employ post data].[EVENT DATE]) "
strSQL = strSQL + "Between #" + strStart + "# And #" + strEnd + "#) "
strSQL = strSQL + "ORDER BY [tbl employ post data].[EMP#], [tbl employ post data].[EVENT DATE] "

Set rst = dbs.OpenRecordset(strSQL)

Do While Not (rst.EOF)

If currentEmp <> rst![EMP#] Then
'Initialize employee
currentEmp = rst![EMP#]
'open tblEmployee and Rate table information.
strSQL = "SELECT [tblEmployee].* FROM [tblEmployee] "
strSQL = strSQL + "WHERE ([tblEmployee].[intEMP#]= " + CStr(rst![EMP#]) + ")"
Set empRst = dbs.OpenRecordset(strSQL)
base = empRst![Weekly Hours] * NUMOFDAYS

strSQL = "SELECT [EW].* FROM [EW] "
strSQL = strSQL + "WHERE ([EW].[EMP#]= " + CStr(rst![EMP#]) + ")"
Set rateRst = dbs.OpenRecordset(strSQL)
RATE = rateRst![St Hr Wage]

ot = False
sumOfHours = 0
payfactor = 1

End If

If rst![EMP#] = 8157 Then
Debug.Print
End If

sumOfHours = sumOfHours + rst![HOURS]
rst.Edit
If Not (ot) Then
'Check if we are into over time empRst![OTT] is the OverTime Threshold
'from tblEmployee. See table design for more detail

If sumOfHours + base > empRst![OTT] Then
ot = True
payfactor = 1.5
rst!PAY = RATE * ((1.5 * (sumOfHours + base - 40)) + (rst!HOURS - (sumOfHours + base - 40)))
Else
rst!PAY = rst!HOURS * RATE
End If
Else
rst!PAY = payfactor * rst!HOURS * RATE
End If
If rst!SPEDRoutes > 0 Then
rst!PAY = rst!PAY + (rst!SPEDRoutes * 2)
End If
Debug.Print CStr(rst![EMP#]) + " " + CStr(rst![Event Date]) + " " + CStr(RATE) + " " + CStr(sumOfHours) + " " + CStr(rst!PAY)
rst.Update
rst.MoveNext
Loop

'****************************************************************
'do the whole thing again for overriden entries
'****************************************************************
'Debug.Print "*************************************"

strSQL = "SELECT [tbl employ post data].* FROM [tbl employ post data] "
strSQL = strSQL + "WHERE (([tbl employ post data].[ENTER DATE]) "
strSQL = strSQL + "Between #" + strStart + "# And #" + strEnd + "#) "
strSQL = strSQL + "AND [tbl employ post data].[OVERRIDE]"
strSQL = strSQL + "ORDER BY [tbl employ post data].[EMP#], [tbl employ post data].[EVENT DATE] "

Set rst = dbs.OpenRecordset(strSQL)

Do While Not (rst.EOF)
rst.Edit

'Initialize employee
strSQL = "SELECT [EW].* FROM [EW] "
strSQL = strSQL + "WHERE ([EW].[EMP#]= " + CStr(rst![EMP#]) + ")"
Set rateRst = dbs.OpenRecordset(strSQL)
RATE = rateRst![St Hr Wage]


rst!PAY = rst!HOURS * RATE
'Debug.Print CStr(rst![EMP#]) + " " + CStr(rst![Event Date]) + " " + CStr(rate) + " " + CStr(sumOfHours) + " " + CStr(rst!PAY)
If rst!SPEDRoutes > 0 Then
rst!PAY = rst!PAY + (rst!SPEDRoutes * 2)
End If
rst.Update
rst.MoveNext
Loop

Next looping
DoCmd.Hourglass False

End Function

 

[HarleyQuinn]

This line will be causing a problem, as it's not a numeric datatype not you'll need to enclose it in single quotes e.g.

strSQL = strSQL + "WHERE [Employee Pay Schedule Pay].[BUS Number]= '" & strbus & "'"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[jcallavini]

HarleyQuinn,

Thanks for your reply. That did take care of the debug however, now it will not bring back the hours and populate the hours field.

Do you have any other suggestions?

Joe

 

[dhookom]

Have you tried any debugging/trouble-shooting?
faq705-7148

Duane
Hook'D on Access
MS Access MVP

 

[jcallavini]

Hey,

I played around with it using different variations of Harley's code and got it to work.

Thanks for the help.

 

[missinglinq]

When you're concatenating things like

trSQL = strSQL + "WHERE ([EW].[EMP#]= " + CStr(rst![EMP#]) + ")"

you should be using an ampersand, not the plus sign.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!