Performing a Custom Search 1

[quietstormtw]

Hi all,

Awhile back, in 2003, there was already a post for the issue...but I can't seem to find it no matter how I search. I hope that someone remembers this oldie. The thread number was 702-563930...

Anyway, my issue now is that I can't seem to search the db looking for a number. The number has preceeding zeros. However, I created a new field using the VAL function. The ID # is 000038. The VAL converted # is 38. My code is as follows:

Private Sub Preview_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "Select qryMain.*" & _
" From qryMain"

strWhere = "Where"

strOrder = "Order BY qryMain.AGTLASTNAME;"


If Not IsNull(Me.txtAgent) Then
strWhere = strWhere & " (qryMain.CAREERAGTNBR) = " & Me.txtAgent & " AND "
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Set qryDef = dbNm.QueryDefs("qryDateRange")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

Dim rsCnt As Variant
rsCnt = DCount("[qryMain.CAREERAGTNBR]", "qryDateRange")
If rsCnt = 0 Then
MsgBox "There is no data for this report. The request wil be canceled...", vbOKOnly, "No Data"
Else
DoCmd.Close acForm, "frmDateRange", acSaveYes
DoCmd.OpenForm "Switchboard", acNormal
DoCmd.OpenQuery "qryDateRange", acViewNormal

End If

End Sub


Thanks for any assistance in advance!!

 

[quietstormtw]

I forgot to mention that I'm getting a run-time error '3464':

Data type mismatch in criteria expression

 

[rjoubert]

Your CAREERAGTNBR field is a Text field? This is the one with the leading zeros? And will your textbox have the leading zeros in it as well? Or will the user simply enter 38 (or whatever number they want to find)?

If you're dealing with a text field here, you'll need to add single quotes around the value passed into your query...

If Not IsNull(Me.txtAgent) Then
strWhere = strWhere & " (qryMain.CAREERAGTNBR) = '" & Me.txtAgent & "' AND "
End If

 

[PHV]

Or this ?
strWhere = strWhere & " Val(qryMain.CAREERAGTNBR) = " & Val(Me.txtAgent) & " AND "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[quietstormtw]

Thanks RJOUBERT,

It worked perfectly! A star for you.

Thanks for your help PHV. However, I haven't been able to get you suggestion to work yet...and time is of the essence. Although, I'm sure it 's my error

Thanks to you both