If criteria is null return all

[Guest]

If criteria is null return all
That all I want to do but after trying several ways I can't get any to work.
Here is what I have tried so far for the criteria

Like [forms]![search]![name] & "*"
<>Which doesn't work since I have 10 fields I want to search and using
this causes null values in some fields to filter out.
Like "*" & [forms]![search]![name] & "*"
<>Same as above
[forms]![search]![name] and ([forms]![Search]![name] is null)
<> this causes the query to response with insufficient memory error

Below is the SQL of my query so you can see how many fields I am dealing with.

SELECT UpdatedTable.[Who are you?], UpdatedTable.Date, UpdatedTable.[User
ID], UpdatedTable.[Profit Center], UpdatedTable.[Policy Number],
UpdatedTable.[Call Type], UpdatedTable.[Action Taken on Call],
UpdatedTable.[Reason for Disconnect], UpdatedTable.[Problem Description]
FROM UpdatedTable;

 

[Rick B]

Just enter your criteria Like [forms]![search]![name] & "*" under each
field you want to check. Put each piece of criteria on a separate row in
the query grid to create an "OR" condition, or on the same line to create an
"AND" condition.

What ten fields are you searching? Are they all name fields? If so, you
have a flawed data structure and you will run into lots of headaches unless
you fix the data structure. If the name may appear in one of several
fields, then you have a one-to-many relationship that should be handled with
a separate related table.

Post back with some details if you need help with the structure of your
table.

 

[Guest]

Here is the structure of my data.

maintracker:
[AssociateID] = the user id (this is tracked by there sign in name which is
a 6 digit user id)
[date] = the date they took the call
[UserID] = ID of the person contacting the associate
[ProfitCenter] = Where the user is located > selection is linked to table
[Policy Number] = ID of the customer's policy
[Call Type] = Subject type of call > selection is linked to table
[Action Taken on Call] = Basic action our associate did > selection is
linked to table
[Reason for Disconnect] = In certain cases they will fill this out if they
were required to remove a particualr option > selection is linked to table
[Problem Description] = A list of major problems encountered by custmer's >
selection is linked to table
[Comments] = Allows our user to put in comments for later follow ups or
questions.


I hope this helps with the below also. I attempted to use the below but
both options did not work for what I needed.

First option doesn't work since it doesn't allow for me to filter out
unwanted data. (e.g. if I put in a user and a date it will show me all the
users for the date instead of just one)

Second option doesn't work since it filters out null data.


Please look over the above and let me know if the above is a flawed structure.

Just enter your criteria Like [forms]![search]![name] & "*" under each
field you want to check. Put each piece of criteria on a separate row in
the query grid to create an "OR" condition, or on the same line to create an
"AND" condition.

What ten fields are you searching? Are they all name fields? If so, you
have a flawed data structure and you will run into lots of headaches unless
you fix the data structure. If the name may appear in one of several
fields, then you have a one-to-many relationship that should be handled with
a separate related table.

Post back with some details if you need help with the structure of your
table.

--
Rick B

If criteria is null return all
That all I want to do but after trying several ways I can't get any to
work.
Here is what I have tried so far for the criteria

Like [forms]![search]![name] & "*"
<>Which doesn't work since I have 10 fields I want to search and using
this causes null values in some fields to filter out.
Like "*" & [forms]![search]![name] & "*"
<>Same as above
[forms]![search]![name] and ([forms]![Search]![name] is null)
<> this causes the query to response with insufficient memory error

Below is the SQL of my query so you can see how many fields I am dealing
with.

SELECT UpdatedTable.[Who are you?], UpdatedTable.Date, UpdatedTable.[User
ID], UpdatedTable.[Profit Center], UpdatedTable.[Policy Number],
UpdatedTable.[Call Type], UpdatedTable.[Action Taken on Call],
UpdatedTable.[Reason for Disconnect], UpdatedTable.[Problem Description]
FROM UpdatedTable;

 

[Guest]

BTW thank you for your help. forgot to mention that in my last post.

....it's been one of those days...

Just enter your criteria Like [forms]![search]![name] & "*" under each
field you want to check. Put each piece of criteria on a separate row in
the query grid to create an "OR" condition, or on the same line to create an
"AND" condition.

