Help me vba search

[bloedje]

Hello,

i got an access database that has an search form. problem is if i filter on data of birth that doesn't exist it will show everything instead of nothing at all.

can one of u please help me fix this problem?

followings is the code

Private Sub add_adjustcustomer_Click()

End Sub

DoCmd.OpenForm "Add adjustcustomer", acNormal, , "[Add/adjustcostomer]=" & Me.AllowFormView




End Sub

Private Sub cmdSearch_Click()
On erorr GoTo errr
Me.infoTBL_subform.Form.RecordSource = "SELECT * FROM InfoTBL " & BuildFilter
Me.infoTBL_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "\#dd\/mm\/yyyy\#"
varWhere = Null

If Me.txtID > "" Then
varWhere = varWhere & "[ID] like " & Me.txtID & " AND "
End If
If Me.txtName > "" Then
varWhere = varWhere & "[Name] like " & tmp & Me.txtName & tmp & " AND "
End If
If Me.txtDateFrom > "" Then
varWhere = varWhere & "([DOB] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Me.txtPhonenumber > "" Then
varWhere = varWhere & "[Phonenumber] like " & tmp & Me.txtPhonenumber & tmp & " AND "
End If
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function


Private Sub cmdClear_Click()
Me.infoTBL_subform.Form.RecordSource = "SELECT * FROM InfoTBL "
Me.infoTBL_subform.Requery
txtID = ""
txtName = ""
DateFrom = ""
txtPhonenumber = ""
txtID.SetFocus
End Sub

Private Sub cmdclose_Click()
DoCmd.Close
End Sub

Private Sub Command24_Click()
DoCmd.OpenForm "frminfotblrepair", acNormal
End Sub

Private Sub Command26_Click()
DoCmd.OpenForm "addinfocost&repair", acNormal
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()
cmdClear_Click
End Sub

Private Sub Dateto_Click()

End Sub

Private Sub infoTBL_subform_Enter()

End Sub

Private Sub txtID_Click()

End Sub

Private Sub txtPhonenumber_Click()

End Sub

thank you in advance

 

[DavidAtWork]

I'd look at your expressions such as:

If Me.txtID > "" Then

You should tailor your exressions to suit their data types, ie if txtID is a number then say
If Me.txtID > 0 Then


with text fields you can use
If Len(Me.txtName) > 1 Then


with dates
If Me.txtDateFrom > 0 Then

David

 

[bloedje]

Hello,

made this search field due a you tube film, my knowledge ist that good with vba

i understand what you mean, but i have no clue how insert this into the

If Me.txtDateFrom > "" Then
varWhere = varWhere & "([DOB] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

Iam trying to learn this, but its really hard

 

[DavidAtWork]

just use
If Me.txtDateFrom > 0 Then
varWhere = varWhere & "([DOB] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

Also if you need to test your criteria string as it gets built
if varWhere = Null then add the first piece of criteria, otherwise
and add an AND before the new piece of criteria is added
varWhere = varWhere & " AND " & new criteria

David

 

[bloedje]

just use
If Me.txtDateFrom > 0 Then
varWhere = varWhere & "([DOB] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

Also if you need to test your criteria string as it gets built
if varWhere = Null then add the first piece of criteria, otherwise
and add an AND before the new piece of criteria is added
varWhere = varWhere & " AND " & new criteria

David

But value isn't 0
if value is incorrect then is 0

thought it was something like that

 

[nschroeder]

I think you can solve your issue by changing the format property of txtDateFrom to "Short Date" (and probably renaming it to dtDateFrom). Then your code could be:

If Me.dtDateFrom > 0 Then
    varWhere = varWhere & "([DOB] >= " & Me.dtDateFrom & ") AND "
End If

 

[bloedje]

VBA error doesnt work, thank you for your time and work anyway

i will ceep it this way


Greetings

 

[nschroeder]

I tested the code before I posted, and it works. What error did you get?