Run Query usinf Data from a form

D

deodev

I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
 
O

Ofer

What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value
 
D

deodev

I now have an * in each text box but the result I get is for the mrmatter
='0133256'

the iif is not evaluating correctly? once I get this working i will add the
check for null

WHERE IIf((Forms!frmFormQuery.txtmatter.text="*" and
Forms!frmFormQuery.txttkpr.text="*"),FINALMATTEREXCEPTION.mrmatter Like
"*",FINALMATTEREXCEPTION.mrmatter= '0133256')
ORDER BY FINALMATTEREXCEPTION.ldesc, FINALMATTEREXCEPTION.head1;

Ofer said:
What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value

deodev said:
I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
Click to expand...
Click to expand...
 
O

Ofer

Try that, I hope it will work
SELECT *
FROM FINALMATTEREXCEPTION
WHERE (((nz(FINALMATTEREXCEPTION.mbillaty,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txttkpr].[text]),"*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="","*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="*","*",[Forms]![frmFormQuery].[txttkpr].[text]))))
AND ((nz(FINALMATTEREXCEPTION.mrmatter,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txtmatter].[text]),"*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="","*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="*","*",[Forms]![frmFormQuery].[txtmatter].[text])))));

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))
Click to expand...
Click to expand...
Click to expand...

deodev said:
I now have an * in each text box but the result I get is for the mrmatter
='0133256'

the iif is not evaluating correctly? once I get this working i will add the
check for null

WHERE IIf((Forms!frmFormQuery.txtmatter.text="*" and
Forms!frmFormQuery.txttkpr.text="*"),FINALMATTEREXCEPTION.mrmatter Like
"*",FINALMATTEREXCEPTION.mrmatter= '0133256')
ORDER BY FINALMATTEREXCEPTION.ldesc, FINALMATTEREXCEPTION.head1;

Ofer said:
What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value

deodev said:
I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
Click to expand...
Click to expand...
Click to expand...
 
D

deodev

This work much better than my code - however, I get the results for the
matter only when I enter both a mbillaty and a mrmatter ex 0815 and 0133256

I have records for both 0815 and 0133256 however there is no record where
mbillaty =0815 and mrmatter =0133256

I should get zero records.

Thanks a lot for your help





Ofer said:
Try that, I hope it will work
SELECT *
FROM FINALMATTEREXCEPTION
WHERE (((nz(FINALMATTEREXCEPTION.mbillaty,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txttkpr].[text]),"*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="","*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="*","*",[Forms]![frmFormQuery].[txttkpr].[text]))))
AND ((nz(FINALMATTEREXCEPTION.mrmatter,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txtmatter].[text]),"*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="","*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="*","*",[Forms]![frmFormQuery].[txtmatter].[text])))));

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))
Click to expand...
Click to expand...

deodev said:
I now have an * in each text box but the result I get is for the mrmatter
='0133256'

the iif is not evaluating correctly? once I get this working i will add the
check for null

WHERE IIf((Forms!frmFormQuery.txtmatter.text="*" and
Forms!frmFormQuery.txttkpr.text="*"),FINALMATTEREXCEPTION.mrmatter Like
"*",FINALMATTEREXCEPTION.mrmatter= '0133256')
ORDER BY FINALMATTEREXCEPTION.ldesc, FINALMATTEREXCEPTION.head1;

Ofer said:
What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value

:


I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

I would advice you to add another field where the user can decide when he
want to use AND and when to use OR. and then change the string by the user
decision from AND to OR
The Metter you are talking about we should use OR.
But Sometimes you want to use AND

deodev said:
This work much better than my code - however, I get the results for the
matter only when I enter both a mbillaty and a mrmatter ex 0815 and 0133256

I have records for both 0815 and 0133256 however there is no record where
mbillaty =0815 and mrmatter =0133256

I should get zero records.

Thanks a lot for your help





Ofer said:
Try that, I hope it will work
SELECT *
FROM FINALMATTEREXCEPTION
WHERE (((nz(FINALMATTEREXCEPTION.mbillaty,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txttkpr].[text]),"*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="","*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="*","*",[Forms]![frmFormQuery].[txttkpr].[text]))))
AND ((nz(FINALMATTEREXCEPTION.mrmatter,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txtmatter].[text]),"*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="","*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="*","*",[Forms]![frmFormQuery].[txtmatter].[text])))));

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))
Click to expand...

