Calculated field

[Aless]

I have a table which includes 2 fields - a date field called ArriveDate and
a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days to give
departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates. Therefore
in the crietria of the calculated field I have put in "Between [Departure
date From:] And [Departure date To:]". But when I run the query, ALL the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate it if you
can point me in the right direction.

Is it possible to have a calculated field in a table which is based on the
result of other fields in the same table?

Al

 

[Allen Browne]

Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

 

[Aless]

I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does not do the
job.

Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called ArriveDate and
a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days to give
departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates. Therefore
in the crietria of the calculated field I have put in "Between [Departure
date From:] And [Departure date To:]". But when I run the query, ALL the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate it if you
can point me in the right direction.

Is it possible to have a calculated field in a table which is based on the
result of other fields in the same table?

Al

 

[Allen Browne]

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does not do the
job.

Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called

ArriveDate
and

a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days to give
departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates. Therefore
in the crietria of the calculated field I have put in "Between [Departure
date From:] And [Departure date To:]". But when I run the query, ALL the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate it

if
you

can point me in the right direction.

Is it possible to have a calculated field in a table which is based on the
result of other fields in the same table?

Al

 

[Aless]

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does not do the
job.

Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called ArriveDate
and
a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days

to
give

departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between [Departure
date From:] And [Departure date To:]". But when I run the query,

ALL
the

records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate it if
you
can point me in the right direction.

Is it possible to have a calculated field in a table which is based

on
the

result of other fields in the same table?

Al

 

[Allen Browne]

If you want only the records where both dates are the same:

1. Create a query into this table.

2. In the Criteria row under your [Arrival Date], enter:
[Enter departure from:]

3. In the Field row, enter this field:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

4. In the Criteria row under the calculated field:
[Enter departure to:]

BTW, be sure to declare your two parameters so Access knows they are the
Date/Time data type. (Parameters on Query menu). More info on preventing
misunderstandings in calculated fields:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does not

do
the

job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called ArriveDate
and
a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days to
give
departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the query, ALL
the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate

it
if

you
can point me in the right direction.

Is it possible to have a calculated field in a table which is

based

on

 

[Aless]

I regret that this does not work - Access throws up a message "This
expression is typed incorrectly or it is too complex to be evaluated. For
example numeric expression may contain too many complicated elements. etc".

The DepartDate field in 3 below works out the departure date correctly.
However, the date entered in 2 would appear to be selecting records by
arrival date if it matches the "Enter departure from" date - this is not
what I am after. I am after records where guests DEPART (= Arrival Date
plus Days Stayed) on ANY date which falls within the two dates specified by
the user.

I thought there may be an easy solution to this but clearly this is not the
case. I appreciate the time you have taken to help me and hope we can find
a solution.

I assume the two parameters that need to be declared are Enter departure
from and Enter departure to?

Al

If you want only the records where both dates are the same:

1. Create a query into this table.

2. In the Criteria row under your [Arrival Date], enter:
[Enter departure from:]

3. In the Field row, enter this field:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

4. In the Criteria row under the calculated field:
[Enter departure to:]

BTW, be sure to declare your two parameters so Access knows they are the
Date/Time data type. (Parameters on Query menu). More info on preventing
misunderstandings in calculated fields:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it

overlaps
the

2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does

not

do

the
job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called
ArriveDate
and
a numerical field called Days. I have made up a query with a
calculated
field (formatted as date field) which adds the ArriveDate and

Days
to

give
departure date. What I want the query to do is let a user key

in
two

departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the

query,
ALL

the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would

appreciate

it based

 

[John Viescas]

PMJI.

You need to add a DepartureDate calculated field to your query:

DepartureDate: Bookings.[Arrival Date] + Bookings.[Days Stayed]

Under [Arrival Date] on the Criteria line, enter:

<= [Enter Departure To:]

Under your new calculated field, enter:

= [Enter Departure From:]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does not

do
the

job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called ArriveDate
and
a numerical field called Days. I have made up a query with a calculated
field (formatted as date field) which adds the ArriveDate and Days to
give
departure date. What I want the query to do is let a user key in two
departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the query, ALL
the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate

it
if

you
can point me in the right direction.

Is it possible to have a calculated field in a table which is

based

on

 

[Allen Browne]

Hi Al.

This sentence has clarified it for me:
"I am after records where guests DEPART (= Arrival Date plus Days Stayed) on
ANY date which falls within the two dates specified by the user."

In the Field row of your query:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

In the Criteria row beneath this field:
Between [Enter departure from:] And [Enter departure to:]

Yes. The two parameters to declare are:
[Enter departure from:] Date/Time
[Enter departure to:] Date/Time

 

[Aless]

I am afraid this will not give the right answer. The criteria under Arrival
Date will select records by arrival date NOT departure date.

I am after records where guests DEPART on ANY date which falls within the
two dates specified by the user.

Al

PMJI.

You need to add a DepartureDate calculated field to your query:

DepartureDate: Bookings.[Arrival Date] + Bookings.[Days Stayed]

Under [Arrival Date] on the Criteria line, enter:

<= [Enter Departure To:]

Under your new calculated field, enter:

= [Enter Departure From:]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al

So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it

overlaps
the

2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does

not

do

the
job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called
ArriveDate
and
a numerical field called Days. I have made up a query with a
calculated
field (formatted as date field) which adds the ArriveDate and

Days
to

give
departure date. What I want the query to do is let a user key

in
two

departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the

query,
ALL

the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would

appreciate

it based

 

[John Viescas]

Right. Like Allen Browne, I misread the question to begin with. His last
post is the correct one. My solution shows all rows where part or all of
the stay falls within or across the two prompted dates. I see folks
struggling all the time with a predicate to find matches across two date
spans, so I posted my "knee jerk" answer that is really quite simple.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am afraid this will not give the right answer. The criteria under Arrival
Date will select records by arrival date NOT departure date.

I am after records where guests DEPART on ANY date which falls within the
two dates specified by the user.

Al

PMJI.

You need to add a DepartureDate calculated field to your query:

DepartureDate: Bookings.[Arrival Date] + Bookings.[Days Stayed]

Under [Arrival Date] on the Criteria line, enter:

<= [Enter Departure To:]

Under your new calculated field, enter:

= [Enter Departure From:]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I think I need to explain the problem a little better as it looks like we
may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al


So, if you ask for the records that "fall between Feb 2 and Feb 20", which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps
the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it
overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
I would like the query to select any record which falls within the 2
specified Departure dates but the query as I have created it does

not

do
the
job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called
ArriveDate
and
a numerical field called Days. I have made up a query with a
calculated
field (formatted as date field) which adds the ArriveDate and Days
to
give
departure date. What I want the query to do is let a user key in
two
departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the query,
ALL
the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would

appreciate

it
if
you
can point me in the right direction.

Is it possible to have a calculated field in a table which is based
on
the
result of other fields in the same table?

Al

 

[Aless]

Hi Allen

I was sure what you suggested would solve the problem but unfortuantely
Access does not like it - I get the same message as before, namely, "This
expression is typed incorrectly or it is too complex to be evaluated. For
example numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables
".

I have double checked everything so typing error is unlikely. If I remove
the criteria and the parameters declared, all the records are displayed with
the departure dates. I can't see why the Between ..And criteria is too
complex to evaluate.

Al

 

[Aless]

Hi John

Please have a look at my response to Allen Browne's suggestion. I thought
it would work but there seems to be a problem with this!

Al

Right. Like Allen Browne, I misread the question to begin with. His last
post is the correct one. My solution shows all rows where part or all of
the stay falls within or across the two prompted dates. I see folks
struggling all the time with a predicate to find matches across two date
spans, so I posted my "knee jerk" answer that is really quite simple.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am afraid this will not give the right answer. The criteria under Arrival
Date will select records by arrival date NOT departure date.