What ten fields are you searching? Are they all name fields? If so, you
have a flawed data structure and you will run into lots of headaches unless
you fix the data structure. If the name may appear in one of several
fields, then you have a one-to-many relationship that should be handled with
a separate related table.

Post back with some details if you need help with the structure of your
table.

--
Rick B

If criteria is null return all
That all I want to do but after trying several ways I can't get any to
work.
Here is what I have tried so far for the criteria

Like [forms]![search]![name] & "*"
<>Which doesn't work since I have 10 fields I want to search and using
this causes null values in some fields to filter out.
Like "*" & [forms]![search]![name] & "*"
<>Same as above
[forms]![search]![name] and ([forms]![Search]![name] is null)
<> this causes the query to response with insufficient memory error

Below is the SQL of my query so you can see how many fields I am dealing
with.

SELECT UpdatedTable.[Who are you?], UpdatedTable.Date, UpdatedTable.[User
ID], UpdatedTable.[Profit Center], UpdatedTable.[Policy Number],
UpdatedTable.[Call Type], UpdatedTable.[Action Taken on Call],
UpdatedTable.[Reason for Disconnect], UpdatedTable.[Problem Description]
FROM UpdatedTable;

 

[Guest]

After alot of searching on the forums and ultimatly reffering to MS Acces
Inside Out I found the solution. Basically I had a form with all the search
fields. And another form that was linked to main table. Clicking the search
button ran the below VB statement.

Private Sub Command24_Click()
Dim varWhere As Variant
If IsNull(Me.Associate) Then
' do nothing
Else
varWhere = "[Who Are You?] = """ & Me.Associate & """"
End If
If IsNull(Me.[Date]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[Date] = #" & Me.Date & "#"
End If
If IsNull(Me.[user id]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[user id] = """ & Me.[user
id] & """"
End If
If IsNull(Me.[profit center]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[profit center] = """ &
Me.[profit center] & """"
End If
If IsNull(Me.[policy number]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[policy number] = """ &
Me.[policy number] & """"
End If
If IsNull(Me.[call type]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[call type] = """ & Me.[call
type] & """"
End If
If IsNull(Me.[action taken on call]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[action taken on call] = """
& Me.[action taken on call] & """"
End If
If IsNull(Me.[reason for disconnect]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[reason for disconnect] = """
& Me.[reason for disconnect] & """"
End If
If IsNull(Me.[start]) Then
' do nothing
Else
varWhere = (varWhere + " And ") & "[hour] = """ & Me.[start] &
""""
End If
If IsNull(Me.Associate) Then
varWhere = Mid(varWhere, 5, 999)
End If
Text = varWhere
DoCmd.OpenForm "Search1", acFormDS, WhereCondition:=varWhere

End Sub

BTW thank you for your help. forgot to mention that in my last post.

...it's been one of those days...

Just enter your criteria Like [forms]![search]![name] & "*" under each
field you want to check. Put each piece of criteria on a separate row in
the query grid to create an "OR" condition, or on the same line to create an
"AND" condition.

What ten fields are you searching? Are they all name fields? If so, you
have a flawed data structure and you will run into lots of headaches unless
you fix the data structure. If the name may appear in one of several
fields, then you have a one-to-many relationship that should be handled with
a separate related table.

Post back with some details if you need help with the structure of your
table.

--
Rick B

If criteria is null return all
That all I want to do but after trying several ways I can't get any to
work.
Here is what I have tried so far for the criteria

Like [forms]![search]![name] & "*"
<>Which doesn't work since I have 10 fields I want to search and using
this causes null values in some fields to filter out.
Like "*" & [forms]![search]![name] & "*"
<>Same as above
[forms]![search]![name] and ([forms]![Search]![name] is null)
<> this causes the query to response with insufficient memory error

Below is the SQL of my query so you can see how many fields I am dealing
with.

SELECT UpdatedTable.[Who are you?], UpdatedTable.Date, UpdatedTable.[User
ID], UpdatedTable.[Profit Center], UpdatedTable.[Policy Number],
UpdatedTable.[Call Type], UpdatedTable.[Action Taken on Call],
UpdatedTable.[Reason for Disconnect], UpdatedTable.[Problem Description]
FROM UpdatedTable;