Subform/Query Criteria

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

Guest

Hi everyone

I have set up a Query in Access. In one of the criteria fields I have
something like this which ckecks each returned record's "state" field against
a value from a textbox. This is to see if the returned record is from the
same US state as the state selected by the user. (NY, CA, GA, MD, and the
like).

Here is the criterion:

AND ((qryLocationsByType1.State) Like
[Forms]![frmMain]![subform].[Form]![txtState] )

What I really need is a way to check the returned record's "state" field
against a list of US States to see if the returned record's "state" field is
in a set of States that the user has selected through a multi-selectable
listbox.

Here is what I have... and it returns nothing. Can someone please help? Thanks

AND ((qryLocationsByType1.State) In
([Forms]![frmMain]![subform].[Form]![txtState] ))
 
Because you are using a multi-select listbox, you cannot refer directly to
the forms control, you have to build the text, which will ultimately look
like:

AND ((qryLocationsByType1.State) In ("CA", "MD", "DC")

To get to this point, I have a function that will return these values.

You would use this something like the following in your code:
strSQL = "SELECT * FROM yourQuery " _
& "WHERE .......... _
& ("AND qryLocationsByType1.State " + MultiList(yourControlName)
+ ")"

This function will return a NULL value if no items are selected. In the
example above this would cause the entire last line to be ignored (it would
not check any of the state values).

If only one item is selected it will return the equal sign and your item
enclosed in quotes, so the final line of the above query would look something
like the following if you printed out strSQL:

AND qryLocationsByType1.State = "CA"

If more than one item is selected it will look like:

AND qryLocationsByType1.State IN ("CA", "MA", "DC")


Public Function MultiList(lst As Control) As Variant

Dim varItem As Variant
Dim varList As Variant

varList = Null
For Each varItem In lst.ItemsSelected
varList = (varList + ", ") & Chr$(34) & lst.Column(0, varItem) &
Chr$(34)
Next

If lst.ItemsSelected.Count = 0 Then
MultiList = Null 'No items selected
ElseIf lst.ItemsSelected.Count = 1 Then
'only one item selected
MultiList = " = " & varList
Else
'multiple items selected
MultiList = "IN (" & varList & ")"
End If

End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Koorosh said:
Hi everyone

I have set up a Query in Access. In one of the criteria fields I have
something like this which ckecks each returned record's "state" field against
a value from a textbox. This is to see if the returned record is from the
same US state as the state selected by the user. (NY, CA, GA, MD, and the
like).

Here is the criterion:

AND ((qryLocationsByType1.State) Like
[Forms]![frmMain]![subform].[Form]![txtState] )

What I really need is a way to check the returned record's "state" field
against a list of US States to see if the returned record's "state" field is
in a set of States that the user has selected through a multi-selectable
listbox.

Here is what I have... and it returns nothing. Can someone please help? Thanks

AND ((qryLocationsByType1.State) In
([Forms]![frmMain]![subform].[Form]![txtState] ))
Click to expand...
 
BTW,

I noticed that you posted this in multiple newsgroups. If you think you
need to do this, I recommend that you "crosspost" rather than "multi-post".

Multi-posting is creating new messages with the same or similiar text and
posting them to different newsgroups, one at a time. When you do this, you
have to check each location to see what your responses are, and you
potentially waste the time of those answering your post.

Cross posting is creating a new message and sending it to multiple groups at
once. Generally, it is recommended to only do this to no more than 2 groups
at a time. How you implement this will depend on how you are accessing the
newsgroups. If via Outlook Express, you just put multiple newsgroup
addresses in the To: box. If you are using Microsofts Office Discussion
Group web site, there is an "Advanced Options" on the "New Question" form.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


Koorosh said:
Hi everyone

I have set up a Query in Access. In one of the criteria fields I have
something like this which ckecks each returned record's "state" field against
a value from a textbox. This is to see if the returned record is from the
same US state as the state selected by the user. (NY, CA, GA, MD, and the
like).

Here is the criterion:

AND ((qryLocationsByType1.State) Like
[Forms]![frmMain]![subform].[Form]![txtState] )

What I really need is a way to check the returned record's "state" field
against a list of US States to see if the returned record's "state" field is
in a set of States that the user has selected through a multi-selectable
listbox.

Here is what I have... and it returns nothing. Can someone please help? Thanks

AND ((qryLocationsByType1.State) In
([Forms]![frmMain]![subform].[Form]![txtState] ))
Click to expand...
 
Back
Top