query error

[awake2424]

I am using the code below to update the amount available reactions:

Private Sub cmdConfirm_Click()
On Error GoTo cmdConfirmErr
DoCmd.RunCommand acCmdSaveRecord 'save record
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateReactions" 'update Reactions
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec 'set form to new record OR use DoCmd.Close acForm, Me.Name to close the form
cmdConfirmExit:
DoCmd.SetWarnings True
Exit Sub
cmdConfirmErr:
MsgBox Err.Number & vbLf & Err.Description
GoTo cmdConfirmExit
End Sub

the data type of the reactions on the table is set to Number and the query:

(DLookUp("Reactions","tblLibrary")-Val([forms]![Patients]![Text119]))

I am getting a data type mismatch error 3464 and not sure why. Thanks.

 

[rzw0wr]

(DLookUp("Reactions","tblLibrary")-Val([forms]![Patients]![Text119]))

What are you trying to do here?
DAle

 

[billmeye]

Either Reactions or [forms]![Patients]![Text119] is not a number. Could it be that [forms]![Patients]![Text119] is null or contains other than a number? Have you tried using CDbl() instead of Val()?

 

[awake2424]

CDbl did not work as I get the same error.

[forms]![Patients]![Text119] how do I change this text box to number, I do not see that as a choice. Thanks.

 

[billmeye]

If [forms]![Patients]![Text119] is an unbound textbox, use Format property and choose a number format.