Trying to set up a query

[DeanL]

Hi all,

I'm trying to set up a query that runs from a command button on a form
(simple enough so far), what I want the query to do is take values
from the fields on the form (seven fields in total) but sometimes not
all the fields will be filled. If a field is empty then the
assumption is that no filter will be applied to that field in the
query and should return all results within the limits of the other
field in the query.

How would I set up a query to take a value as a wildcard (*) if no
data is entered into the field?

Probably a simple answer but I think I'm currently suffering from
putty brain or something.

Thanks, Dean...

[King Ron]

On Jun 7, 3:16 pm, DeanL <[e-mail]:

Hi all,

I'm trying to set up a query that runs from a command button on a form
(simple enough so far), what I want the query to do is take values
from the fields on the form (seven fields in total) but sometimes not
all the fields will be filled. If a field is empty then the
assumption is that no filter will be applied to that field in the
query and should return all results within the limits of the other
field in the query.

How would I set up a query to take a value as a wildcard (*) if no
data is entered into the field?

Probably a simple answer but I think I'm currently suffering from
putty brain or something.

Thanks, Dean...

Here's some working code that filters a schedule of events (Course
Sections) on (optionally) Date/Time and (Optionally) an Instructor's
Department or ID. Basically I check the length of the form fields
("Me...") and if 0 skip appending the where clause onto the filter...

<compiled VBA>

Public Sub SetFilter()
Dim wkFilter As String
Dim wkDate As String
Dim wkTime As String
Dim wkDept As String
Dim wkInst As String

On Error GoTo ProcErr

Me.Filter = ""
Me.FilterOn = False

If (Len(Me.usSetDate) 0) Then
wkDate = CDate(DatePart("m", Me.usSetDate) & "/" & DatePart("d",
Me.usSetDate) & "/" & DatePart("yyyy", Me.usSetDate))
wkTime = CDate(DatePart("h", Me.usSetDate) & ":" & DatePart("n",
Me.usSetDate) & ":" & DatePart("s", Me.usSetDate) & IIf(Right$
(Me.usSetDate, 1) = "M", Right$(Me.usSetDate, 2), ""))

Select Case (wkTime)
Case "12:00:00 AM"
wkFilter = "([EventDate] = #" & wkDate & "#) AND "
Case Else
wkFilter = "([EventDate] = #" & wkDate & "#) AND " & _
"([EventStartTime] <= #" & wkTime & "#) AND " & _
"([EventEndTime] >= #" & wkTime & "#) AND "
End Select
End If

wkDept = Me.usSetDept
wkInst = Me.usSetInst

If (wkDept <"{ALL}") Then
If (Len(wkDept) 0) Then
wkFilter = wkFilter & "([Dept] = '" & wkDept & "') AND "
Else
wkFilter = wkFilter & "(IsNull([Dept]) = True) AND "
End If
End If

If (wkInst <"{ALL}") Then
If (Len(wkInst) 0) Then
wkFilter = wkFilter & "([Instructor] = '" & wkInst & "') AND "
Else
wkFilter = wkFilter & "(IsNull([Instructor]) = True) AND "
End If
End If

If (Len(wkFilter) 0) Then
Me.Filter = Mid$(wkFilter, 1, Len(wkFilter) - 5)
Me.FilterOn = True
End If

ProcExit:
Exit Sub

ProcErr:
Select Case Err
Case 0:
Resume Next
Case Else
Forms.frmMain.Prog 0, "SetFilter Error: " & Err & " " &
Err.Description & " " & Now()
Forms.frmMain.ErrLog "SetFilter", Err, Err.Description
Resume ProcExit
End Select
End Sub

</compiled VBA>

Hopefully there's not too much obscure crap in there to confuse yas.
In this sample, most of the the form fields are drop down lists of
distinct values in the Table.RecordSource UNIONed with the literal
"{ALL}" (the curly braces sort this value to the top of the lists). If
the field is {ALL} then that phrase of the filter is skipped.

Lucks to yas pal!

Ron, King of Chi

[Scott]

you need to put the field reference from the form in the query criteria i.e
forms!formname!fieldname to handle null vales you need to put
forms!formname!fieldname = null in the criteria. So for one query field you
will have two rows of crtieria one for the value the other for no value. If
you want to add a second query field with crteria no you will need four rows
of criteria, one for the value of field one and field two, the next for one
value and one null, another for it reversed and a fourth for both null.
Hopefully I explained it so you can understand

"DeanL" <de*************@yahoo.comwrote in message
news:11**********************@g37g2000prf.googlegr oups.com...

Hi all,

I'm trying to set up a query that runs from a command button on a form
(simple enough so far), what I want the query to do is take values
from the fields on the form (seven fields in total) but sometimes not
all the fields will be filled. If a field is empty then the
assumption is that no filter will be applied to that field in the
query and should return all results within the limits of the other
field in the query.

How would I set up a query to take a value as a wildcard (*) if no
data is entered into the field?

Probably a simple answer but I think I'm currently suffering from
putty brain or something.

Thanks, Dean...

[Bob Quintal]

"Scott" <a@awrote in
news:46**********************@news.aliant.net:

you need to put the field reference from the form in the query
criteria i.e forms!formname!fieldname

The first part is correct.

to handle null vales you

need to put forms!formname!fieldname = null in the criteria.

that is wrong. it needs to be
forms!formname!fieldname IS null

