Limiting Date Range

[Zanstemic]

I'm trying to limit a report to a date range using

Between [Forms]![View Reports]![date_start] And [Forms]![View
Reports]![date_end]

This is working fine for a filed that has a date in the table. However, I
really need to have the date range based on the following resulting date and
also the "Not Yet Seen":

Report Due Date:
IIf(Registration!EventID=4,IIf(QueryPeer_StartDate!RegistrationDate Is
Null,"No Start
Date",DateAdd('ww',3,QueryPeerStartDate!RegistrationDate)),IIf(QueryVisitCompleteDate![MaxOfDate
Scheduled] Is Null,"Not Yet
Seen",(DateAdd('ww',2,QueryVisitCompleteDate![MaxOfDate Scheduled]))))

Any advice for an intermediate to novice level would be appreciated.

Should this result be calculated in a separate table field and not part of
the query? Or, Is there a way to limit the result to a date range (date_start
to date_end) based on this result?

Thanks in advance for the guidance and direction.

 

[Jeff Boyce]

It all starts with the (underlying) data. We aren't there -- we can't see
your data.

I have no idea what "Not Yet Seen" means in the context of a date range
question.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Zanstemic]

Good question. The "Not Yet Seen" is just textual meaning that no date exists.

The field in question is a date field if Seen, otherwise the text Not Yet
Seen shows in the field. It's a Text field that can contain either a date or
a String.

I would like to show everything that fits the Date Range and everything that
as th String "Not Yet Seen".

So a result looks something like

Date Seen
5/5/2004
5/7/2005
Not Yet Seen
5/2/2005
7/7/2005
8/5/2007
Not Yet Seen
4/6/2005
etc.

Does this explaination help?

It all starts with the (underlying) data. We aren't there -- we can't see
your data.

I have no idea what "Not Yet Seen" means in the context of a date range
question.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to limit a report to a date range using

Between [Forms]![View Reports]![date_start] And [Forms]![View
Reports]![date_end]

This is working fine for a filed that has a date in the table. However, I
really need to have the date range based on the following resulting date
and
also the "Not Yet Seen":

Report Due Date:
IIf(Registration!EventID=4,IIf(QueryPeer_StartDate!RegistrationDate Is
Null,"No Start
Date",DateAdd('ww',3,QueryPeerStartDate!RegistrationDate)),IIf(QueryVisitCompleteDate![MaxOfDate
Scheduled] Is Null,"Not Yet
Seen",(DateAdd('ww',2,QueryVisitCompleteDate![MaxOfDate Scheduled]))))

Any advice for an intermediate to novice level would be appreciated.

Should this result be calculated in a separate table field and not part of
the query? Or, Is there a way to limit the result to a date range
(date_start
to date_end) based on this result?

Thanks in advance for the guidance and direction.

 

[Jeff Boyce]

I'm still not clear on this...

It sounds like you have a field that can hold either a text string (e.g.,
"Not Seen Yet") or a "date".

I'm not aware of a single data type in Access that can store either text or
date/time values. I'm guessing that you are storing a text string that YOU
think of as a date. But unless you tell Access that the string (e.g.,
"5/5/2004") is actually a date value, Access won't know that.

Or am I reading too much between the lines?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Good question. The "Not Yet Seen" is just textual meaning that no date
exists.

The field in question is a date field if Seen, otherwise the text Not Yet
Seen shows in the field. It's a Text field that can contain either a date
or
a String.

I would like to show everything that fits the Date Range and everything
that
as th String "Not Yet Seen".

So a result looks something like

Date Seen
5/5/2004
5/7/2005
Not Yet Seen
5/2/2005
7/7/2005
8/5/2007
Not Yet Seen
4/6/2005
etc.

Does this explaination help?

It all starts with the (underlying) data. We aren't there -- we can't
see
your data.

I have no idea what "Not Yet Seen" means in the context of a date range
question.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to limit a report to a date range using

Between [Forms]![View Reports]![date_start] And [Forms]![View
Reports]![date_end]

This is working fine for a filed that has a date in the table. However,
I
really need to have the date range based on the following resulting
date
and
also the "Not Yet Seen":

Report Due Date:
IIf(Registration!EventID=4,IIf(QueryPeer_StartDate!RegistrationDate Is
Null,"No Start
Date",DateAdd('ww',3,QueryPeerStartDate!RegistrationDate)),IIf(QueryVisitCompleteDate![MaxOfDate
Scheduled] Is Null,"Not Yet
Seen",(DateAdd('ww',2,QueryVisitCompleteDate![MaxOfDate Scheduled]))))

Any advice for an intermediate to novice level would be appreciated.

Should this result be calculated in a separate table field and not part
of
the query? Or, Is there a way to limit the result to a date range
(date_start
to date_end) based on this result?

Thanks in advance for the guidance and direction.

 

[Zanstemic]

You are correct. It is just a text field that contains either "Not Yet Seen"
or numbers and slashes in that field.

So I'm anticipating now that the text field cannot use the -- Between
[Forms]![View Reports]![date_start] And [Forms]![View Reports]![date_end]s --

I could setup a hidden field that is only an actual date field that instead
of having "Not Yet Seen", it can be a Null value. Than I might be able to use
Between and Is Null to give the desired result?

 

[Jeff Boyce]

While a text field is more difficult to use with the Between ... And ...,
you could still (theoretically) compare what you consider "dates", but only
if they were in a form that lends itself to sorting correctly. The one I'm
familiar with is a "yyyymmdd" format (e.g., 20080108), which can be sorted
and can be searched Between ... And ...

If you have a field that has a text representation of a date, another
approach would be to convert that text to an actual date in the query, then
use your Between ... And ... criteria against that converted value instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

You are correct. It is just a text field that contains either "Not Yet
Seen"
or numbers and slashes in that field.

So I'm anticipating now that the text field cannot use the -- Between
[Forms]![View Reports]![date_start] And [Forms]![View
Reports]![date_end]s --

I could setup a hidden field that is only an actual date field that
instead
of having "Not Yet Seen", it can be a Null value. Than I might be able to
use
Between and Is Null to give the desired result?

I'm trying to limit a report to a date range using

Between [Forms]![View Reports]![date_start] And [Forms]![View
Reports]![date_end]

This is working fine for a filed that has a date in the table. However, I
really need to have the date range based on the following resulting date
and
also the "Not Yet Seen":

Report Due Date:
IIf(Registration!EventID=4,IIf(QueryPeer_StartDate!RegistrationDate Is
Null,"No Start
Date",DateAdd('ww',3,QueryPeerStartDate!RegistrationDate)),IIf(QueryVisitCompleteDate![MaxOfDate
Scheduled] Is Null,"Not Yet
Seen",(DateAdd('ww',2,QueryVisitCompleteDate![MaxOfDate Scheduled]))))

Any advice for an intermediate to novice level would be appreciated.

Should this result be calculated in a separate table field and not part
of
the query? Or, Is there a way to limit the result to a date range
(date_start
to date_end) based on this result?

Thanks in advance for the guidance and direction.