Easy Drop Down Box Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
 
Adam I will detail out every step incase this is your first time.

Easiest way to do that is make sure you have the toolbox displayed.

View--Toolbox
Make sure the wand in the upper right is pressed and then click on
combo box and drop it in design view of your form. As soon s you
release the mouse in your form the wizard should walk you right through
how to do this and gives you the option of selecting a table and field
etc...

Goodluck!

-Brian
 
Adam said:
How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".
Click to expand...

Click the elipsis to the right of the rowsource property. This will bring up
a query builder that will allow you to choose your fields, criteria, sort
order, etc. If you want unique states, open the query's property sheet
(Alt-Enter) and set Unique Values to Yes. This should give you a unique list
of states.
I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.
Click to expand...

Comboboxes only allow a single selection. To allow multiple selections, you
must use a ListBox with its Multiselect property set to Simple or Extended. A
Listbox's rowsource can be set the same was as a combobox's.

Barry
 
You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
 
Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


Klatuu said:
You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
Adam said:
How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
 
Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


Adam said:
Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


Klatuu said:
You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
Adam said:
How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

Klatuu said:
Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


Adam said:
Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


Klatuu said:
You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


Adam said:
Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

Klatuu said:
Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


Adam said:
Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
Thanks for sticking with me. Here is where I am now...

RowSource for "StateV" in the form:
SELECT DISTINCT Contacts.[Designated State] FROM Contacts;

Modifyied IIF form (per your last post):
IIf([forms]![PSV]![StateV]="(All States)","*",[forms]![ PSV]![StateV])

WHERE section of my code that works for anythign except "All States":
WHERE..."AND Contacts.[Designated State]=(Forms!PSV!StateV)"

What I don't understand is how to incorporate the "UNION Select Null as
AllChoice , "(All)" as Bogus" section from the link you provided. Should I
leave the WHERE section as it currently stands and edit my Row Source code as
follows?:

SELECT DISTINCT Contacts.[Designated State] UNION Select Null as AllStates,
"All States" as Bogus FROM Contacts

Thanks for sticking w/ me,
Adam



Klatuu said:
First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


Adam said:
Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

Klatuu said:
Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


:

Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
That appears to be correct.

Adam said:
Thanks for sticking with me. Here is where I am now...

RowSource for "StateV" in the form:
SELECT DISTINCT Contacts.[Designated State] FROM Contacts;

Modifyied IIF form (per your last post):
IIf([forms]![PSV]![StateV]="(All States)","*",[forms]![ PSV]![StateV])

WHERE section of my code that works for anythign except "All States":
WHERE..."AND Contacts.[Designated State]=(Forms!PSV!StateV)"

What I don't understand is how to incorporate the "UNION Select Null as
AllChoice , "(All)" as Bogus" section from the link you provided. Should I
leave the WHERE section as it currently stands and edit my Row Source code as
follows?:

SELECT DISTINCT Contacts.[Designated State] UNION Select Null as AllStates,
"All States" as Bogus FROM Contacts

Thanks for sticking w/ me,
Adam



Klatuu said:
First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


Adam said:
Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

:

Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


:

Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
I changed the row source to: "SELECT DISTINCT Contacts.[Designated State]
UNION Select Null as AllStates, "All States" as Bogus FROM Contacts"

However, where do I put the IIF statement?

As it stands now, after I changed the row source code, when I open the "PSV"
form and click on the dropdown menu a popup stating "Query input must contain
at least one table or query."

Ideas?

Klatuu said:
That appears to be correct.

Adam said:
Thanks for sticking with me. Here is where I am now...

RowSource for "StateV" in the form:
SELECT DISTINCT Contacts.[Designated State] FROM Contacts;

Modifyied IIF form (per your last post):
IIf([forms]![PSV]![StateV]="(All States)","*",[forms]![ PSV]![StateV])

WHERE section of my code that works for anythign except "All States":
WHERE..."AND Contacts.[Designated State]=(Forms!PSV!StateV)"

What I don't understand is how to incorporate the "UNION Select Null as
AllChoice , "(All)" as Bogus" section from the link you provided. Should I
leave the WHERE section as it currently stands and edit my Row Source code as
follows?:

SELECT DISTINCT Contacts.[Designated State] UNION Select Null as AllStates,
"All States" as Bogus FROM Contacts

Thanks for sticking w/ me,
Adam



Klatuu said:
First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


:

Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

:

Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


:

Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
Here is the original code from my test database for the row source of the
combo. It works fine. I think you don't need the "Null as AllStates" part.

SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Your original post said you wanted to use the value of the combo as part of
your search criteria. For that you will need a query that will include the
State field from your table. The IIf code goes in the Criteria row for that
field.


Adam said:
I changed the row source to: "SELECT DISTINCT Contacts.[Designated State]
UNION Select Null as AllStates, "All States" as Bogus FROM Contacts"

However, where do I put the IIF statement?

As it stands now, after I changed the row source code, when I open the "PSV"
form and click on the dropdown menu a popup stating "Query input must contain
at least one table or query."

Ideas?

Klatuu said:
That appears to be correct.

Adam said:
Thanks for sticking with me. Here is where I am now...

RowSource for "StateV" in the form:
SELECT DISTINCT Contacts.[Designated State] FROM Contacts;

