Query Using Form

[Bob Watson]

I am aware that non-bound query objects on forms can
be used to drive queries. Assuming you had four potential
fields to put into a query, but any of these four
could be used or not used, how do you drive the query?

To give an example, assume you wanted a query
which would respond to

Time Zone Frequency
Eastern Always
Central Sometimes
Mountain Never
Pacific

MediaType One More Which I Forget
Radio CriteriaA
TV CirteriaB

Now, I need the user to be able to pick any
and all of the above ( somewhere between 0 and
4 of the above categories ) and they use that
information to power a query.

I guess, said another way, all of the four criteria
categories could have an
ALL value as well. Such
as
Time Zone etc etc etc etc
ALL
Eastern
Central
Mountain
Pacific

How would I go about that?

Thanks in advance,
Bob

 

[Graham R Seach]

Bob,

You have to build the query in stages.

1. First build the basic query:
sSQL1 = "SELECT blah, blah FROM tblWhatsit"

2. Next build the sort order:
sSQL3 = "ORDER BY blah DESC, blahh"

3. Next build the filter clause:
If Not IsNull(Me!cboTimeZone) Then
sSQL2 = "[Time Zone] = """ & Me!cboTimeZone & """"
End If

If Not IsNull(Me!cboFrequency) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Frequency] = """ & Me!cboFrequency & """"
End If

If Not IsNull(Me!cboMediaType) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Media Type] = """ & Me!cboMediaType & """"
End If

4. Finally, put them all together:
sSQLFinal = sSQL1 & IIf(Len(sSQL2) > 0, " WHERE ", "") & sSQL2 & sSQL3

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Bob Watson]

Thanks Graham ... I forgot to say that this
is Access 97 ... all of what you said is applicable
for 97, right?? I actually come from a Delphi
background and am only marginally Access proficient.

Thanks,
Bob

Bob,

You have to build the query in stages.

1. First build the basic query:
sSQL1 = "SELECT blah, blah FROM tblWhatsit"

2. Next build the sort order:
sSQL3 = "ORDER BY blah DESC, blahh"

3. Next build the filter clause:
If Not IsNull(Me!cboTimeZone) Then
sSQL2 = "[Time Zone] = """ & Me!cboTimeZone & """"
End If

If Not IsNull(Me!cboFrequency) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Frequency] = """ & Me!cboFrequency & """"
End If

If Not IsNull(Me!cboMediaType) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Media Type] = """ & Me!cboMediaType & """"
End If

4. Finally, put them all together:
sSQLFinal = sSQL1 & IIf(Len(sSQL2) > 0, " WHERE ", "") & sSQL2 & sSQL3

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

I am aware that non-bound query objects on forms can
be used to drive queries. Assuming you had four potential
fields to put into a query, but any of these four
could be used or not used, how do you drive the query?

To give an example, assume you wanted a query
which would respond to

Time Zone Frequency
Eastern Always
Central Sometimes
Mountain Never
Pacific

MediaType One More Which I Forget
Radio CriteriaA
TV CirteriaB

Now, I need the user to be able to pick any
and all of the above ( somewhere between 0 and
4 of the above categories ) and they use that
information to power a query.

I guess, said another way, all of the four criteria
categories could have an
ALL value as well. Such
as
Time Zone etc etc etc etc
ALL
Eastern
Central
Mountain
Pacific

How would I go about that?

Thanks in advance,
Bob

 

[Graham R Seach]

Bob,

<<...all of what you said is applicable for 97, right??>>
Yep.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

Thanks Graham ... I forgot to say that this
is Access 97 ... all of what you said is applicable
for 97, right?? I actually come from a Delphi
background and am only marginally Access proficient.

Thanks,
Bob

Bob,

You have to build the query in stages.

1. First build the basic query:
sSQL1 = "SELECT blah, blah FROM tblWhatsit"

2. Next build the sort order:
sSQL3 = "ORDER BY blah DESC, blahh"

3. Next build the filter clause:
If Not IsNull(Me!cboTimeZone) Then
sSQL2 = "[Time Zone] = """ & Me!cboTimeZone & """"
End If

If Not IsNull(Me!cboFrequency) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Frequency] = """ & Me!cboFrequency & """"
End If

If Not IsNull(Me!cboMediaType) Then
sSQL = sSQL & IIf(Len(sSQL) > 0, " AND ", "")
sSQL = sSQL & "[Media Type] = """ & Me!cboMediaType & """"
End If

4. Finally, put them all together:
sSQLFinal = sSQL1 & IIf(Len(sSQL2) > 0, " WHERE ", "") & sSQL2 & sSQL3

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

I am aware that non-bound query objects on forms can
be used to drive queries. Assuming you had four potential
fields to put into a query, but any of these four
could be used or not used, how do you drive the query?

To give an example, assume you wanted a query
which would respond to

Time Zone Frequency
Eastern Always
Central Sometimes
Mountain Never
Pacific

MediaType One More Which I Forget
Radio CriteriaA
TV CirteriaB

Now, I need the user to be able to pick any
and all of the above ( somewhere between 0 and
4 of the above categories ) and they use that
information to power a query.

I guess, said another way, all of the four criteria
categories could have an
ALL value as well. Such
as
Time Zone etc etc etc etc
ALL
Eastern
Central
Mountain
Pacific

How would I go about that?

Thanks in advance,
Bob