Search from not pulling up all my data

[wnicole]

I have a query and search form with this critera in each field for: Employee, Job Number etc.

Example: Like "*" & [forms]![F_SearchFormWide]![Job Number] & "*"

If I put in may the first 3 characters of the job it pulls it up but when I put the full number nothing comes up.


Also the DB has 40,000 records. The total at bottom of query is only 16,000.
I have tried verifying my join connections making sure all jobs are pulling from job table, I tried all 3 ways. But for the life of me can't figure out why its not pulling up all my records.

Do you think you can help?

[wnicole]

I'm looking for a link to a nice multi field Search Form setup or video link.
The one I created is just acting strange...not pulling up all data. (see above the criteria I'm using)

Can you send me to one? Pretty simple form nothing complicated.
Thanks

[ItsMe]

I don't know exactly what you are doing with your form but here is a very forgiving way to build a where clause. Basicaly, you just need to insert your field names and control names. You may need to adjust the method for verifying data exists in a given field. Perhaps default value = 0. Then the IsNull would not work.

The major trick to this code is to determine ONE field that must have data and DEMAND the user input data into that field in order to begin. In this example that field is "Customer".

    Dim varWhere As Variant
    varWhere = Null

    If Not IsNull(Me.Customer) Then
        varWhere = "[Customer] Like '" & Me.Customer & "*'"
    End If
    
    If Not IsNull(Me.Field2) Then
        varWhere = (varWhere + " AND ") & "[Field2] LIKE '" & Me.Field2 & "*'"
    End If

    If Not IsNull(Me.Field3) Then
    
        varWhere = (varWhere + " AND ") & "[Field3] LIKE '" & Me.Field3 & "*'"
    End If
    
    If Not IsNull(Me.Field4) Then
    
        varWhere = (varWhere + " AND ") & "[Field4] LIKE '" & Me.Field4 & "*'"
    End If

[wnicole]

I tried this but its saying something about "separator" doesn't seem to like that my fields have spaces in them.
Also syntax error. Is this correct?

Private Sub Command63_Click()

DoCmd.OpenQuery "Q_SearchForm", , acEdit

DoCmd.Requery



Dim varWhere As Variant
varWhere = Null

If Not IsNull(Me.Job Number) Then
varWhere = "[Job Number] Like '" & Me.JobNumber & "*'"
End If

If Not IsNull(Me.Project: Name) Then
varWhere = (varWhere & " AND ") & "[Project: Name] LIKE '" & Me.Project: Name & "*'"
End If

If Not IsNull(Me.Address) Then

varWhere = (varWhere & " AND ") & "[Address] LIKE '" & Me.Address & "*'"
End If

If Not IsNull(Me.Employee) Then

varWhere = (varWhere & " AND ") & "[Employee] LIKE '" & Me.Employee & "*'"
End If


End Sub

[ItsMe]

What is this? Is this a field name within a query?
Project: Name

On one line you have
Me.Job Number

then you have
Me.JobNumber

Anything Me. should be the name of a control on the form the user is typing their criteria into.

[ItsMe]

I noticed something wrong with my code too. I will edit post # 4

(varWhere & " AND ")
should be
(varWhere + " AND ")