Combo Box/Select all

[Ruth]

I have a form with combo boxes that I am using to provide criteria to
a query. I am able to pass the other combo box values (I require each
of those to have a selection made) by referencing the control name in
the query. The form values are passed to the query with a button click
(which actually launches a report that the query is based on).

Here's what I need to do:
If NO customer is selected in cboCustomer, then records for ALL
Customers are returned. I have tried various solutions using the "On
Click" event for the button but can't get the syntax right.

Help please?

Thanks!
Ruth

BTW, cboCustomer is based on the field [CustomerName]. I chose not to
use the [CustomerID] field since it is a text field as well and
doesn't conform to any standard parameters like # characters, no
spaces, etc.

 

[Steve]

Put the following expression in the criteria for Customer in your query:
Forms!NameOfYourForm!CboCustomer Or (Forms!NameOfYourForm!CboCustomer Is
Null)

Your query will return the customer you select in CboCustomer or if you make
no selection, your query will return all customers.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Klatuu]

Here is the criteria expression to do that:

LIKE IIf(Forms!MyForm!MyComb Is Null, "*", Forms!MyForm!MyComb)

In both cases, it will not return records with a null in the field.

 

[Jeff Smith]

Hello,
I have a simlar problem. I have a form on which is a ComboBox and ActiveX Control (Calendar 11). An employee is selected from the combo box and a date is selected from the calendar. A button of the form should pass the selection of the combo box and calendar to a query linked to a report (the report opens via DoCmd.OpenReport. I have been able to get the value of the combo box passed (using [forms]![MyFormName]![ComboBoxName], but I cannot get the date to work...it's either not passing to the query or not formatted correctly. Can anyone help?
Thanks. Jeff

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ruth]

Hi Steve,
Thanks for your help! Your solution was the one that ultimately
worked! I tried a coding solution but never could get it to run, so I
revisited your suggestion.

Here's what the final SQL looks like for the query that the menu form
provides values for (with line breaks for readability)and (sorry for
the long field and control names-- it's a legacy database and more LOE
than the client wants to pay for to revise all tables/forms/reports
with better names):

SELECT Jobs.COnumber, Jobs.POnumber, Jobs.Start, Jobs.Stop,
Customers.CustomerName, Jobs.CustomerID, Jobs.Location,
Jobs.AircraftNo, Jobs.AircraftType, Jobs.JobStatus, Jobs.[Aircraft
Location]
FROM Customers INNER JOIN Jobs ON Customers.CustomerID =
Jobs.CustomerID
WHERE (((Jobs.Start) Between (IIf([Forms]![menuFrmRptCnclJobs]!
[cboStartDate] Is Null,([Jobs].[Start])>#1/1/2001#,[Forms]!
[menuFrmRptCnclJobs]![cboStartDate])) And (IIf([Forms]!
[menuFrmRptCnclJobs]![cboStopDate] Is Null,([Jobs].[Start])<=Now(),
[Forms]![menuFrmRptCnclJobs]![cboStopDate]))) AND
((Jobs.JobStatus)="Canceled") AND (([Forms]![menuFrmRptCnclJobs]!
[cboCustName]) Is Null) AND (([Forms]![menuFrmRptCnclJobs]!
[cboLocation]) Is Null))

OR (((Jobs.Start) Between (IIf([Forms]![menuFrmRptCnclJobs]!
[cboStartDate] Is Null,([Jobs].[Start])>#1/1/2001#,[Forms]!
[menuFrmRptCnclJobs]![cboStartDate])) And (IIf([Forms]!
[menuFrmRptCnclJobs]![cboStopDate] Is Null,([Jobs].[Start])<=Now(),
[Forms]![menuFrmRptCnclJobs]![cboStopDate]))) AND
((Jobs.CustomerID)=[Forms]![menuFrmRptCnclJobs]![cboCustName]) AND
((Jobs.JobStatus)="Canceled") AND (([Forms]![menuFrmRptCnclJobs]!
[cboLocation]) Is Null))

OR (((Jobs.Start) Between (IIf([Forms]![menuFrmRptCnclJobs]!
[cboStartDate] Is Null,([Jobs].[Start])>#1/1/2001#,[Forms]!
[menuFrmRptCnclJobs]![cboStartDate])) And (IIf([Forms]!
[menuFrmRptCnclJobs]![cboStopDate] Is Null,([Jobs].[Start])<=Now(),
[Forms]![menuFrmRptCnclJobs]![cboStopDate]))) AND
((Jobs.Location)=[Forms]![menuFrmRptCnclJobs]![cboLocation]) AND
((Jobs.JobStatus)="Canceled") AND (([Forms]![menuFrmRptCnclJobs]!
[cboCustName]) Is Null))

OR (((Jobs.Start) Between (IIf([Forms]![menuFrmRptCnclJobs]!
[cboStartDate] Is Null,([Jobs].[Start])>#1/1/2001#,[Forms]!
[menuFrmRptCnclJobs]![cboStartDate])) And (IIf([Forms]!
[menuFrmRptCnclJobs]![cboStopDate] Is Null,([Jobs].[Start])<=Now(),
[Forms]![menuFrmRptCnclJobs]![cboStopDate]))) AND
((Jobs.CustomerID)=[Forms]![menuFrmRptCnclJobs]![cboCustName]) AND
((Jobs.Location)=[Forms]![menuFrmRptCnclJobs]![cboLocation]) AND
((Jobs.JobStatus)="Canceled"))
ORDER BY Customers.CustomerName;

Thanks again for your suggestion and I hope this helps someone else.
Obviously it's taken me days of effort to get here!

Ruth