concatenation help needed

[arthur-e]

Thanks in advance!! I've looked at fConcatFld but that doesn't seem to
be what i'm looking for or I just can't figure it out.

I'm trying to have a button select records based on two combo boxes -
one holding a number and one text.
Floor(combo18) is a floor number and site(combo20) is a building
name

Private Sub Command22_Click()
Dim strSQL As String
strSQL = "Select * FROM [qryAllSlrSites]"

strSQL = strSQL & "WHERE ((([qryAllSlrSites].[Exclude])=False) AND
((qryAllSlrSites.Floor)=" & Me.Combo18 & "))"

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

Me.RecordSource = strSQL
End Sub

Is there a good way to make do this? I've been cutting and pasting
SQL statements trying to put fields in where criteria were in the
statement.

-warning e-mail address altered- arthureNOSPACE@

[Salad]

arthur-e wrote:

Thanks in advance!! I've looked at fConcatFld but that doesn't seem to
be what i'm looking for or I just can't figure it out.

I'm trying to have a button select records based on two combo boxes -
one holding a number and one text.
Floor(combo18) is a floor number and site(combo20) is a building
name

Private Sub Command22_Click()
Dim strSQL As String
strSQL = "Select * FROM [qryAllSlrSites]"

strSQL = strSQL & "WHERE ((([qryAllSlrSites].[Exclude])=False) AND
((qryAllSlrSites.Floor)=" & Me.Combo18 & "))"

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

Me.RecordSource = strSQL
End Sub

Is there a good way to make do this? I've been cutting and pasting
SQL statements trying to put fields in where criteria were in the
statement.

-warning e-mail address altered- arthureNOSPACE@
Hmmmm...I might put in the after update event some code like
SetFilter
for each combo box.

THen create a routine

PRivate Sub SetFilter()
Dim strFilter As STring
If Not IsNull(Me.Combo18) Then
strFilter = "Exclude = False And "
"Floor = " & Me.Combo18 & " And "
ENdif

If Not IsNull(Me.Combo20) Then
strFilter = strFilter & _
"Site = " & Me.Combo20 & " And "
ENdif

If strFilter > "" Then
'remove the word And
strFilter = Left(strFilter,Len(strFilter) -5)
Me.Filter = strFilter
Me.FilterOn = True
Else
'no filter
Me.Filter = ""
Me.FilterOn = False
ENdif
ENd SUb


strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

[arthur-e]

THANKIS -
interesting concept - I'll give it a try.
The key in your routine seems to be eliminating the last AND if there
isn't one.

I couldn't even got two simple boxes to work together - I couldn't
keep the """""" and ))))) straight.
Arthur

Salad <oi*@vinegar.com> wrote:

arthur-e wrote:

Thanks in advance!! I've looked at fConcatFld but that doesn't seem to
be what i'm looking for or I just can't figure it out.

I'm trying to have a button select records based on two combo boxes -
one holding a number and one text.
Floor(combo18) is a floor number and site(combo20) is a building
name

Private Sub Command22_Click()
Dim strSQL As String
strSQL = "Select * FROM [qryAllSlrSites]"

strSQL = strSQL & "WHERE ((([qryAllSlrSites].[Exclude])=False) AND
((qryAllSlrSites.Floor)=" & Me.Combo18 & "))"

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

Me.RecordSource = strSQL
End Sub

Is there a good way to make do this? I've been cutting and pasting
SQL statements trying to put fields in where criteria were in the
statement.

-warning e-mail address altered- arthureNOSPACE@

Hmmmm...I might put in the after update event some code like
SetFilter
for each combo box.

THen create a routine

PRivate Sub SetFilter()
Dim strFilter As STring
If Not IsNull(Me.Combo18) Then
strFilter = "Exclude = False And "
"Floor = " & Me.Combo18 & " And "
ENdif

If Not IsNull(Me.Combo20) Then
strFilter = strFilter & _
"Site = " & Me.Combo20 & " And "
ENdif

If strFilter > "" Then
'remove the word And
strFilter = Left(strFilter,Len(strFilter) -5)
Me.Filter = strFilter
Me.FilterOn = True
Else
'no filter
Me.Filter = ""
Me.FilterOn = False
ENdif
ENd SUb

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

-warning e-mail address altered- arthureNOSPACE@

[Salad]

arthur-e wrote:

THANKIS -
interesting concept - I'll give it a try.
The key in your routine seems to be eliminating the last AND if there
isn't one.
It's a common practice that if you are stringing a bunch of conditions
that are ANDed, to add it to the end of each and then remove at the end.
Makes it easy to debug.

Another key is that if you call the routine from each combo, the list
will shrink or expand. For example, in the AfterUpdate event of the
Major combo (Combo18), you could have the code.
Me.Combo20 = Null
SetFilter
Now you get all of the records for the Floor to be displayed.

Then the minor combo (combo20) enter
SetFilter
in the afterupdate event.


I couldn't even got two simple boxes to work together - I couldn't
keep the """""" and ))))) straight.
Arthur
I know what you mean. :-)

If I copy/paste code from the SQL window in the query builder, there are
so many () I usually head to the "Where" clause and remove them simply
so I can read it easily. Usually the where clause is strung together by
ANDs. The only time I need tha parans is when you have ORs mixed in the
clause.

I was assuming your Combos use numbers. If not, you can add the quotes
as necessary.

Instead of
strFilter = strFilter & "Site = " & Me.Combo20 & " And "
you could use
strFilter = strFilter & "Site = '" & Me.Combo20 & "' And "

Salad <oi*@vinegar.com> wrote:

arthur-e wrote:

Thanks in advance!! I've looked at fConcatFld but that doesn't seem to
be what i'm looking for or I just can't figure it out.

I'm trying to have a button select records based on two combo boxes -
one holding a number and one text.
Floor(combo18) is a floor number and site(combo20) is a building
name

Private Sub Command22_Click()
Dim strSQL As String
strSQL = "Select * FROM [qryAllSlrSites]"

strSQL = strSQL & "WHERE ((([qryAllSlrSites].[Exclude])=False) AND
((qryAllSlrSites.Floor)=" & Me.Combo18 & "))"

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

Me.RecordSource = strSQL
End Sub

Is there a good way to make do this? I've been cutting and pasting
SQL statements trying to put fields in where criteria were in the
statement.

-warning e-mail address altered- arthureNOSPACE@

Hmmmm...I might put in the after update event some code like
SetFilter
for each combo box.

THen create a routine

PRivate Sub SetFilter()
Dim strFilter As STring
If Not IsNull(Me.Combo18) Then
strFilter = "Exclude = False And "
"Floor = " & Me.Combo18 & " And "
ENdif

If Not IsNull(Me.Combo20) Then
strFilter = strFilter & _
"Site = " & Me.Combo20 & " And "
ENdif

If strFilter > "" Then
'remove the word And
strFilter = Left(strFilter,Len(strFilter) -5)
Me.Filter = strFilter
Me.FilterOn = True
Else
'no filter
Me.Filter = ""
Me.FilterOn = False
ENdif
ENd SUb

strSQL = strSQL & " AND ((qryAllSlrSites.site)= '" & Me.Combo20 & "))"

-warning e-mail address altered- arthureNOSPACE@