Cannot get a Wild Card Query to show Records.

[Guest]

Brain Dead.... Ok.. my objective : if the form control field (text box) is
blank show all records, otherwise show the specific record that matches the
value in the text box. I need this for three fields on the same form.

I have a master form with six text boxes to query data from. Three fields(
1,2 and 3) (combo boxes) are easy.. Match the form control exactly) The other
three form controls 4,5, and 6) text boxes) need to be able to be blank or
have data.

I want to be able to construct a query so that If any one of form controls
4,5,6 is blank then return all records for that field and continue to filter
from the other fields.

What I Have Is: In the query design window for instance in field 4

IIF([Forms]![MasterForm]![TxtBx4] Is Null, "*",[Forms]![MasterForm]![TxtBx4])
I repeated this for fields 5 and 6 changing the field names respectively.

I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"

Like*[Forms]![MasterForm]![TxtBx4]

[Forms]![MasterForm]![TxtBx4]*
and a host of others.

When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the statement
for a known value and run it I do get records matching the known value, so I
know that it recognizes when the form control field is blank.

Pulled all of my hair out and my dog is getting nervous.

Help

 

[Rick B]

Like [Forms]![MasterForm]![TxtBx4] & "*"

 

[Guest]

This will not return any records. For Me. The query will only return
records when I enter known values into the text boxes. I put the suggested
syntax in the query and It will only display data if ALL three text boxes
have known values in them. None can be left blank.

Like [Forms]![MasterForm]![TxtBx4] & "*"

Brain Dead.... Ok.. my objective : if the form control field (text box) is
blank show all records, otherwise show the specific record that matches the
value in the text box. I need this for three fields on the same form.

I have a master form with six text boxes to query data from. Three fields(
1,2 and 3) (combo boxes) are easy.. Match the form control exactly) The other
three form controls 4,5, and 6) text boxes) need to be able to be blank or
have data.

I want to be able to construct a query so that If any one of form controls
4,5,6 is blank then return all records for that field and continue to filter
from the other fields.

What I Have Is: In the query design window for instance in field 4

IIF([Forms]![MasterForm]![TxtBx4] Is Null, "*",[Forms]![MasterForm]![TxtBx4])
I repeated this for fields 5 and 6 changing the field names respectively.

I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"

Like*[Forms]![MasterForm]![TxtBx4]

[Forms]![MasterForm]![TxtBx4]*
and a host of others.

When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the statement
for a known value and run it I do get records matching the known value, so I
know that it recognizes when the form control field is blank.

Pulled all of my hair out and my dog is getting nervous.

Help

 

[Rick B]

Put the criteria on separate rows of the query. This creates an OR
statement.

This will not return any records. For Me. The query will only return
records when I enter known values into the text boxes. I put the suggested
syntax in the query and It will only display data if ALL three text boxes
have known values in them. None can be left blank.

Like [Forms]![MasterForm]![TxtBx4] & "*"

Brain Dead.... Ok.. my objective : if the form control field (text

box)
is

blank show all records, otherwise show the specific record that

matches
the

value in the text box. I need this for three fields on the same form.

I have a master form with six text boxes to query data from. Three fields(
1,2 and 3) (combo boxes) are easy.. Match the form control exactly)

The
other

three form controls 4,5, and 6) text boxes) need to be able to be

blank
or

have data.

I want to be able to construct a query so that If any one of form controls
4,5,6 is blank then return all records for that field and continue to filter
from the other fields.

What I Have Is: In the query design window for instance in field 4

IIF([Forms]![MasterForm]![TxtBx4] Is Null, "*",[Forms]![MasterForm]![TxtBx4])
I repeated this for fields 5 and 6 changing the field names respectively.

I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"

Like*[Forms]![MasterForm]![TxtBx4]

[Forms]![MasterForm]![TxtBx4]*
and a host of others.

When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the statement
for a known value and run it I do get records matching the known

value, so
I

know that it recognizes when the form control field is blank.

Pulled all of my hair out and my dog is getting nervous.

Help

 

[Guest]

Doesn't do anything. I've even booted pc and restarted access. I can get
the wildcard to return values if not part of any other criteria. But that's
it. If I make it part of an expression no records return, Using Office
Developer XP. I've done this in other queries in the same database and no
problems. It will not even return records for Like "*" if used in
conjunction with an expression. I DON'T KNOW IF THIS IS A PROBLEM, BUT ..
once I build the expression the system adds the name of the table and field
before the expression but doesn't do it with any other expressions.:

I type in: Like [Forms]![MasterForm]![TxtBx4] & "*"