I am after records where guests DEPART on ANY date which falls within the
two dates specified by the user.

Al

PMJI.

You need to add a DepartureDate calculated field to your query:

DepartureDate: Bookings.[Arrival Date] + Bookings.[Days Stayed]

Under [Arrival Date] on the Criteria line, enter:

<= [Enter Departure To:]

Under your new calculated field, enter:

= [Enter Departure From:]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I think I need to explain the problem a little better as it looks

like
we

may be at cross puposes here.

Table contains (amonst others) Arrival Date and Days Stayed fields.
Adding the two gives the Departure Date but this field is not in the table
(presumably this will be a calculated field in the query)

I would like to create a query where:
a) User is prompted to enter two dates.
b) Query then produces a result set of records where (Arrival Date + Days
Stayed) equals any date between the two dates, inclusive of both dates.

I hope this clarifies the problem.

I have tried the following criteria in the Arrival Date field but Access
doesn't like it:
Between [Enter departure from:] + [Bookings]![Days Stayed] And [Enter
departure to:] + [Bookings]![Days Stayed]

Al


So, if you ask for the records that "fall between Feb 2 and Feb 20",
which
of the following cases should be included in the result set?

a) a record that starts on Feb 3 and ends on Feb 4 (because it falls
completely between the 2 dates)?

b) a record that starts on Feb 3 and ends on Feb 22 (because it overlaps
the
2 dates partly, even though it is not completely between them)?

c) a record that starts on January 1 and ends on Feb 3 (because it
overlaps
the 2 dates partly, even though it is not completely between them?

d) a record that starts on January 1 and ends on March 1 (because it
encompasses the dates completely and includes other dates beyond your
criteria as well)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
I would like the query to select any record which falls within

the

2

specified Departure dates but the query as I have created it

does
not

do
the
job.


Your design is good. Do not add a 3rd field for the departure date.

Not sure exactly what you are after in your query.
Do you want:
a) only records that have exactly the arrival and departure date
specified;
b) any record that overlaps with the specified dates at all;
c) any record that falls completely within the specified dates;
d) something else.

The basic logic is that two events overlap if:
- A starts before B ends, AND
- B starts before A ends.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which includes 2 fields - a date field called
ArriveDate
and
a numerical field called Days. I have made up a query with a
calculated
field (formatted as date field) which adds the ArriveDate

and
Days

to
give
departure date. What I want the query to do is let a user

key
in

two
departure dates and select records which fall within these dates.
Therefore
in the crietria of the calculated field I have put in "Between
[Departure
date From:] And [Departure date To:]". But when I run the query,
ALL
the
records are selected. What am I doing wrong?

I am sure there is a simple solution to this and would appreciate
it
if
you
can point me in the right direction.

Is it possible to have a calculated field in a table which is
based
on
the
result of other fields in the same table?

Al

 

[John Viescas]

Did you explicitly declare both parameters? If so, you could try this:

= [Enter departure from:] And <= [Enter departure to:]

You could also try simplifying the expression:

DepartDate: [Arrival Date] + [Days Stayed]

Are the arrival date and days stayed values all valid? If, for example, any
rows contain a Null, Allen's suggestion to use CVDate and DateAdd will cause
an error because DateAdd won't accept null values.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi Allen

I was sure what you suggested would solve the problem but unfortuantely
Access does not like it - I get the same message as before, namely, "This
expression is typed incorrectly or it is too complex to be evaluated. For
example numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables
".

I have double checked everything so typing error is unlikely. If I remove
the criteria and the parameters declared, all the records are displayed with
the departure dates. I can't see why the Between ..And criteria is too
complex to evaluate.

Al

Hi Al.

