Query Based On "yes/no" Switch in Data Base Table

[Tom]

John,
So this begs the question of how to "serve two masters".
What is my next step in the redesign. Bare in mind that
I am very new to this.
The form is set up to enter customer information
including
1. a start date (for the service)
2. an end date (to the service)
3 days of the week the service will be supplied.
The table this is based on currently has
1. StartDate
2. EndDate
3. days of the week (data type "yes/no")
Any help you can give will be greatly appreciated.
Tom

 

[Tom]

John,
P.S. concerning the days of the week, the possibility
of haveing more than one day of the week
for service is what led me to
the check boxes.
Tom

 

[Wayne Morgan]

Unless the check boxes are set for Triple State, you may need to set them to
False, not Null.

 

[Tom]

Wayne,
I have no clue what "Triple State" is. Next question is
which one will serve my purpose. Then, what do I need
to change? How do I change it? And will the end result
fullfill my query requirements?
Tom

 

[Wayne Morgan]

Tom,

I believe there is a logic problem in the criteria. John has already pointed
out the "Form" instead of "Forms" problem.

The logic problem I believe is here.
((ScheduledRuns.Sunday)=[Form]![ScheduledRuns]![Sunday])) OR
(((ScheduledRuns.Monday)=[Form]![ScheduledRuns]![Monday])) OR
(((ScheduledRuns.Tuesday)=[Form]![ScheduledRuns]![Tuesday])) OR
(((ScheduledRuns.Wednesday)=[Form]![ScheduledRuns]![Wednesday])) OR
(((ScheduledRuns.Thursday)=[Form]![ScheduledRuns]![Thursday])) OR
(((ScheduledRuns.Friday)=[Form]![ScheduledRuns]![Friday])) OR
(((ScheduledRuns.Saturday)=[Form]![ScheduledRuns]![Saturday]));

If ScheduledRuns.Sunday is False and the checkbox is False, then that record
will be returned. If I'm understanding correctly, you only want the record
returned if the checkbox is checked (True). Using a Triple State checkbox
and having them Null for not selected, as your previous code set them, may
help because the record in the table is probably True or False, so Null
won't match. However, what might be easier is creating the SQL in the code.
Check each checkbox and only include lines for the checkboxes that are True.
You would then assign the SQL you build to the SQL property of the query.
This will change the query. Once you've done that, open the report and it
will use the new SQL in the query.

Example:
Dim intTrueCount As Integer
strSQL = "SELECT ScheduledRuns.PtLastName, ScheduledRuns.StartDate, " & _
"ScheduledRuns.EndDate, ScheduledRuns.ApptTime, ScheduledRuns.ScheduledTime,
" & _
"ScheduledRuns.TrFacility, ScheduledRuns.RecFacility, ScheduledRuns.Sunday,
" & _
"ScheduledRuns.Monday, ScheduledRuns.Tuesday, ScheduledRuns.Wednesday, " & _
"ScheduledRuns.Thursday, ScheduledRuns.Friday, ScheduledRuns.Saturday " & _
"FROM ScheduledRuns " & _
"WHERE (((ScheduledRuns.StartDate) Between IIf(Date()<[EndDate],Date()) And
" & _
"[StartDate]) AND ((ScheduledRuns.EndDate) Between " & _
"IIf(Date()>[StartDate],Date()) And [EndDate]) AND "

If Me.chkSunday = True Then
strSQL = strSQL & "ScheduledRuns.Sunday = True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkMonday = True Then
strSQL = strSQL & "ScheduledRuns.Monday= True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkTuesday = True Then
strSQL = strSQL & "ScheduledRuns.Tuesday= True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkWednesday = True Then
strSQL = strSQL & "ScheduledRuns.Wednesday= True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkThursday = True Then
strSQL = strSQL & "ScheduledRuns.Thursday= True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkFriday = True Then
strSQL = strSQL & "ScheduledRuns.Friday= True And "
intTrueCount = intTrueCount + 1
End If
If Me.chkSaturday = True Then
strSQL = strSQL & "ScheduledRuns.Saturday= True And "
intTrueCount = intTrueCount + 1
End If
If intTrueCount = 0 Then
Msgbox "No Selections Made"
Exit Sub
End If
'Remove the trailing "AND "
strSQL = Left(strSQL, Len(strSQL) - 4) & ";"
CurrentDb.Queydefs("NameOfQuery").SQL = strSQL
DoCmd.OpenReport "ScheduledRunsTodayList", acPreview