Modifyied IIF form (per your last post):
IIf([forms]![PSV]![StateV]="(All States)","*",[forms]![ PSV]![StateV])

WHERE section of my code that works for anythign except "All States":
WHERE..."AND Contacts.[Designated State]=(Forms!PSV!StateV)"

What I don't understand is how to incorporate the "UNION Select Null as
AllChoice , "(All)" as Bogus" section from the link you provided. Should I
leave the WHERE section as it currently stands and edit my Row Source code as
follows?:

SELECT DISTINCT Contacts.[Designated State] UNION Select Null as AllStates,
"All States" as Bogus FROM Contacts

Thanks for sticking w/ me,
Adam



:

First, here is a site that shows how to add All to a combo:

http://www.mvps.org/access/forms/frm0043.htm

Then in your query use something like this as the criteria for the state
field:
(Change the Names, this is from one of my Experimental forms.

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])


:

Ok, so I got the combo box going (I can forgo the multiple selection for now)
and it works great when a state is selected.

How do I write the SQL so if the combo box doesn't have a selected state it
will return all states? What if I were to code "All States" as the default
value?

Thanks,
Adam

:

Okay, if you go with a multi select list box, look in VBA Help for the
ItemsSelected property. It has a good example there of how to loop through
all the selected items.

To help out, here is a function from a form I use that has 7 different list
boxes a user can select multipe criteria from. I use this to build a string
for the filtering

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


:

Thanks for the suggestions! I'll start working through them and will post
again if I run in to trouble.

Adam


:

You will not be able to use a multi select list box, as Barry suggests, as
criteria in a query. A multi select list box is a different sort of animal.
It doesn't return a value like most controls, because it is, in fact, a
collection. You will have to use VBA to create the Where condition for the
query.

As to how to create the row source, it would be the same for either the
combo or the list boxes.
SELECT DISTINCT [State] FROM Contacts;
This will return one occurance of each state in the State field.

How good are you with VBA, to use a list box to build criteria takes some
medium leverl VBA skill? I can help you with that if you are up to it.
:

How do I ake a dropdown box populate values off of the "state" field in the
"contacts" table?

Ideally, I want each state to be listed once, assuming it is already listed
in a record in "contacts".

I plan on using this form field as a query criteria, so if possible, I also
want to make it so I can select all (or a combination?) of the states.

Thanks for the assist.

-Adam
Click to expand...
Click to expand...
Click to expand...
 
I've been following this thread because I'm having the same problem and I
can't get "all" into my combo box for the life of me.
 
It's a long SQL statement for the entire query, but here it is:

SELECT Contacts.[Wholesaler ID], Contacts.[Wholesaler Name],
Contacts.[Packet Sent to Wholesaler Date], Contacts.City, Contacts.[Product
Type], Contacts.[General Notes], Contacts.[Designated State], Contacts.[Check
Received YN], Contacts.[Employee], Contacts.[Payment for Allocation],
Contacts.[Allocated Case Volume]

FROM Contacts

WHERE Contacts.[Packet Sent to Wholesaler YN]=True And Contacts.[Agreement
Received YN]=(Forms!PSV!ARYN="True") AND Contacts.[Check Received
YN]=(Forms!PSV!CRYN="True") AND Contacts.[Product
Type]=(Forms!PSV!ProductTypeV) AND Contacts.[Designated
State]=(Forms!PSV!StateV)

GROUP BY Contacts.[Wholesaler ID], Contacts.[Wholesaler Name],
Contacts.[Packet Sent to Wholesaler Date], Contacts.City, Contacts.[Product
Type], Contacts.[General Notes], Contacts.[Designated State], Contacts.[Check
Received YN], Contacts.[Employee], Contacts.[Payment for Allocation],
Contacts.[Allocated Case Volume];
 
I don't see the part about filtering by state code in the Where clause.

Adam said:
It's a long SQL statement for the entire query, but here it is:

SELECT Contacts.[Wholesaler ID], Contacts.[Wholesaler Name],
Contacts.[Packet Sent to Wholesaler Date], Contacts.City, Contacts.[Product
Type], Contacts.[General Notes], Contacts.[Designated State], Contacts.[Check
Received YN], Contacts.[Employee], Contacts.[Payment for Allocation],
Contacts.[Allocated Case Volume]

FROM Contacts

WHERE Contacts.[Packet Sent to Wholesaler YN]=True And Contacts.[Agreement
Received YN]=(Forms!PSV!ARYN="True") AND Contacts.[Check Received
YN]=(Forms!PSV!CRYN="True") AND Contacts.[Product
Type]=(Forms!PSV!ProductTypeV) AND Contacts.[Designated
State]=(Forms!PSV!StateV)

GROUP BY Contacts.[Wholesaler ID], Contacts.[Wholesaler Name],
Contacts.[Packet Sent to Wholesaler Date], Contacts.City, Contacts.[Product
Type], Contacts.[General Notes], Contacts.[Designated State], Contacts.[Check
Received YN], Contacts.[Employee], Contacts.[Payment for Allocation],
Contacts.[Allocated Case Volume];


Klatuu said:
Post your code so I can take a look. It is working just fine in my database
Click to expand...
Click to expand...
 
Back
Top