Help: Using a Checkbox to Control a Date's Value

[Megan]

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

[Chris Tyson]

This is how I did it Megan, though being new to this I cannot for the life
of me think why your approach did not work, seems sensible to me...

Anyway,

1) I presume your query has InDate and OutDate columns.
2) I added a third calculated column to the query, 'Status', built as
follows: IIf([OutDate] Is Null,-1,0)
3) I then added a criteria to this 'Status' column thus:
[Forms]![frmStatus]![Completed]

As such, the value of the third column will be set to '0' (checkbox
unticked) or '-1' (checkbox ticked), and this is tested against the
'Completed' value on your form.

Hope this helps, worked for me,

Chris
"Megan" <me************ **@hotmail.com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

[Chris Tyson]

This is how I did it Megan, though being new to this I cannot for the life
of me think why your approach did not work, seems sensible to me...

Anyway,

1) I presume your query has InDate and OutDate columns.
2) I added a third calculated column to the query, 'Status', built as
follows: IIf([OutDate] Is Null,-1,0)
3) I then added a criteria to this 'Status' column thus:
[Forms]![frmStatus]![Completed]

As such, the value of the third column will be set to '0' (checkbox
unticked) or '-1' (checkbox ticked), and this is tested against the
'Completed' value on your form.

Hope this helps, worked for me,

Chris
"Megan" <me************ **@hotmail.com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

[Chris Tyson]

This is how I did it Megan, though being new to this I cannot for the life
of me think why your approach did not work, seems sensible to me...

Anyway,

1) I presume your query has InDate and OutDate columns.
2) I added a third calculated column to the query, 'Status', built as
follows: IIf([OutDate] Is Null,-1,0)
3) I then added a criteria to this 'Status' column thus:
[Forms]![frmStatus]![Completed]

As such, the value of the third column will be set to '0' (checkbox
unticked) or '-1' (checkbox ticked), and this is tested against the
'Completed' value on your form.

Hope this helps, worked for me,

Chris

"Megan" <me************ **@hotmail.com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

[Don Leverton]

Hi Megan

You can't use "Is Null" or "Is Not Null" to *set* a value to Null.

These operators are used to *test* a field or control's value.

For example:
This query will return all records that do NOT have an OutDate:
SELECT tblStatus.*
FROM tblStaus
WHERE (((tblStaus.Out Date) Is Null));

This query will return all records that DO have an OutDate:
SELECT tblStatus.*
FROM tblStaus
WHERE (((tblStaus.Out Date) Is Not Null));

It sounds like what you want to do is use the "Completed" checkbox control's
After Update event
to test and set the value of "OutDate", something like this:

(AirCode)
*************** *************** *********
Private Sub Completed_After Update()

If Me![Completed] = True Then
Me![OutDate] = Date()
Else
Me![OutDate] = Null
End If

End Sub
*************** *************** *********

HTH,
Don
"Megan" <me************ **@hotmail.com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

[Pieter Linden]

me************* *@hotmail.com (Megan) wrote in message news:<5c******* *************** ****@posting.go ogle.com>...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan

Easiest way to do this is with code behind a button on your form.
If ([forms]![frmStatus]![Completed]=True Then
'open the first query
DoCmd.OpenQuery "qryComplet ed"
Else
'open the second query
DoCmd.Openquery "qryIncompl ete"
End If

Alternatively, create a report to show the data you want (NOT
filtered), and then pass the filter to it when you open the report.

[Megan]

Thank-you eveyone for the ideas, suggestions, and code! I figured out
an alternate way to do what I wanted. Check it out:

I used this code behind the On Click Event of my Print Preview Command
Button.

............... ............... ............... ............... ............... ....

Dim stDocName As String
Dim stCompleted As String
Dim stNotCompleted As String

stDocName = "MyReport"

If Me.Completed.Va lue = True Then
stCompleted = "[OutDate] Is Not Null"
DoCmd.OpenRepor t stDocName, acViewPreview, , stCompleted
ElseIf Me.Completed.Va lue = False Then
stNotCompleted = "[OutDate] = Is Null"
DoCmd.OpenRepor t stDocName, acViewPreview, , stNotCompleted
End If

............... ............... ............... ............... ............... ....

It worked out perfectly for me although I'm not totally sure why. I'm
still new with VBA so I'm not really familiar with all of the
commands.

Thanks again everyone!

Megan
me************* *@hotmail.com (Megan) wrote in message news:<5c******* *************** ****@posting.go ogle.com>...

Hi everybody-

I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."

I have 2 date fields: InDate and OutDate. If there is an OutDate, then
the record is finished. If there is no OutDate, then the record is not
finished.

I'm using an unbound form (frmStatus) to enter data into a query to
generate a report.

I have 2 unbound text boxes for the dates: txtInDate and txtOutDate. I
use these dates in the criteria for my InDate. I might enter the date
range: 1/1/04 to 7/1/04. My query retrieves all records with an InDate
between those dates.

I know that some of my records have null OutDates- a date hasn't been
entered yet. For instance some records have InDates and OutDates, and
some records have InDates and no OutDates.

If a record has an InDate and OutDate, then it is considered
"Completed. " If a record has an InDate and no OutDate, then it is
considered "Not Completed."

I have a checkbox on my unbound form named, "Completed. "

If I check the checkbox, then I want the query to return records where
there is an OutDate. If the checkbox is not checked, then I want the
query to return records with no OutDate.

Here is a example of my table, tblStatus:
RecordID
InDate
OutDate

Here is the criteria of the OutDate field in my query:

IIf([forms]![frmStatus]![Completed]=True,([tblStatus].[OutDate]) Is
Not Null,([tblStatus].[OutDate]) Is Null)
This doesn't work. So, I'm wondering if this possible to do with a
checkbox?

thanks!

Megan