By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.google.c om...
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

Nov 13 '05 #2

P: n/a
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.google.c om...
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

Nov 13 '05 #3

P: n/a
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.google.c om...
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

Nov 13 '05 #4

P: n/a
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.OutDate) Is Null));

This query will return all records that DO have an OutDate:
SELECT tblStatus.*
FROM tblStaus
WHERE (((tblStaus.OutDate) 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_AfterUpdate()

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.google.c om...
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

Nov 13 '05 #5

P: n/a
me**************@hotmail.com (Megan) wrote in message news:<5c**************************@posting.google. 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 "qryCompleted"
Else
'open the second query
DoCmd.Openquery "qryIncomplete"
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.
Nov 13 '05 #6

P: n/a
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.Value = True Then
stCompleted = "[OutDate] Is Not Null"
DoCmd.OpenReport stDocName, acViewPreview, , stCompleted
ElseIf Me.Completed.Value = False Then
stNotCompleted = "[OutDate] = Is Null"
DoCmd.OpenReport 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.google. 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

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.