Data mismatch

[ili_sophia]

Hi there, i have this code in my search command button where it searched based on two field MXD and Batch no. However, i get the error of data type mismatch in criteria expression

does anyone know why

Sub Search()
Dim StrCriteria, task As String

Me.Refresh
If IsNull(Me.PO) Or IsNull(Me.BatchNumber) Then
    MsgBox "Please enter the MXD and Batch Number"
    Me.MXD.SetFocus
Else
    StrCriteria = "[P/O]='" & Me.PO & "' AND [BATCH NO] =" & Me.BatchNumber
      task = "Select * from [Setting Machine Production] subform where " & StrCriteria
  '  DoCmd.ApplyFilter task 'won't work using this method
 'order by [P/O]
    Me.SettingMPsubform.Form.RecordSource = task
End If

End Sub

 

[Minty]

Is BATCH NO actually a text field ? If it is then you'll need to add ' ' delimiters.
Similarly is P/O actually numeric in which case you'll need to get rid of the delimiters.

 

[Ranman256]

are PO's numeric, you have them as string:
"[P/O]='" & Me.PO & "'"

are BATCH NO's string? you have them as a numeric:
[BATCH NO] =" & Me.BatchNumber

and you can try:
me.filter = strCriteria
me.FilterOn = true

 

[ili_sophia]

There are both text Fields

 

[ili_sophia]

How do i make it criteria. where Po LIKE and/or Batch No LIKE?

 

[Minty]

You would add wild cards - but be warned this will get very slow if you have lots of data;

 StrCriteria = "[P/O]='*" & Me.PO & "*' AND [BATCH NO] Like '*" & Me.BatchNumber & "*'"

It would be better if you only have the wildcards at the end of the string, as Access will then be able to use any indexes you have on those fields.

 StrCriteria = "[P/O]='" & Me.PO & "*' AND [BATCH NO] Like '" & Me.BatchNumber & "*'"