Of course, adjust all names to the correct names for your fields and form
controls. You won't need to refer to the form in the SQL (query) because you
will be providing the desired value (True) directly. This is possible since
you are dynamically rewriting the query as needed. The only days of the week
that will be included in the query are those that are checked.

--
Wayne Morgan
MS Access MVP

I found the first problem, checkbox control name, but have not fixed it
yet.

Name of Form: ScheduledRuns
Name of each checkbox control:
Sun
Mon
Tue
Wed
Thu
Fri
Sat
SQL Statement:
SELECT ScheduledRuns.PtLastName, ScheduledRuns.StartDate,
ScheduledRuns.EndDate, ScheduledRuns.ApptTime,
ScheduledRuns.ScheduledTime,
ScheduledRuns.TrFacility, ScheduledRuns.RecFacility, ScheduledRuns.Sunday,
ScheduledRuns.Monday, ScheduledRuns.Tuesday, ScheduledRuns.Wednesday,
ScheduledRuns.Thursday, ScheduledRuns.Friday, ScheduledRuns.Saturday
FROM ScheduledRuns
WHERE (((ScheduledRuns.StartDate) Between IIf(Date()<[EndDate],Date()) And
[StartDate]) AND ((ScheduledRuns.EndDate) Between
IIf(Date()>[StartDate],Date()) And [EndDate]) AND
((ScheduledRuns.Sunday)=[Form]![ScheduledRuns]![Sunday])) OR
(((ScheduledRuns.Monday)=[Form]![ScheduledRuns]![Monday])) OR
(((ScheduledRuns.Tuesday)=[Form]![ScheduledRuns]![Tuesday])) OR
(((ScheduledRuns.Wednesday)=[Form]![ScheduledRuns]![Wednesday])) OR
(((ScheduledRuns.Thursday)=[Form]![ScheduledRuns]![Thursday])) OR
(((ScheduledRuns.Friday)=[Form]![ScheduledRuns]![Friday])) OR
(((ScheduledRuns.Saturday)=[Form]![ScheduledRuns]![Saturday]));

I appologize for not informing you of the other part of the query. This
limits the
reports to dates between the "StartDate" and "EndDate" but >= today.
That part needs to be there and it is working fine.

Tom

ok, first let's fix your code. it opens the report twice. take out one of
the two DoCmd.OpenReport lines. if you want to *see* the report rather
than
immediately print it, take out the second line.

next, sounds like your form references are incorrect, because you should
not
be getting any message boxes at all when you click the command button.
please tell us the name of your form - the name that shows on the list of
forms on the Form "tab" in the database window. and tell us the name of
each
checkbox control on the form - in form design, click on a checkbox
control
and, in the Properties box, click on the Other tab, and look at the Name
property.

next, open your query in design view. on the menu bar, click View | SQL
View. higlight the *whole* SQL statement from beginning to end, copy it,
and
paste it into your post so we can look at it.

hth

 

[tina]

okay, i've read through all the subsequent responses in this thread. so you
already know that you need to change your query criteria references from

((ScheduledRuns.Sunday)=[Form]![ScheduledRuns]![Sunday]))

to

((ScheduledRuns.Sunday)=[Forms]![ScheduledRuns]![Sun]))

