Query by form w/ null options.

[Fspinelli]

I have a query by form that asks for:

Start Date (field name is "txtDate1")
End Date (field name is "txtDate2")
Distributor (combo box name is "cboDist")
Client (combo box name is "cboClient")

The qeury brings up the data just fine if the end user has every field filled in, but what if Start and End dates are not needed, and/or the distribitor or client?

Any one of them with criteria in them should produce results for the end user. Well, that's my wish. However; I can only get desired results if each field has data in it.

The query has this:

Date:
Between [Forms]![frmMain]![txtDate1] And [Forms]![frmMain]![txtDate2]


Client:
[ClientName]=[Forms]![frmMain]![cboClient] Or [Forms]![frmMain]![cboClient] Is Null
(I have "true" in the criteria part of that)


Distributor:
[Distame]=[Forms]![frmMain]![cboDist] Or [Forms]![frmMain]![cboDist] Is Null
(I have "true" in the criteria part of that)

I tried "Is Null" in the Or spot of each query column, but that didn't work.

Any suggestions?

Thank you!

[FishVal]

How to show all values in a query iif criteria?

[Fspinelli]

Thank you for the reply, FishVal!

I think that's more than I need. There are two text boxes and two combo boxes (Value List).

I just need to make it so that any one, or all, that are filled in will produce results. For instance, if someone just wants a start date (they enter it dd/mm/yyyy) and a particular client (they would choose from the list in the combo box) then the subform populates the results. No matter if a field has something in it or not (but one of them at least has to have something.)

I hope I'm not confusing everyone too much. Can you tell I'm a beginner?

[FishVal]

So, this means you have 4 (even 5 taking into account the requirement that at least one of 4 controls has to be not null) conditions.
A record should be fetched if all 4(5) conditions are True. So far , so good.
Now, each condition has to be true if correspondent comparison returns True or a correspondent control value is False. An example of how it could be done is in the thread I've posted link to (msg #7).
When combining all 4(5) conditions pay attention to operator precedence (AND operation is performed before OR), so you'll need to put brackets appropriately to override default order of expression evaluation.

And the last, but not the least. If you feel it is too complicated to get it all at once, then try to make it for a single condition first, then for two and then for all the set.

Kind regards,
Fish.

[NeoPa]

FSpinelli:
For instance, if someone just wants a start date (they enter it dd/mm/yyyy)

You need to be a little careful with dates, especially if you're using English/French format (d/m/yyyy). You can find more detail of that in Literal DateTimes and Their Delimiters (#). I think Access tries to be clever for you, but remember that TextBoxes just store their data as text. It is only converted when used. You should avoid the possibility of comparing dates as text strings as the order textually is quite different from the order expected when the data is interpreted as dates.

Talking of national formats, would it be out of order to ask why that format if you're from New York State?

[Fspinelli]

Um... because? I don't have an intelligent answer for that one! (**as she crosses her eyes**)

I'm working off of someone elses work. It's already in the DB as mm/dd/yyyy (08/27/2010) so I'm just bringing that format on over into the QBF. Plus we usually put the month, the date, and the year (maybe abreviated like 8/27/10).

