Data Type MisMatch

[JohnnyJones]

Sorry I'm quite new to access and vba and I'm having an issue with the highlighted part of my code. The error I'm getting is "data type mismatch in criteria expression". Can anyone see what the issue is here?


Private Sub BtnAddBooking_Click()

Dim CustomerID As String
Dim StaffID As String
Dim intPos As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim BathValue As Integer
Dim rst As DAO.Recordset
Dim RemainingBookingExists As String
Dim RemainingBaths As Integer
Dim RemainingBathsExists As String

If Len(Me.OpenArgs) > 0 Then
intPos = InStr(Me.OpenArgs, "|")

If intPos > 0 Then

CustomerID = Left$(Me.OpenArgs, intPos - 1)
StaffID = Mid$(Me.OpenArgs, intPos + 1)

End If
End If



If DCount("ID", "Remaining_Bookings", "CustomerID=" & CustomerID) > 0 Then
RemainingBookingExists = "Yes"
Else
RemainingBookingExists = "No"
End If



If RemainingBookingExists = "Yes" Then

Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 RemainingBaths FROM Remaining_Bookings WHERE CustomerID = CustomerID ORDER BY ID DESC")
rst.MoveFirst
RemainingBaths = rst.Fields("RemainingBaths")
rst.Close

End If


If RemainingBaths > 0 Then
RemainingBathsExists = "Yes"
Else
RemainingBathsExists = "No"
End If


If Me.UseRemainingBath = "Yes" And RemainingBathsExists = "No" Then
MsgBox "You have no Baths left to use, you must purchase one"
GoTo EndSub
End If



If RemainingBookingExists = "Yes" And Me.UseRemainingBath.Value = "No" And Me.BathsPurchased.Value > Me.BathsUsed.Value Then

' Open a connection and execute query
strSQL = "INSERT INTO Bookings (CustomerID,StaffID,BookingDate,BathsPurchased,BathsUsed,AmountPaid,UseRemainingBath) VALUES ('" & CustomerID & "', '" & StaffID & "', '" & Me.BookingDate & "', '" & Me.BathsPurchased & "', '" & Me.BathsUsed & "', '" & Me.AmountPaid & "', '" & Me.UseRemainingBath & "')"
CurrentDb.Execute strSQL

BathValue = Me.BathsPurchased.Value - Me.BathsUsed.Value
strSQL2 = "UPDATE Remaining_Bookings SET RemainingBaths = RemainingBaths + ' & BathValue & ' WHERE CustomerID = '" & CustomerID & "'"
CurrentDb.Execute strSQL2

End If

 

[JHB]

ID's is usually numbers so I guess you've to take away the two ':

strSQL2 = "UPDATE Remaining_Bookings SET RemainingBaths =  RemainingBaths + ' & BathValue & ' WHERE CustomerID =[B][COLOR=Red] '[/COLOR][/B]" &  CustomerID & "[B][COLOR=Red]'[/COLOR][/B]"

Correct it to:

strSQL2 = "UPDATE Remaining_Bookings SET RemainingBaths =  RemainingBaths + ' & BathValue & ' WHERE CustomerID =[B][COLOR=Red] [/COLOR][/B]" &  CustomerID & ""

 

[smig]

you probably try to match a String to a Number