Calculated field in a query

[Jan]

I am trying to create a calculate fied in a query to get a departure date
which would then be used to select records between two departure dates. I
have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or it is
too complex to be evaluated .......". I guess the answer is simple but
can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan

 

[Jeff Boyce]

[Bookings]![Nights],[Bookings]![ArrivalDate]

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jan]

Just tried what you suggest and DepartureDate:
DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
getting the same error message!!

[Bookings]![Nights],[Bookings]![ArrivalDate]

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to create a calculate fied in a query to get a departure date
which would then be used to select records between two departure dates. I
have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or it
is too complex to be evaluated .......". I guess the answer is simple
but can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan

 

[Jeff Boyce]

What happens if you substitute actual values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Just tried what you suggest and DepartureDate:
DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
getting the same error message!!

[Bookings]![Nights],[Bookings]![ArrivalDate]

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to create a calculate fied in a query to get a departure date
which would then be used to select records between two departure dates.
I have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or it
is too complex to be evaluated .......". I guess the answer is simple
but can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan

 

[Jan]

If I put in DepartureDate: DateAdd("d",5,17/10/2008) and run the query with
departure dates between 17/10/2008 and 21/10/2008 some records are selected
but they bear no relation to the selection criteria (some of the are from
1999!) and the calculated field shows 4/1/1990 on each record as the
departure date!

I have another query within the same database which also calculates the
departure date based on the following:
DepartDate: CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0)))
This has worked perfectly for a number of years. I have tried using this in
the new query but when I run it ALL the records which match some of the
other selection criteria seem to get selected - selection by departure dates
appears to get ignored.

Jan

What happens if you substitute actual values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Just tried what you suggest and DepartureDate:
DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
getting the same error message!!

[Bookings]![Nights],[Bookings]![ArrivalDate]

Regards

Jeff Boyce
Microsoft Office/Access MVP


I am trying to create a calculate fied in a query to get a departure
date which would then be used to select records between two departure
dates. I have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or it
is too complex to be evaluated .......". I guess the answer is simple
but can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan

 

[Jeff Boyce]

It may be that you are running into the issue of date formatting. If I
recall, dates used in the built-in Access functions need to follow the
US-based "mm/dd/yyyy" format.

Also, I wonder if you need to use the "#" delimiters for your date values.

What happens if you use

DateAdd("d",5,#10/17/2008#)

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I put in DepartureDate: DateAdd("d",5,17/10/2008) and run the query
with departure dates between 17/10/2008 and 21/10/2008 some records are
selected but they bear no relation to the selection criteria (some of the
are from 1999!) and the calculated field shows 4/1/1990 on each record as
the departure date!

I have another query within the same database which also calculates the
departure date based on the following:
DepartDate: CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0)))
This has worked perfectly for a number of years. I have tried using this
in the new query but when I run it ALL the records which match some of the
other selection criteria seem to get selected - selection by departure
dates appears to get ignored.

Jan

What happens if you substitute actual values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Just tried what you suggest and DepartureDate:
DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
getting the same error message!!




[Bookings]![Nights],[Bookings]![ArrivalDate]

Regards

Jeff Boyce
Microsoft Office/Access MVP


I am trying to create a calculate fied in a query to get a departure
date which would then be used to select records between two departure
dates. I have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or
it is too complex to be evaluated .......". I guess the answer is
simple but can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan

 

[Steve Schapel]

PMFJI... It is correct that if you are using an actual date, that it
needs to be delimited with #s. But I think the date needs to be in the
same format as the user's Windows settings. The stipulatrion about US
format applies in VBA but not elsewhere.

 

[Steve Schapel]

Jan,

Nights is a number field, right? And ArrivalDate is a Date/Time data
type field?

What happens if you leave out the table reference?
DepartureDate: DateAdd("d",[Nights],[ArrivalDate])

This should be ok, unless you happen to have a Nights field or a
ArrivalDate field in more than one table within the query.

 

[Steve Schapel]

.... Alternatively, this should also be good:

DepartureDate: [ArrivalDate]+[Nights]

 

[Jeff Boyce]

Thank you, Steve. So, I'm batting .500?! That's good enough for any
baseball team...<g>

Jeff

 

[Steve Schapel]

Baseball? Hmmm, yes I remember hearing once that there is a game like
that. I heard that it is a bit like cricket but with very few rules.

 

[Rick Brandt]

PMFJI... It is correct that if you are using an actual date, that it
needs to be delimited with #s. But I think the date needs to be in
the same format as the user's Windows settings. The stipulatrion
about US format applies in VBA but not elsewhere.

That is not correct. Date *literals* always need to be in either US format
or in a non-ambiguous format. Queries and VBA code is the same.

 

[Steve Schapel]

Sorry to be contrary, Rick. I claim some expertise in this matter based
on the fact that I live and develop Access applications in a non-US country.