deodev said:
I now have an * in each text box but the result I get is for the mrmatter
='0133256'

the iif is not evaluating correctly? once I get this working i will add the
check for null

WHERE IIf((Forms!frmFormQuery.txtmatter.text="*" and
Forms!frmFormQuery.txttkpr.text="*"),FINALMATTEREXCEPTION.mrmatter Like
"*",FINALMATTEREXCEPTION.mrmatter= '0133256')
ORDER BY FINALMATTEREXCEPTION.ldesc, FINALMATTEREXCEPTION.head1;

:

What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value

:


I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
Click to expand...
Click to expand...
Click to expand...
 
D

deodev

Thank you

Ofer said:
I would advice you to add another field where the user can decide when he
want to use AND and when to use OR. and then change the string by the user
decision from AND to OR
The Metter you are talking about we should use OR.
But Sometimes you want to use AND

deodev said:
This work much better than my code - however, I get the results for the
matter only when I enter both a mbillaty and a mrmatter ex 0815 and 0133256

I have records for both 0815 and 0133256 however there is no record where
mbillaty =0815 and mrmatter =0133256

I should get zero records.

Thanks a lot for your help





Ofer said:
Try that, I hope it will work
SELECT *
FROM FINALMATTEREXCEPTION
WHERE (((nz(FINALMATTEREXCEPTION.mbillaty,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txttkpr].[text]),"*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="","*",IIf([Forms]![frmFormQuery].[txttkpr].[text]="*","*",[Forms]![frmFormQuery].[txttkpr].[text]))))
AND ((nz(FINALMATTEREXCEPTION.mrmatter,"")) Like
IIf(IsNull([Forms]![frmFormQuery].[txtmatter].[text]),"*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="","*",IIf([Forms]![frmFormQuery].[txtmatter].[text]="*","*",[Forms]![frmFormQuery].[txtmatter].[text])))));

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))

:

I now have an * in each text box but the result I get is for the mrmatter
='0133256'

the iif is not evaluating correctly? once I get this working i will add the
check for null

WHERE IIf((Forms!frmFormQuery.txtmatter.text="*" and
Forms!frmFormQuery.txttkpr.text="*"),FINALMATTEREXCEPTION.mrmatter Like
"*",FINALMATTEREXCEPTION.mrmatter= '0133256')
ORDER BY FINALMATTEREXCEPTION.ldesc, FINALMATTEREXCEPTION.head1;

:

What the problem?
You get no records, you get all the records?

If you have to fields that you want to filter the query with then you should
use AND instead of or.
With the or, if you wont enter any value in one of the fields then the query
will return all records.
Also in the iif statement you didnt check id the field value is null, in
that case the query wont return any value

:


I have two text boxes on a form - txttkpr and txtmatter

I woul like a query to use the data from these two text boxes as criteria.

the query should return data based on any combination of the data in the
text boxes

that is
txttkpr txtmatter
* *
1025 *
* 02356
1025 01256

I am having a proble with the where statement

where (((FINALMATTEREXCEPTION.mbillaty) Like
(IIf(([Forms]![frmFormQuery].[txttkpr].[text])="*","*",IIf(([Forms]![frmFormQuery].[txttkpr].[text])="","*",([Forms]![frmFormQuery].[txttkpr].[text]))))))
OR (((FINALMATTEREXCEPTION.mrmatter) Like
(IIf(([Forms]![frmFormQuery].[txtmatter].[text])="","*",IIf(([Forms]![frmFormQuery].[txtmatter].[text])="*","*",([Forms]![frmFormQuery].[txtmatter].[text]))))))



thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Search using a combo box and then creating a report. 4
Multiple Parameter Crosstab query filter by form 1
Query using text from form or anything if empty 2
If Statement with "*" in Query 2
Between inside IIf on a date query 9
iif function syntax problem 5
IIF Expression returning Zero Records in Query 2
Parameter Criteria from a forms Text box - 2003 - not working 5

Top