Query with info from a form

G

Guest

I have an form setup that you can type information to use as a search. For
example to see all the contracts for a specific Assigned Resource you can
just type in the Assigned Resourse and click search. However, my problem is
when one of the search fields is left blank. I do not get the results I
need. I want the query to use the information entered into the form but alow
anything in the fields that are left null.
I have 20 feilds you can search by but I only included two because of length.
My first try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main
Table].CN_PN)=If([Forms]![Search]![CN_PN]<>"",([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*","")) AND (([JFWorkflow
Main Table].[Assigned Resource])=If([Forms]![Search]![Assigned
Resource]<>"",([JFWorkflow Main Table].[Assigned Resource]) Like
[Forms]![Search]![Assigned Resource] & "*","")));
But I do not get any results if a field is left blank. I assume I need
something else in the "false" field of the "if" statement.
Here is my other try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*"
Or ([JFWorkflow Main Table].CN_PN) Is Null) AND (([JFWorkflow Main
Table].[Assigned Resource]) Like [Forms]![Search]![Assigned Resource] & "*"
Or ([JFWorkflow Main Table].[Assigned Resource]) Is Null));
But if you search my Assigned Resource you will also get everything not
Assigned
Thanks
 
O

OfficeDev18 via AccessMonster.com

Referring to your first try: I assume that when you use "If(..." you are
using the IIF() function, namely "IIf(...". I f this is not the case, try
changing If to IIf.

Besides that, instead of testing for blanks (""), try using either the IsNull
() or Nz() functions. Normally, blank fields are null, not zero-length
strings.

HTH
I have an form setup that you can type information to use as a search. For
example to see all the contracts for a specific Assigned Resource you can
just type in the Assigned Resourse and click search. However, my problem is
when one of the search fields is left blank. I do not get the results I
need. I want the query to use the information entered into the form but alow
anything in the fields that are left null.
I have 20 feilds you can search by but I only included two because of length.
My first try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main
Table].CN_PN)=If([Forms]![Search]![CN_PN]<>"",([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*","")) AND (([JFWorkflow
Main Table].[Assigned Resource])=If([Forms]![Search]![Assigned
Resource]<>"",([JFWorkflow Main Table].[Assigned Resource]) Like
[Forms]![Search]![Assigned Resource] & "*","")));
But I do not get any results if a field is left blank. I assume I need
something else in the "false" field of the "if" statement.
Here is my other try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*"
Or ([JFWorkflow Main Table].CN_PN) Is Null) AND (([JFWorkflow Main
Table].[Assigned Resource]) Like [Forms]![Search]![Assigned Resource] & "*"
Or ([JFWorkflow Main Table].[Assigned Resource]) Is Null));
But if you search my Assigned Resource you will also get everything not
Assigned
Thanks
Click to expand...
 
G

Guest

Sorry, yes I am using IIF()
I tried Is Not Null and <> Nz but I did not recieve any results
I only want to use that field for search criteria if it is not null so I am
not sure if Nz would work. Would it?
Anyway the IIf([Forms]![Search]![CN_PN] Is Not Null,([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*"))
Didn't work either. Any other ideas I can try?
Thanks again.

OfficeDev18 via AccessMonster.com said:
Referring to your first try: I assume that when you use "If(..." you are
using the IIF() function, namely "IIf(...". I f this is not the case, try
changing If to IIf.

Besides that, instead of testing for blanks (""), try using either the IsNull
() or Nz() functions. Normally, blank fields are null, not zero-length
strings.

HTH
I have an form setup that you can type information to use as a search. For
example to see all the contracts for a specific Assigned Resource you can
just type in the Assigned Resourse and click search. However, my problem is
when one of the search fields is left blank. I do not get the results I
need. I want the query to use the information entered into the form but alow
anything in the fields that are left null.
I have 20 feilds you can search by but I only included two because of length.
My first try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main
Table].CN_PN)=If([Forms]![Search]![CN_PN]<>"",([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*","")) AND (([JFWorkflow
Main Table].[Assigned Resource])=If([Forms]![Search]![Assigned
Resource]<>"",([JFWorkflow Main Table].[Assigned Resource]) Like
[Forms]![Search]![Assigned Resource] & "*","")));
But I do not get any results if a field is left blank. I assume I need
something else in the "false" field of the "if" statement.
Here is my other try:
SELECT [JFWorkflow Main Table].CN_PN, [JFWorkflow Main Table].[Assigned
Resource]
FROM [JFWorkflow Main Table]
WHERE ((([JFWorkflow Main Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*"
Or ([JFWorkflow Main Table].CN_PN) Is Null) AND (([JFWorkflow Main
Table].[Assigned Resource]) Like [Forms]![Search]![Assigned Resource] & "*"
Or ([JFWorkflow Main Table].[Assigned Resource]) Is Null));
But if you search my Assigned Resource you will also get everything not
Assigned
Thanks
Click to expand...
Click to expand...
 
J

John Vinson

Sorry, yes I am using IIF()
I tried Is Not Null and <> Nz but I did not recieve any results
I only want to use that field for search criteria if it is not null so I am
not sure if Nz would work. Would it?
Anyway the IIf([Forms]![Search]![CN_PN] Is Not Null,([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*"))
Didn't work either. Any other ideas I can try?
Click to expand...

You cannot pass an *OPERATOR* such as LIKE using a function - only the
actual value to search.

Instead, use a criterion

LIKE [Forms]![Search]![CN_PN] & "*" OR [Forms]![Search]![CN_PN] IS
NULL

John W. Vinson[MVP]
 
G

Guest

That will also give me blank results.
Example:
If I type John in the Assigned Resource field and click search it is giong
to return everything assigned to John as well as everything that has not been
assigned. I only want what has been assigned to John.
I want the query to only use the field if it is not null. If it is null
when the query is ran then that field can contain anything.
Thanks again.

John Vinson said:
Sorry, yes I am using IIF()
I tried Is Not Null and <> Nz but I did not recieve any results
I only want to use that field for search criteria if it is not null so I am
not sure if Nz would work. Would it?
Anyway the IIf([Forms]![Search]![CN_PN] Is Not Null,([JFWorkflow Main
Table].[CN_PN]) Like [Forms]![Search]![CN_PN] & "*"))
Didn't work either. Any other ideas I can try?
Click to expand...

You cannot pass an *OPERATOR* such as LIKE using a function - only the
actual value to search.

Instead, use a criterion

LIKE [Forms]![Search]![CN_PN] & "*" OR [Forms]![Search]![CN_PN] IS
NULL

John W. Vinson[MVP]
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

Search critieria from Form 11
Access Sub form opens before the main form in MS Access 0
How do this tricky query? 4
iif exists - too complex? 1
Aggregate Function Error 0
Impossible Query.. 9
Access Passing data from a form to another form! 0
Invalid Argument 2

Top