NextMonth: DateAdd("m",1,#4/04/2008#)
returns: 4/05/2008

NextMonth: DateAdd("m",1,#6/07/2008#)
returns: 6/08/2008

Similarly in query criteria, for example:
<#3/5/2008#
.... will return records with dates in April.

I'm therefore not sure what you are referring to, Rick. Perhaps you
could give me a specific example of where you think what you stated
would be true.

 

[Rick Brandt]

Sorry to be contrary, Rick. I claim some expertise in this matter
based on the fact that I live and develop Access applications in a non-US
country.
NextMonth: DateAdd("m",1,#4/04/2008#)
returns: 4/05/2008

Not a great example since both day and month are (4). Did it add a month to
the first part or the second?

NextMonth: DateAdd("m",1,#6/07/2008#)
returns: 6/08/2008

You indicated that US (or Non-Ambiguous) format was necessary for VBA, but
not queries, yet these are VBA functions in your example. Are you saying
that VBA functions used in queries have different behavior than VBA
functions used in VBA code routines?

Similarly in query criteria, for example:
<#3/5/2008#
... will return records with dates in April.

I'm therefore not sure what you are referring to, Rick. Perhaps you
could give me a specific example of where you think what you stated
would be true.

Of all of the references to this in these groups I simply don't recall it
ever being described as a code-only issue. If what you say is true then
that would mean that an identical query will run differently on multiple PCs
based on the user's regional settings. I would describe that as incorrect
behavior. Granted, it would be poor form to distribute a query with a date
literal embedded in it that did not use a non-ambiguous format, but however
such a query behaved, I always thought that behavior would at least be
consistent.

 

[Steve Schapel]

Not a great example since both day and month are (4). Did it add a month to
the first part or the second?

Ok, fair enough.

NextMonth: DateAdd("m",1,#3/05/2008#)
3/06/2008

TwoDaysHence: #5/06/2008#+2
7/06/2008

You indicated that US (or Non-Ambiguous) format was necessary for VBA, but
not queries, yet these are VBA functions in your example. Are you saying
that VBA functions used in queries have different behavior than VBA
functions used in VBA code routines?

Yes, Rick, that's correct. If you do the same as the above examples in
the Immediate Window...

? DateAdd("m",1,#3/05/2008#)
5/04/2008

? #5/06/2008#+2
8/05/2008

.... believe it or not!!

Of all of the references to this in these groups I simply don't recall it
ever being described as a code-only issue. If what you say is true then
that would mean that an identical query will run differently on multiple PCs
based on the user's regional settings. I would describe that as incorrect
behavior. Granted, it would be poor form to distribute a query with a date
literal embedded in it that did not use a non-ambiguous format, but however
such a query behaved, I always thought that behavior would at least be
consistent.

As you suggest, date literals in queries are in practice not common.
But the reality is that the behaviour is not consistent.

It is also not consistent with dates referenced from controls. For
example, if in a query you use a criteria of:
Between [Forms]![MyForm]![DateFrom] And [Forms]![MyForm]![DateFrom]
.... then not only does this work fine for non-US dates on a computer
with non-US regional settings, in fact it is required for the query to
work correctly. In other words Access expects the date format used to be
consistent with the regional settings. If you try to execute the same
query in VBA, the dates must be converted to US format.

For myself, I routinely convert dates to Long Integer in code, as that
always will be right. E.g.
"... WHERE TheDateField BETWEEN " & CLng(Me.DateFrom) & " AND " &
CLng(Me.DateTo)

Honestly, you Americans have no idea of the pain this has inflicted on
the innocent.

 

[Jan]

Nights is a number field and ArrivalDate is a Date/Time field.

This is what I tried earlier but got the error message "This expression is
typed incorrectly or it is
too complex to be evaluated .......".

You also suggested trying DepartureDate: [ArrivalDate]+[Nights]
This works up to a point, unfortunately no notice is taken of the selection
criteria in this field - all the records which match another selection
criteria are selected.

By the way, the DepartureDate bit of what you suggest disappears from the
query when I next open it in design view!

I have another query within the same database which also calculates the
departure date based on the following:
DepartDate: CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0)))

I can't recall how I ever put this together but it has worked perfectly for
a number of years and still does. I tried using this in
the new query but when I run it I get the same result as above (ie, all
records get selected).

Jan

Jan,

Nights is a number field, right? And ArrivalDate is a Date/Time data type
field?

What happens if you leave out the table reference?
DepartureDate: DateAdd("d",[Nights],[ArrivalDate])

This should be ok, unless you happen to have a Nights field or a
ArrivalDate field in more than one table within the query.

--
Steve Schapel, Microsoft Access MVP

I am trying to create a calculate fied in a query to get a departure date
which would then be used to select records between two departure dates.
I have tried the following:

DepartureDate: DateAdd("d",[Bookings.Nights],[Bookings.ArrivalDate])

When I run the query, I get "This expression is typed incorrectly or it
is too complex to be evaluated .......". I guess the answer is simple
but can't figure out what I am doing wrong.

Would appreciate any help in getting this resolved.

Jan