with the appropriate correction for each day of the week, as well as the
Form to Forms correction. hopefully you've done that already and it has
worked for you. i agree with Wayne's assessment of True/False checkboxes,
except that i assumed that the checkbox controls on your form are unbound
(no field name in the controls' ControlSource property). if that's the case,
then resetting the controls to Null, after the report runs, should work
fine - as in the code i gave you. an unbound textbox control is neither True
nor False, until you click in it. once you make it True, clicking will only
make it False; but setting its' value to Null in code will make it again
neither True nor False.

i also agree with John's assessment - it's essentially the same thing i told
you in my first response in this thread. because you may need to indicate
more than one day of the week for a single record in the ScheduledRuns
table, in order to normalize your table design you would put the "scheduled
run days" in a "child" table, as

tblScheduledRuns
SchedRunID (presumably you already have a primary key field in the table, it
goes here)
PtLastName
StartDate
EndDate
ApptTime
ScheduledTime
TrFacility
RecFacility

tblScheduledRunDays
SchedRunID (foreign key from tblScheduledRuns)
DayOfWeek (do NOT call this field "Day" because that's a Reserved word in
Access)
the two fields above are used as a combination primary key, since any one
record in tblScheduledRuns will have any one day of the week listed only
once. if, for example, you have three records in tblScheduledRuns, with
primary key values of 1, 2, and 3, then the records in tblScheduledRunDays
might look like the following, as

tblScheduledRunDays
SchedRunID DayOfWeek
1 Monday
1 Thursday
2 Wednesday
3 Monday
3 Tuesday
3 Saturday

as you can see, one record for each day that applies to a specific scheduled
run record.

hth

I found the first problem, checkbox control name, but have not fixed it yet.

Name of Form: ScheduledRuns
Name of each checkbox control:
Sun
Mon
Tue
Wed
Thu
Fri
Sat
SQL Statement:
SELECT ScheduledRuns.PtLastName, ScheduledRuns.StartDate,
ScheduledRuns.EndDate, ScheduledRuns.ApptTime, ScheduledRuns.ScheduledTime,
ScheduledRuns.TrFacility, ScheduledRuns.RecFacility, ScheduledRuns.Sunday,
ScheduledRuns.Monday, ScheduledRuns.Tuesday, ScheduledRuns.Wednesday,
ScheduledRuns.Thursday, ScheduledRuns.Friday, ScheduledRuns.Saturday
FROM ScheduledRuns
WHERE (((ScheduledRuns.StartDate) Between IIf(Date()<[EndDate],Date()) And
[StartDate]) AND ((ScheduledRuns.EndDate) Between
IIf(Date()>[StartDate],Date()) And [EndDate]) AND
((ScheduledRuns.Sunday)=[Form]![ScheduledRuns]![Sunday])) OR
(((ScheduledRuns.Monday)=[Form]![ScheduledRuns]![Monday])) OR
(((ScheduledRuns.Tuesday)=[Form]![ScheduledRuns]![Tuesday])) OR
(((ScheduledRuns.Wednesday)=[Form]![ScheduledRuns]![Wednesday])) OR
(((ScheduledRuns.Thursday)=[Form]![ScheduledRuns]![Thursday])) OR
(((ScheduledRuns.Friday)=[Form]![ScheduledRuns]![Friday])) OR
(((ScheduledRuns.Saturday)=[Form]![ScheduledRuns]![Saturday]));

I appologize for not informing you of the other part of the query. This
limits the
reports to dates between the "StartDate" and "EndDate" but >= today.
That part needs to be there and it is working fine.

Tom

ok, first let's fix your code. it opens the report twice. take out one of
the two DoCmd.OpenReport lines. if you want to *see* the report rather than
immediately print it, take out the second line.

next, sounds like your form references are incorrect, because you should not
be getting any message boxes at all when you click the command button.
please tell us the name of your form - the name that shows on the list of
forms on the Form "tab" in the database window. and tell us the name of each
checkbox control on the form - in form design, click on a checkbox control
and, in the Properties box, click on the Other tab, and look at the Name
property.

next, open your query in design view. on the menu bar, click View | SQL
View. higlight the *whole* SQL statement from beginning to end, copy it, and
paste it into your post so we can look at it.

hth

 

[Wayne Morgan]

Triple State lets a checkbox be False (unchecked), True (checked), or Null
(gray center). It is one of the properties of the checkbox control.

I made a mistake in the code I gave you, the ANDs should be ORs as Tina had
suggested. Using this, the Triple State shouldn't matter. If the checkbox
isn't True (checked) the query won't event deal with it.

Amended part of the code:
"IIf(Date()>[StartDate],Date()) And [EndDate]) AND ("

If Me.chkSunday = True Then
strSQL = strSQL & "ScheduledRuns.Sunday = True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkMonday = True Then
strSQL = strSQL & "ScheduledRuns.Monday= True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkTuesday = True Then
strSQL = strSQL & "ScheduledRuns.Tuesday= True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkWednesday = True Then
strSQL = strSQL & "ScheduledRuns.Wednesday= True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkThursday = True Then
strSQL = strSQL & "ScheduledRuns.Thursday= True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkFriday = True Then
strSQL = strSQL & "ScheduledRuns.Friday= True Or "
intTrueCount = intTrueCount + 1
End If
If Me.chkSaturday = True Then
strSQL = strSQL & "ScheduledRuns.Saturday= True Or "
intTrueCount = intTrueCount + 1
End If
If intTrueCount = 0 Then
Msgbox "No Selections Made"
Exit Sub
End If
'Remove the trailing "Or "
strSQL = Left(strSQL, Len(strSQL) - 3 & ");"

I believe the code will do what you want. It would go in the button's Click
event before the opening of the report. You can see in the code where I set
the report to open. Replace the lines in the first code message with their
matching lines above.