It constructs (

.[field]) = Like [Forms]![MasterForm]![TxtBx4] & "*"

PS.. I really appreciate your help!

thx

Put the criteria on separate rows of the query. This creates an OR
statement.

This will not return any records. For Me. The query will only return
records when I enter known values into the text boxes. I put the suggested
syntax in the query and It will only display data if ALL three text boxes
have known values in them. None can be left blank.

Like [Forms]![MasterForm]![TxtBx4] & "*"




Brain Dead.... Ok.. my objective : if the form control field (text box)
is
blank show all records, otherwise show the specific record that matches
the
value in the text box. I need this for three fields on the same form.

I have a master form with six text boxes to query data from. Three
fields(
1,2 and 3) (combo boxes) are easy.. Match the form control exactly) The
other
three form controls 4,5, and 6) text boxes) need to be able to be blank
or
have data.

I want to be able to construct a query so that If any one of form controls
4,5,6 is blank then return all records for that field and continue to
filter
from the other fields.

What I Have Is: In the query design window for instance in field 4

IIF([Forms]![MasterForm]![TxtBx4] Is Null,
"*",[Forms]![MasterForm]![TxtBx4])
I repeated this for fields 5 and 6 changing the field names respectively.

I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"

Like*[Forms]![MasterForm]![TxtBx4]

[Forms]![MasterForm]![TxtBx4]*
and a host of others.

When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the
statement
for a known value and run it I do get records matching the known value, so
I
know that it recognizes when the form control field is blank.

Pulled all of my hair out and my dog is getting nervous.

Help

 

[Rick B]

I'll have to say that I don't know. Hopefully an MVP will step in.

Good Luck

Doesn't do anything. I've even booted pc and restarted access. I can get
the wildcard to return values if not part of any other criteria. But that's
it. If I make it part of an expression no records return, Using Office
Developer XP. I've done this in other queries in the same database and no
problems. It will not even return records for Like "*" if used in
conjunction with an expression. I DON'T KNOW IF THIS IS A PROBLEM, BUT ...
once I build the expression the system adds the name of the table and field
before the expression but doesn't do it with any other expressions.:

I type in: Like [Forms]![MasterForm]![TxtBx4] & "*"

It constructs (

.[field]) = Like [Forms]![MasterForm]![TxtBx4] & "*"

PS.. I really appreciate your help!

thx

Put the criteria on separate rows of the query. This creates an OR
statement.

This will not return any records. For Me. The query will only return
records when I enter known values into the text boxes. I put the suggested
syntax in the query and It will only display data if ALL three text boxes
have known values in them. None can be left blank.

:

Like [Forms]![MasterForm]![TxtBx4] & "*"




Brain Dead.... Ok.. my objective : if the form control field

(text
box)

is
blank show all records, otherwise show the specific record that matches
the
value in the text box. I need this for three fields on the same form.

I have a master form with six text boxes to query data from. Three
fields(
1,2 and 3) (combo boxes) are easy.. Match the form control

exactly)
The

other
three form controls 4,5, and 6) text boxes) need to be able to

be
blank

or
have data.

I want to be able to construct a query so that If any one of form controls
4,5,6 is blank then return all records for that field and continue to
filter
from the other fields.

What I Have Is: In the query design window for instance in field 4

IIF([Forms]![MasterForm]![TxtBx4] Is Null,
"*",[Forms]![MasterForm]![TxtBx4])
I repeated this for fields 5 and 6 changing the field names respectively.

I have also changed the syntax to [Forms]![MasterForm]![TxtBx4] &"*"

Like*[Forms]![MasterForm]![TxtBx4]

[Forms]![MasterForm]![TxtBx4]*
and a host of others.

When I substitute the "[Forms]![MasterForm]![TxtBx4]" part of the
statement
for a known value and run it I do get records matching the known value, so
I
know that it recognizes when the form control field is blank.

Pulled all of my hair out and my dog is getting nervous.

Help

 

[Van T. Dinh]

It sounds to me that you want criteria something like:

WHERE ...
( ( Field4 = [Forms]![MasterForm]![TxtBx4] ) OR
( [Forms]![MasterForm]![TxtBx4] Is Null ) ) AND
( ( Field5 = [Forms]![MasterForm]![TxtBx5] ) OR
( [Forms]![MasterForm]![TxtBx5] Is Null ) ) AND
( ( Field6 = [Forms]![MasterForm]![TxtBx6] ) OR
( [Forms]![MasterForm]![TxtBx6] Is Null ) )