Getting Access to stop splitting my expression. Thank you!

jkdalex

New Member
Joined
Jul 24, 2007
Messages
11
Hi I am doing this in my search criteria of a query in the Comments field of a table based on information passed from a form "frm_search"

Code:
Like "*" & [Forms]![frm_Search]![Comments] & "*" or [Forms]![frm_Search]![Comments] is null

which works fine except for the fact that access splits this into two rows in the query design table. one for the first statement and the second for the or statement.

This is becoming a problem for me because i use more than one of the code above so if I use it twice, I get four rows and three times I get eight rows... you get the idea. That forces me to rewrite the other search criteria 7 times.

Is there any way to rewrite the expression above so access doesn't try to split the expression into two rows?

Alternatively the expression I used before was
Code:
iif(isnull([Forms]![frm_Search]![Comments]),[Comments],[Forms]![frm_Search]![Comments] )

The problem with that is it doesn't show entries with empty fields. I would like to be able to see those entries with empty fields which is why i had to resort to the first method. Answering either of these problems will solve everything for me. thanks in advance!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Based on what I found about the Nx() function, it might work. See the help topice link below:

http://msdn2.microsoft.com/en-us/library/aa172237(office.11).aspx

Instead of this:
Code:
iif(isnull([Forms]![frm_Search]![Comments]),[Comments],[Forms]![frm_Search]![Comments] )

Try this:
Code:
IIf(Nz([Forms}![frm_Search]![Comments]) like " ",  " ", Like "*" & [Forms]![frm_Search]![Comments] & "*")


Logic: IF the Nz() function returns a zero-length string (like " ") because the Comments form field was null, then place a zero length string in the criteria (two quotes with a space in between, I think). ELSE (meaning the Comment field on the form had info) place the Comment form field text with a star on either end.

I haven't thoroughly tested this, but am running short on time. Hope this helps!
Max[/code]
 

jkdalex

New Member
Joined
Jul 24, 2007
Messages
11
Hi Max,

thanks for your quick reply. I tried it out and unfortunately it doesn't work. It's really strange the way the criteria box acts but it doesn't seem to like my IIf statements :(

What I don't understand is why they made it so hard to show partial records. There has got to be a very simple solution to this!
 

jkdalex

New Member
Joined
Jul 24, 2007
Messages
11
What I did the first time is listed here -

http://support.microsoft.com/kb/304428/

But like I said, it forces your expression into two rows and two columns.

One for [comments] criteria: Like [Forms]![frm_Search]![Amount]
and one for [Forms]![frm_Search]![Amount] criteria: Is Null

i.e. try pasting
Code:
"Like [Forms]![frm_Search]![Date] Or [Forms]![frm_Search]![Date] is null"
into a search criteria, save the query and reopen it to see what happens.
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200

ADVERTISEMENT

Ok, I've tested it...try this

In your comment field criteria

row1) Like "*" & [Forms]![Form1]![Field1] & "*"
row2) [Forms]![Form1]![Field1] Is Null

change the [Form1] to your form name and [Field1] to your form field name.

I read where the link you provided said to keep it all on one line of the criteria row, but that just wasn't working in this case.

Good luck!
Max
 

jkdalex

New Member
Joined
Jul 24, 2007
Messages
11
Ok, I've tested it...try this

In your comment field criteria

row1) Like "*" & [Forms]![Form1]![Field1] & "*"
row2) [Forms]![Form1]![Field1] Is Null

change the [Form1] to your form name and [Field1] to your form field name.

I read where the link you provided said to keep it all on one line of the criteria row, but that just wasn't working in this case.

Good luck!
Max

Yes but that's similar what access automatically does when I enter the previous code method and what i'm trying to avoid. That works fine with maybe 1-2 criteria (you'll end up with 4 rows max) but the problem is that I input this criteria to 8 fields, and i'll need 2^8 to have all the right combinations. It's very frustrating :(. Is there any way to get it all on one line?
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Sorry about that...forgot that was the main source of concern :oops:

I see what you mean about it automatically splitting it up when you save and reopen. I don't know how else to help you with that! Anybody else have an idea?

An alternate idea:

Maybe this is too far out in left field, but could you just add all the comment fields together into one big text field to use for testing against?

Exp1: Comment1 & Comment2 & Comment3...& Comment8

Then test using the criteria on two rows on this field? You could hide this Exp1 field from the user's view!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,334
Messages
5,769,473
Members
425,553
Latest member
chrisc297

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top