It's just for my small office and keeping with the current format reduces the headache I'm getting from relearning code (it's been about 18 years) and utilizing Access (remember v2? lol).

Just need those four fields (or at least one) to bring up data from a query. If it's just a start date of 2/10/2009 - then whatever broker and client along with whatever fields I chose has any relation to that start date will come up in the subform. If it's just a broker name chosen from the combo box, then everything comes up (yes, I have it grouped by whatever). If only a client, then wallah - same thing. Simple stuff.

Trying to keep it as simple as possible.

Thanks for the link - I'll be reading and trying to figure it out for sure!

[NeoPa]

Let me start by apologising for leaving you in the wind here somewhat. I was away last week, but I'm back now.

FSpinelli:
Um... because? I don't have an intelligent answer for that one! (**as she crosses her eyes**)

I'm working off of someone elses work. It's already in the DB as mm/dd/yyyy (08/27/2010) so I'm just bringing that format on over into the QBF. Plus we usually put the month, the date, and the year (maybe abreviated like 8/27/10).

It's just for my small office and keeping with the current format reduces the headache I'm getting from relearning code (it's been about 18 years) and utilizing Access (remember v2? lol).

This response confuses me. You're describing why m/d/yyyy, but I was asking about why d/m/yyyy. The former is quite natural for someone from the USA, whereas the latter is a European format. It's only my curiosity of course, but I wondered why you would be using a European format.

FSpinelli:
Just need those four fields (or at least one) to bring up data from a query. If it's just a start date of 2/10/2009 - then whatever broker and client along with whatever fields I chose has any relation to that start date will come up in the subform. If it's just a broker name chosen from the combo box, then everything comes up (yes, I have it grouped by whatever). If only a client, then wallah - same thing. Simple stuff.

Looking at your question again, and assuming you want to work from the form directly (as you appear to be trying), I would guess you're looking for something like :

...
WHERE    ([ClientName] Like '*' & [Forms]![frmMain]![cboClient] & '*')
  AND    ([DistName] Like '*' & [Forms]![frmMain]![cboDist] & '*')
  AND    ([DateField] Between CDate(Nz([Forms]![frmMain]![txtDate1], '1/1/1900'))
                          And CDate(Nz([Forms]![frmMain]![txtDate2],'12/31/9999')))

An alternative approach can be found in Cascaded Form Filtering.

FSpinelli:
Trying to keep it as simple as possible.

Thanks for the link - I'll be reading and trying to figure it out for sure!

Both wise ideas.

[Fspinelli]

Thanks, Neo! I'm going to give that a try.

Any suggestion on partial matches? I have a text box where the end user can enter, for example, "Tr" (even if they use upper and/or lower case) in the Company field then click the search button. Every record that has the letters "tr" in it comes up... Not just the beginning of the name (which is what's needed).

Also, with my question, and I hope I can figure out how to ask it where it makes sense; Can I make it a combo box which also allows text (one or a few letters) in it? Another words, the user can click on the combo box and choose a specific company record from the combo box list, or, they can just enter one or several letters, click search and have the results populate. Know what I mean?

I really appreciate the guidance and imput (from everyone).

[NeoPa]

FSpinelli:
Any suggestion on partial matches? I have a text box where the end user can enter, for example, "Tr" (even if they use upper and/or lower case) in the Company field then click the search button. Every record that has the letters "tr" in it comes up... Not just the beginning of the name (which is what's needed).

Simply remove the leading asterisk (*) characters.

EG. Line #2 would become :

WHERE    ([ClientName] Like [Forms]![frmMain]![cboClient] & '*')

FSpinelli:
Also, with my question, and I hope I can figure out how to ask it where it makes sense; Can I make it a combo box which also allows text (one or a few letters) in it? Another words, the user can click on the combo box and choose a specific company record from the combo box list, or, they can just enter one or several letters, click search and have the results populate. Know what I mean?

I believe you can set up a ComboBox so that it allows entries that are not found in the list (Limit To List = No). This should allow what you need. Give it a try.

[Fspinelli]

Thank you!

I must be doing something wrong again. I tried what you said and I get a syntax error and my query won't save.

So this is the code I have currently:

[Company] Like "*" & [Forms]![frmSearch]![TxtCompany] & "*" or [Forms]![frmSearch]![txtCompany] Is Null

Do I have to put some sort of code before the first Like "*" to make it only choose the beginning letters of a field, not just any letters that match?

[NeoPa]

I don't know where you got that from. It's quite different from anything I suggested.

What you need is simply :

([Company] Like [Forms]![frmSearch]![TxtCompany] & '*')

[Fspinelli]

Yay! It works! You're the best!!!

[NeoPa]

Always glad to help :)

[Fspinelli]

Good morning NeoPa!

So I thought I was rockin'n-rolling until different tests out using various dates, with or without Broker(s) and/or with or without Client(s).

The Beginning and Ending dates are required. (this works)
Broker is not required but is a choice (value list)
Client is not required but is a choice (value list)

Here is the code in my query:

Beginning and End Dates are based off of the Date field:

Criteria: Between [Forms]![frmTradeMain]![txtDate1] And [Forms]![frmTradeMain]![txtDate2] Or Is Null
Or: Is Null
(I don't know why "Is Null" in there twice, Access did it, not me.)

Broker:
[Forms]![frmTradeMain]![cboBroker] Or [Forms]![frmTradeMain]![cboBroker] Is Null
Criteria: is set to True
(Or: does not have Is Null because even when I put it in there it doesn't work)

Client:
[Forms]![frmTradeMain]![cboClient] Or [Forms]![frmTradeMain]![cboClient] Is Null
Criteria: is set to True
(Or: does not have Is Null because even when I put it in there it doesn't work)

Dates work, but I can't get (Only the results) for the chosen broker and/or client.

Help?

[NeoPa]

This is hard to work with as I'm still short of information. Try popping the SQL in here and I'll work to that.