Filter query from Form

[Erik Svensson]

Hi
I am currently trying to build a search function in a form where I have 8
different criterias (combo boxes) that I want to be able to filter one table
with. I have tried to use the function
IIf([Forms]![Drawing search form]![Drawing]="",Like "**",[Forms]![Drawing
search form]![Drawing])

which returns the correct drawing when I post a drawing in the particular
form but it does not return the complete table when I don't post anything at
all in it. If I use the Like "**" function by itself it returns the complete
table, so what am I doing wrong?

//Erik Svensson

 

[Stefan Hoffmann]

hi Erik,

If I use the Like "**" function by itself it returns the complete
table, so what am I doing wrong?

Using it directly, will result in the following correct condition:

WHERE FieldName LIKE '*'

I am currently trying to build a search function in a form where I have 8
different criterias (combo boxes) that I want to be able to filter one table
with. I have tried to use the function
IIf([Forms]![Drawing search form]![Drawing]="",Like "**",[Forms]![Drawing
search form]![Drawing])

You're trying to built it dynamically, but you have to consider two cases:

1) Exact search with a value from your combobox. You must build a
criteria like that:

WHERE FieldName = 'exact value'

2) partial match with '*':

WHERE FieldName LIKE 'part*'

Where do you use your Iif? When you using it in code, please give us a
complete example.


mfG
--> stefan <--

 

[Erik Svensson]

I am using this statement in the query where I used the build function to
construct the code I posted. I want the query to look at the content of one
box in the form and if nothing is stated in the box that particular column in
the query should not filter anything. But if something is stated in the box
the query should filter that column (i.e. the drawing nr column) to present
this particular drawing.

In the search form I have created 8 different combo boxes which every one of
them have a drop down list that automatically sort out the distinct content
of the particular column in the table. I want to be able to search in my
table by these 8 factors and if i don't post anything in one or several of
them (which will be the usual case) I want it to filter the table with the
data that are posted in the other boxes.

//Erik Svensson


--
Student
Chalmers University of Technology, Gothenburg

hi Erik,

If I use the Like "**" function by itself it returns the complete
table, so what am I doing wrong?

Using it directly, will result in the following correct condition:

WHERE FieldName LIKE '*'

I am currently trying to build a search function in a form where I have 8
different criterias (combo boxes) that I want to be able to filter one table
with. I have tried to use the function
IIf([Forms]![Drawing search form]![Drawing]="",Like "**",[Forms]![Drawing
search form]![Drawing])

You're trying to built it dynamically, but you have to consider two cases:

1) Exact search with a value from your combobox. You must build a
criteria like that:

WHERE FieldName = 'exact value'

2) partial match with '*':

WHERE FieldName LIKE 'part*'

Where do you use your Iif? When you using it in code, please give us a
complete example.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Erik,

I am using this statement in the query where I [...]

My fault. I meant, send the piece of code, as far as you have one.

mfG
--> stefan <--

 

[Erik Svensson]

I have only done two of the eight query statements I need but this will give
you a picture of what I am doing.


SELECT DRAWINGS.Drawing, DRAWINGS.CAD_File, DRAWINGS.[No of drawings],
DRAWINGS.[Panel type], DRAWINGS.[STEEB dwg no], DRAWINGS.[Draw by],
DRAWINGS.[Drawing date], DRAWINGS.MUNC, DRAWINGS.HP, DRAWINGS.Volt,
DRAWINGS.Type, DRAWINGS.Phase, DRAWINGS.[Enclosure size],
DRAWINGS.Type_of_Drawing1, DRAWINGS.Type_of_Drawing2,
DRAWINGS.Type_of_Drawing3, DRAWINGS.Description, DRAWINGS.D1,
DRAWINGS.Enclosure, DRAWINGS.Logic, DRAWINGS.D2, DRAWINGS.[Power train],
DRAWINGS.[Gen Rec], DRAWINGS.[Junction box], DRAWINGS.[Rev 1], DRAWINGS.[Rev
2], DRAWINGS.[Rev 3], DRAWINGS.[Rev 4], DRAWINGS.[Rev 5], DRAWINGS.[Rev 6],
DRAWINGS.[Rev 7], DRAWINGS.[Rev 8], DRAWINGS.[Rev 9], DRAWINGS.[Rev 10],
DRAWINGS.[Rev 11], DRAWINGS.[Rev 12], DRAWINGS.[Rev 13], DRAWINGS.[Rev 14],
DRAWINGS.[Rev 15], DRAWINGS.[Rev 16], DRAWINGS.[Rev 17], DRAWINGS.[Rev 18],
DRAWINGS.[Rev 19], DRAWINGS.[Rev 20], DRAWINGS.[Rev 21], DRAWINGS.[Rev 22],
DRAWINGS.[Rev 23], DRAWINGS.[Rev 24], DRAWINGS.[Rev 25], DRAWINGS.[Rev 26],
DRAWINGS.[Rev 27], DRAWINGS.[Rev 28], DRAWINGS.[Rev 29], DRAWINGS.[Rev 30]
FROM DRAWINGS
WHERE (((DRAWINGS.Drawing)=IIf([Forms]![Drawing search
form]![Drawing]="","**",[Forms]![Drawing search form]![Drawing])) AND
((DRAWINGS.[Enclosure size])=IIf([Forms]![Drawing search form]![Enclosure
size]="","**",[Forms]![Drawing search form]![Enclosure size])));


//Erik

--
Student
Chalmers University of Technology, Gothenburg

hi Erik,

I am using this statement in the query where I [...]

My fault. I meant, send the piece of code, as far as you have one.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Erik,

I have only done two of the eight query statements I need but this will give
you a picture of what I am doing.

Ok, that is what i wanted.

SELECT DRAWINGS.Drawing, DRAWINGS.CAD_File, DRAWINGS.[No of drawings]
FROM DRAWINGS
WHERE (((DRAWINGS.Drawing)=IIf([Forms]![Drawing search
form]![Drawing]="","**",[Forms]![Drawing search form]![Drawing])) AND
((DRAWINGS.[Enclosure size])=IIf([Forms]![Drawing search form]![Enclosure
size]="","**",[Forms]![Drawing search form]![Enclosure size])));

A simple coondition:

WHERE
Drawing = IIf([Forms]![Drawing search form]![Drawing]="",
Drawing,
[Forms]![Drawing search form]![Drawing])

You need to get a condition which is always true, when your combo is
empty. So comparing the field with itself should do it.


mfG
--> stefan <--

 

[Erik Svensson]

I tried that and when I did it like you said it wanted to change drawing to
"drawing". I also tried
IIf([Forms]![Drawing search
form]![Drawing]="",[DRAWINGS]![Drawing],[Forms]![Drawing search
form]![Drawing])

and

IIf([Forms]![Drawing search form]![Drawing]="",[Drawing],[Forms]![Drawing
search form]![Drawing])

and neither of them worked. I still get a blank result from the query when I
have a blank field in the form.

//Erik

--
Student
Chalmers University of Technology, Gothenburg

hi Erik,

I have only done two of the eight query statements I need but this will give
you a picture of what I am doing.

Ok, that is what i wanted.

SELECT DRAWINGS.Drawing, DRAWINGS.CAD_File, DRAWINGS.[No of drawings]
FROM DRAWINGS
WHERE (((DRAWINGS.Drawing)=IIf([Forms]![Drawing search
form]![Drawing]="","**",[Forms]![Drawing search form]![Drawing])) AND
((DRAWINGS.[Enclosure size])=IIf([Forms]![Drawing search form]![Enclosure
size]="","**",[Forms]![Drawing search form]![Enclosure size])));

A simple coondition:

WHERE
Drawing = IIf([Forms]![Drawing search form]![Drawing]="",
Drawing,
[Forms]![Drawing search form]![Drawing])

You need to get a condition which is always true, when your combo is
empty. So comparing the field with itself should do it.


mfG
--> stefan <--

 

[Erik Svensson]

To get around the problem I tried to use the function Like instead:

Like "*5*"

then all numbers that contain a 5 comes up but when I use the

Like "*[Forms![Drawing search form]![Drawing]*"

It won't return anything at all, even though I post a 5 in that field in the
form. Isn't this procedure possible to do in Access?

//Erik


--
Student
Chalmers University of Technology, Gothenburg

I tried that and when I did it like you said it wanted to change drawing to
"drawing". I also tried
IIf([Forms]![Drawing search
form]![Drawing]="",[DRAWINGS]![Drawing],[Forms]![Drawing search
form]![Drawing])

and

IIf([Forms]![Drawing search form]![Drawing]="",[Drawing],[Forms]![Drawing
search form]![Drawing])

and neither of them worked. I still get a blank result from the query when I
have a blank field in the form.

//Erik

--
Student
Chalmers University of Technology, Gothenburg

hi Erik,

I have only done two of the eight query statements I need but this will give
you a picture of what I am doing.

Ok, that is what i wanted.

SELECT DRAWINGS.Drawing, DRAWINGS.CAD_File, DRAWINGS.[No of drawings]
FROM DRAWINGS
WHERE (((DRAWINGS.Drawing)=IIf([Forms]![Drawing search
form]![Drawing]="","**",[Forms]![Drawing search form]![Drawing])) AND
((DRAWINGS.[Enclosure size])=IIf([Forms]![Drawing search form]![Enclosure
size]="","**",[Forms]![Drawing search form]![Enclosure size])));

A simple coondition:

WHERE
Drawing = IIf([Forms]![Drawing search form]![Drawing]="",
Drawing,
[Forms]![Drawing search form]![Drawing])

You need to get a condition which is always true, when your combo is
empty. So comparing the field with itself should do it.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Erik,

To get around the problem I tried to use the function Like instead:
Like "*5*"
then all numbers that contain a 5 comes up but when I use the
Like "*[Forms![Drawing search form]![Drawing]*"

The reference to your control will not be evaluated, because it is
inside a valid string for the LIKE operator.

Use

LIKE "*" & [Forms![Drawing search form]![Drawing] & "*"

instead.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Erik,

I tried that and when I did it like you said it wanted to change drawing to
"drawing". I also tried
IIf([Forms]![Drawing search
form]![Drawing]="",[DRAWINGS]![Drawing],[Forms]![Drawing search
form]![Drawing])

This can't work, because [DRAWINGS]![Drawing] is not a vaild field name
as [DRAWINGS].[Drawing] is.

IIf([Forms]![Drawing search form]![Drawing]="",[Drawing],[Forms]![Drawing
search form]![Drawing])

and neither of them worked. I still get a blank result from the query when I
have a blank field in the form.

Then the check condition must be modifed:

IIf(
Len(Trim(Nz([Forms]![Drawing search form]![Drawing], ""))) = 0,
[Drawing],
[Forms]![Drawing search form]![Drawing]
)



mfG
--> stefan <--