Leaving parameter entries blank (1 Viewer)

Darrell

Registered User.
Local time
Today, 20:20
Joined
Feb 1, 2001
Messages
196
I have a form which inputs the parameters of a query such as Start Date, Finish Date, Shift Number, Machine Number etc...

What I would like to know is, is there a way to have all of the data selected from a query if one or more of these parameters are left blank.

ie, if I selected a Shift Number and Machine Number and left the date fields blank, could I get all of the records relevant to those parameters regardless of the date?
 

directormac

Occasional Presence
Local time
Today, 20:20
Joined
Oct 24, 2001
Messages
245
Yup. In your query's Criteria: row, set the control where the value to search for will be found, say [Forms]![Finder]!MachineNumber. In the Or: row, type that same control name and add "Is Null" so that they column evaluates to true in either of the above cases. Do that for all possible searched fields.
 

Darrell

Registered User.
Local time
Today, 20:20
Joined
Feb 1, 2001
Messages
196
Thanks but that doesn't seem to quite work.

Adding Is Null to the 'or' Date criteria meant that I got all of the records even though I had selected no dates but had selected a particular Shift Number

When I added the Is Null to the Shift Number also, it returned no records at all when I tried with the same parameters.

Does anyone know if M$ have a Help file on this?
 

Darrell

Registered User.
Local time
Today, 20:20
Joined
Feb 1, 2001
Messages
196
So I would end up with the original parameter, said parameter with Is Null and then another with Not Is Null??

No offence, but I can't quite see how that would work.


[This message has been edited by Darrell (edited 10-25-2001).]
 

directormac

Occasional Presence
Local time
Today, 20:20
Joined
Oct 24, 2001
Messages
245
Hmmm... ok, I don't know WHY this works, but it does, at least on my system. Assuming you have three fields and you want to be able to search any one of them. Table has 4 fields:

Key: PK autonum
Number: Long Int
Date: Gen Date
Price: Gen Currency

Create an unbound form, with three unbound text boxes named Number, Price, and Date, with formats set correctly. Save this as "Criteria"

Create a query in design view, based on Table. Drag all four fields down. In the Criteria: row of the field Number, type the following:

IIf(IsNull([Forms]![Criteria]![Number]),Null,[Forms]![Criteria]![Number])

Now in the Or: row of the field Date (3rd column), type the same thing but replace all instances of [Number] with [Date].

In the column for field Price (#4), in the NEXT ROW DOWN (which doesn't have a label), type the same thing but replace [Number] with [Price] this time.

When you open the query, you get your list. 2 important notes: (1) if all three boxes on the Criteria form are blank, you get no records returned. (2) this is an OR query. Any record that satisfies any of the criteria is returned.

If you can't get the above to work, e-mail me privately at directormac2000@yahoo.com and I'll send you an .mdb
 

Darrell

Registered User.
Local time
Today, 20:20
Joined
Feb 1, 2001
Messages
196
Thanks but this is not what I am after. I want to be able to leave some or all fields blank and have only the relevant info returned for the fields chosen.

Using the above method, I can do this for one field but not two or more.

So, thanks anyway...
 

directormac

Occasional Presence
Local time
Today, 20:20
Joined
Oct 24, 2001
Messages
245
I'm sorry this hasn't bee helpful for Darrell, but I HAVE figured out why it wasn't working for him:

Since I was using Null as the true part of my Iif, boxes that were Empty (as opposed to actually having a null value) were not evaluated. Solution: You can use it as is if you tab through blank criteria boxes (leaving behind, I guess, actual Null values), or adjust the criteria form so that Null is the default value for each unbound box. Makes me wonder if the very first idea would work correctly with the same adjustment...

Anyway, don't give up on us, keep helping us understand more precisely what you want, and we (I, at least) will keep offering ideas (until I run out
 

directormac

Occasional Presence
Local time
Today, 20:20
Joined
Oct 24, 2001
Messages
245
For anyone following this thread, Darrell and I have been talking off-list and here's the latest solution. Given an unbound form called "Criteria" which has three unbound text boxes ("ShiftName", "StartDate", "EndDate", all formatted correctly), the following three entries in the Criteria: row of the respective fields seems to work:

Under field "NameOfShift" is:
Like (Nz([Forms]![Criteria]![ShiftName],"*"))

Under field "ShiftStartDate" is:
>=(Nz([Forms]![Criteria]![StartDate],#1/1/999#))

Under field "ShiftEndDate" is:
<=(Nz([Forms]![Criteria]![EndDate],#12/31/9999#))

This seems to work... SO FAR...
 

directormac

Occasional Presence
Local time
Today, 20:20
Joined
Oct 24, 2001
Messages
245
Heard from Darrel that this last version seems to do the trick. Case Closed.

Criteria for name field is:
>
> Like (Nz([Forms]![Criteria]![ShiftName],"*"))
>
> criteria for Start Date is:
>
> >=(Nz([Forms]![Criteria]![StartDate],#1/1/999#))
>
> Criteria for End Date is:
>
> <=(Nz([Forms]![Criteria]![EndDate],#12/31/9999#))
>
> Again, remember to change those form controls to your own. Darrell.mdb
> attached (s/b '97 format). Two caveats: won't update query if the
> newly-entered criteria aren't entered/tabbed and won't handle dates less
> than year 999 or greater than year 9999. Let me know how (if?) this
> works for you.
 
R

Rich

Guest
Doesn't the query by form using dynamic sql in the qry97 sample database do the same better?
 

Alexandre

Registered User.
Local time
Tomorrow, 02:20
Joined
Feb 22, 2001
Messages
794
I find that a major limitation of the QBF is that it is unable to handle properly criteria related to child tables. Then you have got to make your custom query... and simple filters are a much less heavy method for that. I personaly gave up using QBF for that reason.
Or does anyone have a different experience / workaround with it?

Alex
 

Darrell

Registered User.
Local time
Today, 20:20
Joined
Feb 1, 2001
Messages
196
Thanks to all the people that have replied to this topic, but due to some anomoly I am only able to view the first 5 replies, so thanks to everyone who has replied with suggestions after this but I haven't been able to see what you've posted.

I have, however, thanks to directormac, been able to solve the problem by using the Nz function in each criteria of the query.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom