I have a form that uses a combo box for end users to be able to select a month from a table. This selection is then used as criteria in a query to generate the required results. Currently the query reads as follows:

[forms]![switchboard]![month]

Is there a way to change this criteria so that if a user does not select a month, the criteria for this field is ignored? There are actually multiple combo boxes used to run the query and basically if the user doesn't select a month but selects a user name, I want the query to ignore the month (as if there was no month criteria) and just run the user name on all months data.

Any thoughts or assistance would be appreciated.

Set the criteria to:

[forms]![switchboard]![month] Or ([forms]![switchboard]![month] Is Null)

I tried that and it didn't work. Is that because I have to have the user select a null value as opposed to just not selecting anything? I tried it both ways also (as one criteria line and using the embedded or criteria function.

I had this problem the other day. I'm not sure exactly how to fix this problem. The following worked for me, but my values for the criteria were coming from a table so I'm not sure if this will help or not.

IIf([Forms]![Switchboard]![month] Is Not Null,[Forms]![Switchboard]![month],([tblDates].[dtmMonth]))

I hope this helps or at least gives you an idea. Good Luck.

Kevin

Just in case I wasn't clear enough--

That IIf statement is what you will put in as your criteria for the query. If you make a table called tblDates and then have a field called dtmMonth where you list every month I believe that statement will work. I don't know if that's too much extra work or not, but just thought I would clarify that a little bit.

Sorry.... once again I read the original post too fast. My first post should work for you if you change the name of the table and the name of the field in the if statement to match your table name and field. I missed the part where you said the combo box is getting the values from the table, so sorry about that. I think my original post will work. It worked for me. Sorry again. I'll stop posting now.

I tried it and it still didn't work. When the criteria gets to the point that it requests data from the table where the month is stored, it asks me to enter the specific data instead of using "wildcard" or "all" table entries. If I leave that prompt balnk, it won't pull any data. I have the dates stored in a table [days].[month] so I set the criteria to read ...

IIf([Forms]![Switchboard]![month] Is Not Null,[Forms]![Switchboard]![month],([days].[Month]))

I've tried other tables that also have the month data stored in them and receive the same prompt. This is a crosstab query and I wouldn't think that has anything to do with it but I thought I'd throw that out there also.

I'll keep plugging away on it but please continue to throw out suggestions that might work.

Thanks again for the help...

???

If the user doesn't select anything, then the value of the ComboBox is Null ...(unless you set up some sort of default value for the ComboBox).

I use the expression I posted numerous times and it works fine. Perhaps, you need to post relevant details of your Table / Form and the SQL String of your Query ...

Please forgive me, I am pretty novice at Access....Let me ask you this....if nothing is selected. the query assumes the value is null. If the value is null, won't the query look for a null field? None of the values in the table are null. I am trying to get it so if no value is selected, it will pull ALL values. (moreso a wildcard as opposed to null).

I've tried ...

IIf([Forms]![Switchboard]![month] Is Not Null,[Forms]![Switchboard]![month],([days].[Month]))
it prompts for a value and if no value is selected, no data is returned.

Also...
[forms]![switchboard]![month] Or ([forms]![switchboard]![month] Is Null)
no pompt but again results in nothing


SQL View:

SELECT Dealervisittable.[Month of Visit], DealerListTable.[Dealer Number], DealerListTable.[Legal Name], DealerListTable.[DBA Name], DealerListTable.City, DealerListTable.State, DealerListTable.[Dealer Reserve Plan], DealerListTable.[DRM #], DealerListTable.[DRM Name], DealerListTable.[Dlr Grp], DealerListTable.[FloorPlan Provider], DealerListTable.Make1, DealerListTable.Make2, DealerListTable.Make3, DealerListTable.Make4, DealerListTable.Make5, DealerListTable.Make6, DealerListTable.Make7, DealerListTable.Make8, DealerListTable.Make9, [Dealervisittable Query].[Dealer Number], [Dealervisittable Query].[Month of Visit], [Dealervisittable Query].[Visit or Control], [Dealervisittable Query].[Analyst Name], [Dealervisittable Query].[Dealer Profile], [Dealervisittable Query].Venue, [Dealervisittable Query].[Field Visit Focus 1], [Dealervisittable Query].[Field Visit Focus 2], [Dealervisittable Query].[Field Visit Focus 3], [Dealervisittable Query].[Appreciation Gift], [Dealervisittable Query].[Met With 1], [Dealervisittable Query].[Met with 2], [Dealervisittable Query].[Met with 3], [Dealervisittable Query].[Met with 4], [Dealervisittable Query].[Title 1], [Dealervisittable Query].[Title 2], [Dealervisittable Query].[Title 3], [Dealervisittable Query].[Title 4], [Dealervisittable Query].[Analyst Comments], Month0.Month, Month0.days0, Month0.[Dealer Number], Month0.[Decisioned Count 740+], Month0.[Approved Count 740+], Month0.[Decisioned Count 720-739], Month0.[Approved Count 720-739], Month0.[Decisioned Count 700-719], Month0.[Approved Count 700-719], Month0.[Decisioned Count 680-699], Month0.[Approved Count 680-699], Month0.[Decisioned Count 660-679], Month0.[Approved Count 660-679], Month0.[Decisioned Count 640-659], Month0.[Approved Count 640-659], Month0.[Decisioned Count 620-639], Month0.[Approved Count 620-639], Month0.[Decisioned Count <620], Month0.[Approved Count <620], Month0.[Decisioned Count Total], Month0.[Approved Count Total], Month0.[Booked Count 740+], Month0.[Note Amount 740+], Month0.[Lifetime Earnings 740+], Month0.[ROE 740+], Month0.[Booked Count 720-739], Month0.[Note Amount 720-739], Month0.[Lifetime Earnings 720-739], Month0.[ROE 720-739], Month0.[Booked Count 700-719], Month0.[Note Amount 700-719], Month0.[Lifetime Earnings 700-719], Month0.[ROE 700-719], Month0.[Booked Count 680-699], Month0.[Note Amount 680-699], Month0.[Lifetime Earnings 680-699], Month0.[ROE 680-699], Month0.[Booked Count Summary Prime], Month0.[Note Amount Summary Prime], Month0.[Lifetime Earnings Summary Prime], Month0.[ROE Summary Prime], Month0.[Booked Count 660-679], Month0.[Note Amount 660-679], Month0.[Lifetime Earnings 660-679], Month0.[ROE 660-679], Month0.[Booked Count 640-659], Month0.[Note Amount 640-659], Month0.[Lifetime Earnings 640-659], Month0.[ROE 640-659], Month0.[Booked Count 620-639], Month0.[Note Amount 620-639], Month0.[Lifetime Earnings 620-639], Month0.[ROE 620-639], Month0.[Booked Count LT 620], Month0.[Note Amount LT 620], Month0.[Lifetime EarningsLT 620], Month0.[ROE LT 620], Month0.[Booked Count Summary Near Prime], Month0.[Note Amount Summary Near Prime], Month0.[Lifetime Earnings Summary Near Prime], Month0.[ROE Summary Near Prime], Month0.[Booked Count Overall], Month0.[Note Amount Overall], Month0.[Lifetime Earnings Overall], Month0.[ROE Overall]
FROM ((Dealervisittable LEFT JOIN DealerListTable ON Dealervisittable.[Dealer Number] = DealerListTable.[Dealer Number]) INNER JOIN Month0 ON (Dealervisittable.[Dealer Number] = Month0.[Dealer Number]) AND (Dealervisittable.[Month of Visit] = Month0.Month)) INNER JOIN [Dealervisittable Query] ON (Dealervisittable.[Month of Visit] = [Dealervisittable Query].[Month of Visit]) AND (Dealervisittable.[Dealer Number] = [Dealervisittable Query].[Dealer Number])
WHERE (((Dealervisittable.[Month of Visit])=[forms]![switchboard]![month] Or ([forms]![switchboard]![month] Is Null))) OR ((([forms]![switchboard]![month]) Is Null));


The switchboard uses a table called [days] to pull the values for month (i.e. 1/1/07, 2/1/07, 3/1/07, etc.). The query uses that switchboard to pull data from another table called [dealervisittable]. ALL values in the [month] field in the [dealervisittable] are popuated, there are no null values. The problem I am trying to solve is to find a way that a user does not have to select a value therefore pull ALL values in the table.

I hope that makes sense.

Thanks again.


Edited by: Leetch on Fri Jun 1 18:53:40 EDT 2007.

Try the criteria:

Well I made those changes and it didn't work either. Unfortunately I am quite the amateur when it comes to Access and I am at a loss as to why this won't work. Any other suggesstions would be helpful but it seems I am resigned to the fact I will have to create seperate queries to capture all the data.

Thanks again for all your efforts.

Well apparently it has something to do with the date field itself. The above criteria works fine on my name fields but for some reason it won't work properly in the date field. I know dates are touchy and maybe it has something to do with the way I have my dates formulated...I don't know.

Thanks again for all the help!!!

What is the default "Short Date" format for your country?

The short date is m/dd/yyyy. I'm sure it has something to do with the format and not being able to read a null value as a date. Like I said, the formula works in all other fields except this date field.

I also tried the following [forms]![switchboard]![month] or (between nz([forms]![switchboard]![month],#1/1/2000#) and nz([forms]![switchboard]![month],#1/1/2050#))

nz should change the null value to the dates in a range using the between function but it's still not reading all the dates.



Edited by: Leetch on Mon Jun 4 19:30:48 EDT 2007.

Please post a copy of your database (compact & zip to less than 500 kB first) and specify the names of the rlevant Form and Query you are having problems with ...