complex criteria 1

[jflo]

Hi,

I use a form to call up a report. I use some fields in the form as criteria to filter some data in the queries that feed my sub reports. What I want to do is : if a field (lets say agency) in the form is blank, then return all lines and if it's not blank, return agency.value. O.K. I'm not the most experienced but I pretty much know my way around. I use a IFF statement as my criteria that looks like this.

IIF([Forms]![SelectionForm]![agency]<>&quot;&quot;;[Forms]![SelectionForm]![agency]; ___ )

It works when you have a selection in your form for agency but otherwise it doesn't.

As the last part of my IIF statement, I've tried *, "*", [*], >= " ", Like "[A-z]*", or just about anything else you could dream of.

I even tried
>=IIF([Forms]![SelectionForm]![agency]<>&quot;&quot;;[Forms]![SelectionForm]![agency]<>&quot;&quot;;A) or something I don't recall and this would work if there's nothing in the field but wouldn't ( and I understand why! ) when there was a selection in agency, returning not only the selection but anything greater.

What am I doing wrong, is there a better way (not only in the criteria)?

Thanks

Jean-François, Montréal

 

[ghubbell]

Salut Jean-François, nous sommes très proche!
(Hi Jean-François we're very close!)

You might try something like this:

>=IIF(IsNull([Forms]![SelectionForm]![agency]),&quot;A&quot;,[Forms]![SelectionForm]![agency])?

Give it a test?...




Gord
ghubbell@total.net

 

[ghubbell]

Aussi! (Also!) I thought of a nice way to eliminate your problem complètement! In the after-update event of your Agency field on your form ( I will assume you start the report by clicking a &quot;report button...) add this:

If Me.Agency = &quot;&quot; Or IsNull(Me.Agency) then
Me![NameOfButton].Enabled = False
Else
Me![NameOfButton].Enabled = True
end if

Just make sure that the button is not the next in line in your tab order after your Agency field.

Now your criteria can just be:

>=[Forms]![SelectionForm]![agency]

Voila! Gord
ghubbell@total.net

 

[jflo]

I tried something like this.

It gives me valid results if the field in the selection form is empty but for exemple, if you have (Montréal , New-York and Paris) as agency, then if you select Paris in the form, it only return Paris which is good. But if you select Montréal in the form, it returns all Montréal's, all New-York's and all Paris records.

Do you see where I'm getting at?

 

[ghubbell]

Yes I think I do and as your last message arrived here at the same moment as my last message, I think my last message will be a good solution. A slight change to your criteria though:

[Forms]![SelectionForm]![agency]

Try... Gord
ghubbell@total.net