Blank selection on form equals everything in query

[instereo911 via AccessMonster.com]

Good afternoon,

I don't know how to explain this well but here you go (and semi new to access)

The form selection which feed into a query to show what was selected on the
form to play in the query. (that doesn't make sense but i don't know how to
explain well)


Here is the example..

(Table1 example)
Director
Apple
Orange
Pear

(AbndtTable example)
Director Date Jan Feb
Apple 01/01/2008 Big Red
Apple 01/01/2008 Big Green
Apple 01/01/2008 Small Red
Orange 01/01/2008 Big Orange
Pear 01/01/2008 Med Green
Apple 01/02/2008 Small Red
etc.

Query1
SELECT AbndtTable.Date, AbndtTable.Director, AbndtTable.Jan, AbndtTable.Feb,
FROM AbndtTable
WHERE (((AbndtTable.Date)=[Forms]![Form1]![DatesList]) AND ((AbndtTable.
Director)=[Forms]![Form1]![DirectorList]));



(Form1 example)
DateList Drop Down Menu (values 01/01/2008, 01/02/2008, etc)

DirectorList Apple
Orange
Pear

JanList Big
Small
Med

button that says RUN QUERY

So if the selection 01/01/2008, Apple and Pear selected and Nothing selected
on JanListit will link to a query to show everythig meeting those criteria
selected

So It will show
Apple 01/01/2008 Big Red
Apple 01/01/2008 Big Green
Apple 01/01/2008 Small Red
Pear 01/01/2008 Med Green


I can't figure it out...

Anyone or anything can help.

 

[Allen Browne]

SELECT AbndtTable.Date,
AbndtTable.Director,
AbndtTable.Jan,
AbndtTable.Feb,
FROM AbndtTable
WHERE (([Forms]![Form1]![DatesList] Is Null)
OR (AbndtTable.Date = [Forms]![Form1]![DatesList]))
AND (([Forms]![Form1]![DirectorList] Is Null)
OR (AbndtTable.Director = [Forms]![Form1]![DirectorList]));

The important thing here is that when the control on the form is null, we
are not comparing it to anything. We just crafted the expression so it
returns TRUE if the control is null. If it's not null, we compare the field
to it.

Other important factors:
- Watch the bracketing when you mix ANDs and ORs.
- Date is a reserved word, so not a good field name.

 

[instereo911 via AccessMonster.com]

Hi Allen,

This worked great with the form being a combo box.... I want the form to be a
list box and multi selections to work. Is there anything in the VBA for the
run query i can do? Currently it says this


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub



This works for the combo box selections, but list box it does not work for
multiple selections... Any thoughts?

SELECT AbndtTable.Date,
AbndtTable.Director,
AbndtTable.Jan,
AbndtTable.Feb,
FROM AbndtTable
WHERE (([Forms]![Form1]![DatesList] Is Null)
OR (AbndtTable.Date = [Forms]![Form1]![DatesList]))
AND (([Forms]![Form1]![DirectorList] Is Null)
OR (AbndtTable.Director = [Forms]![Form1]![DirectorList]));

The important thing here is that when the control on the form is null, we
are not comparing it to anything. We just crafted the expression so it
returns TRUE if the control is null. If it's not null, we compare the field
to it.

Other important factors:
- Watch the bracketing when you mix ANDs and ORs.
- Date is a reserved word, so not a good field name.

Good afternoon,

[quoted text clipped - 59 lines]

Anyone or anything can help.

 

[Allen Browne]

It is possible to craft a VBA function that accepts the list box and field,
and returns True if the field value is selected in the list box. In the
query, you call it as:
WHERE MyFunc([Forms].[MyForm].[MyListbox], [MyField])

But a better solution might be to build the filter string in code, and apply
it as the Filter for a form or the WhereCondition for a report. Here's an
example of how to do that:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

This worked great with the form being a combo box.... I want the form to
be a
list box and multi selections to work. Is there anything in the VBA for
the
run query i can do? Currently it says this


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub



This works for the combo box selections, but list box it does not work for
multiple selections... Any thoughts?

SELECT AbndtTable.Date,
AbndtTable.Director,
AbndtTable.Jan,
AbndtTable.Feb,
FROM AbndtTable
WHERE (([Forms]![Form1]![DatesList] Is Null)
OR (AbndtTable.Date = [Forms]![Form1]![DatesList]))
AND (([Forms]![Form1]![DirectorList] Is Null)
OR (AbndtTable.Director = [Forms]![Form1]![DirectorList]));

The important thing here is that when the control on the form is null, we
are not comparing it to anything. We just crafted the expression so it
returns TRUE if the control is null. If it's not null, we compare the
field
to it.

Other important factors:
- Watch the bracketing when you mix ANDs and ORs.
- Date is a reserved word, so not a good field name.

Good afternoon,

[quoted text clipped - 59 lines]

Anyone or anything can help.

 

[instereo911 via AccessMonster.com]

Thanks tons Allen!

It is possible to craft a VBA function that accepts the list box and field,
and returns True if the field value is selected in the list box. In the
query, you call it as:
WHERE MyFunc([Forms].[MyForm].[MyListbox], [MyField])

But a better solution might be to build the filter string in code, and apply
it as the Filter for a form or the WhereCondition for a report. Here's an
example of how to do that:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Hi Allen,

[quoted text clipped - 49 lines]