This sentence has clarified it for me:
"I am after records where guests DEPART (= Arrival Date plus Days

Stayed)

on
ANY date which falls within the two dates specified by the user."

In the Field row of your query:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

In the Criteria row beneath this field:
Between [Enter departure from:] And [Enter departure to:]

Yes. The two parameters to declare are:
[Enter departure from:] Date/Time
[Enter departure to:] Date/Time

 

[Allen Browne]

Aless, double-check the spelling of your fields and parameters, looking for
details such as extra/missing spaces.

If you cannot see the problem, switch the query to SQL View, copy the SQL
statement, and post it here.

John's comment about the Null problem refers to the 2nd argument of
DateAdd(), which does need Nz() if [Days Stayed] is not a required field:
DepartDate: CVDate(DateAdd("d", Nz([Days Stayed],0), [Arrival Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Did you explicitly declare both parameters? If so, you could try this:

= [Enter departure from:] And <= [Enter departure to:]

You could also try simplifying the expression:

DepartDate: [Arrival Date] + [Days Stayed]

Are the arrival date and days stayed values all valid? If, for example, any
rows contain a Null, Allen's suggestion to use CVDate and DateAdd will cause
an error because DateAdd won't accept null values.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi Allen

I was sure what you suggested would solve the problem but unfortuantely
Access does not like it - I get the same message as before, namely, "This
expression is typed incorrectly or it is too complex to be evaluated. For
example numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables
".

I have double checked everything so typing error is unlikely. If I remove
the criteria and the parameters declared, all the records are displayed with
the departure dates. I can't see why the Between ..And criteria is too
complex to evaluate.

Al

Hi Al.

This sentence has clarified it for me:
"I am after records where guests DEPART (= Arrival Date plus Days

Stayed)

on
ANY date which falls within the two dates specified by the user."

In the Field row of your query:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

In the Criteria row beneath this field:
Between [Enter departure from:] And [Enter departure to:]

Yes. The two parameters to declare are:
[Enter departure from:] Date/Time
[Enter departure to:] Date/Time

 

[Aless]

Hi Allen

Problem solved, works just fine now that I put Nz in calculation!

I am most grateful to you and John for helping me out. Without your help I
would never have found the solution.

Al

Aless, double-check the spelling of your fields and parameters, looking for
details such as extra/missing spaces.

If you cannot see the problem, switch the query to SQL View, copy the SQL
statement, and post it here.

John's comment about the Null problem refers to the 2nd argument of
DateAdd(), which does need Nz() if [Days Stayed] is not a required field:
DepartDate: CVDate(DateAdd("d", Nz([Days Stayed],0), [Arrival Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Did you explicitly declare both parameters? If so, you could try this:

= [Enter departure from:] And <= [Enter departure to:]

You could also try simplifying the expression:

DepartDate: [Arrival Date] + [Days Stayed]

Are the arrival date and days stayed values all valid? If, for example, any
rows contain a Null, Allen's suggestion to use CVDate and DateAdd will cause
an error because DateAdd won't accept null values.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi Allen

I was sure what you suggested would solve the problem but unfortuantely
Access does not like it - I get the same message as before, namely, "This
expression is typed incorrectly or it is too complex to be evaluated. For
example numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables
".

I have double checked everything so typing error is unlikely. If I remove
the criteria and the parameters declared, all the records are

displayed
with

the departure dates. I can't see why the Between ..And criteria is too
complex to evaluate.

Al


Hi Al.

This sentence has clarified it for me:
"I am after records where guests DEPART (= Arrival Date plus Days Stayed)
on
ANY date which falls within the two dates specified by the user."

In the Field row of your query:
DepartDate: CVDate(DateAdd("d", [Days Stayed], [Arrival Date]))

In the Criteria row beneath this field:
Between [Enter departure from:] And [Enter departure to:]

Yes. The two parameters to declare are:
[Enter departure from:] Date/Time
[Enter departure to:] Date/Time