there is a difference between = and IS null.

So for one query field you will have two rows of crtieria one
for the value the other for no value. If you want to add a
second query field with crteria no you will need four rows of
criteria, one for the value of field one and field two, the
next for one value and one null, another for it reversed and a
fourth for both null. Hopefully I explained it so you can
understand

"DeanL" <de*************@yahoo.comwrote in message
news:11**********************@g37g2000prf.googlegr oups.com...

>Hi all,

I'm trying to set up a query that runs from a command button
on a form (simple enough so far), what I want the query to do
is take values from the fields on the form (seven fields in
total) but sometimes not all the fields will be filled. If a
field is empty then the assumption is that no filter will be
applied to that field in the query and should return all
results within the limits of the other field in the query.

How would I set up a query to take a value as a wildcard (*)
if no data is entered into the field?

Probably a simple answer but I think I'm currently suffering
from putty brain or something.

Thanks, Dean...

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[DeanL]

On Jun 7, 2:15 pm, Bob Quintal <[e-mail]:

"Scott" <a@awrote innews:46**********************@news.aliant.net:

you need to put the field reference from the form in the query
criteria i.e forms!formname!fieldname

The first part is correct.

to handle null vales you

need to put forms!formname!fieldname = null in the criteria.

that is wrong. it needs to be
forms!formname!fieldname IS null

there is a difference between = and IS null.

So for one query field you will have two rows of crtieria one
for the value the other for no value. If you want to add a
second query field with crteria no you will need four rows of
criteria, one for the value of field one and field two, the
next for one value and one null, another for it reversed and a
fourth for both null. Hopefully I explained it so you can
understand

"DeanL" <[e-mail] in message
news:11**********************@g37g2000prf.googlegr oups.com...

Hi all,

I'm trying to set up a query that runs from a command button
on a form (simple enough so far), what I want the query to do
is take values from the fields on the form (seven fields in
total) but sometimes not all the fields will be filled. If a
field is empty then the assumption is that no filter will be
applied to that field in the query and should return all
results within the limits of the other field in the query.

How would I set up a query to take a value as a wildcard (*)
if no data is entered into the field?

Probably a simple answer but I think I'm currently suffering
from putty brain or something.

Thanks, Dean...

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

Hi again guys,

The basis of my query is to show records filtered by any or all of the
following:

Cost (Between Min and Max values)
Fiscal Year
Award Date (Between Min and Max values)
Reviewer Name
Project Engineer

The problems start to arise when I select certain fields but need to
leave the date fields empty, how would I then return the values in the
query for ALL dates?

Most of the other fields have no problem substituting a wildcard
character for their value. This query is used to count the total
number of projects for a given Fiscal Year and then 2 other queries
are used, one to count those On Time and On Budget and a 3rd query to
count those not On Time On Budget.

Regards, Dean...

[Bob Quintal]

DeanL <de*************@yahoo.comwrote in
news:11**********************@d30g2000prg.googlegr oups.com:

On Jun 7, 2:15 pm, Bob Quintal <[e-mail]:

>"Scott" <a@awrote
innews:46**********************@news.aliant.net :

you need to put the field reference from the form in the
query criteria i.e forms!formname!fieldname

The first part is correct.

to handle null vales you

need to put forms!formname!fieldname = null in the
criteria.

that is wrong. it needs to be
forms!formname!fieldname IS null

there is a difference between = and IS null.

So for one query field you will have two rows of crtieria
one for the value the other for no value. If you want to
add a second query field with crteria no you will need four
rows of criteria, one for the value of field one and field
two, the next for one value and one null, another for it
reversed and a fourth for both null. Hopefully I explained
it so you can understand

"DeanL" <[e-mail] in message
news:1181247408.576800.313000

@g37g2000prf.googlegroups.com...

>Hi all,

>I'm trying to set up a query that runs from a command
button on a form (simple enough so far), what I want the
query to do is take values from the fields on the form
(seven fields in total) but sometimes not all the fields
will be filled. If a field is empty then the assumption
is that no filter will be applied to that field in the
query and should return all results within the limits of
the other field in the query.

>How would I set up a query to take a value as a wildcard
(*) if no data is entered into the field?

>Probably a simple answer but I think I'm currently
suffering from putty brain or something.

>Thanks, Dean...

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

Hi again guys,

The basis of my query is to show records filtered by any or
all of the following:

Cost (Between Min and Max values)
Fiscal Year
Award Date (Between Min and Max values)
Reviewer Name
Project Engineer

The problems start to arise when I select certain fields but
need to leave the date fields empty, how would I then return
the values in the query for ALL dates?

Most of the other fields have no problem substituting a
wildcard character for their value. This query is used to
count the total number of projects for a given Fiscal Year and
then 2 other queries are used, one to count those On Time and
On Budget and a 3rd query to count those not On Time On
Budget.

Regards, Dean...

the way I'd do this is to create the query programatically in
code.

the other way is to setup your criteria as follows:
cost:
between nz(Forms!myform!minimumcost,0) and nz(Forms!myForm!
maximumCost,99999999999)
Fiscal year:
like nz(Forms!myform!fiscalYear, "*")
Award Date:
Between nz(Forms!myForm!startdate,#01/01/1776#) and nz(Forms!
myForm!enddate,#31/12/9999#)

The last two should mimic fiscal year.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com