Calculated field in a query

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




"Jeff Boyce" <nonsense.TakeThisOut@nonsense.com> wrote in message
news:%23QjsDWULJHA.1156@TK2MSFTNGP04.phx.gbl...
> [Bookings]![Nights],[Bookings]![ArrivalDate]
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "Jan" <jp@hotmail> wrote in message
> news:%23GSNsRULJHA.3808@TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>>
>>
>
>
 >> Stay informed about: Calculated field in a query 

What happens if you substitute actual values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jan" <jp@hotmail> wrote in message
news:uTtJ$cULJHA.5952@TK2MSFTNGP03.phx.gbl...
> Just tried what you suggest and DepartureDate:
> DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
> getting the same error message!!
>
>
>
>
> "Jeff Boyce" <nonsense DeleteThis @nonsense.com> wrote in message
> news:%23QjsDWULJHA.1156@TK2MSFTNGP04.phx.gbl...
>> [Bookings]![Nights],[Bookings]![ArrivalDate]
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Jan" <jp@hotmail> wrote in message
>> news:%23GSNsRULJHA.3808@TK2MSFTNGP04.phx.gbl...
>>>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
>>>
>>>
>>>
>>>
>>
>>
>
 >> Stay informed about: Calculated field in a query 

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


"Jeff Boyce" <nonsense.RemoveThis@nonsense.com> wrote in message
news:%23JpDSkULJHA.5164@TK2MSFTNGP04.phx.gbl...
> What happens if you substitute actual values?
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Jan" <jp@hotmail> wrote in message
> news:uTtJ$cULJHA.5952@TK2MSFTNGP03.phx.gbl...
>> Just tried what you suggest and DepartureDate:
>> DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
>> getting the same error message!!
>>
>>
>>
>>
>> "Jeff Boyce" <nonsense.RemoveThis@nonsense.com> wrote in message
>> news:%23QjsDWULJHA.1156@TK2MSFTNGP04.phx.gbl...
>>> [Bookings]![Nights],[Bookings]![ArrivalDate]
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>>
>>> "Jan" <jp@hotmail> wrote in message
>>> news:%23GSNsRULJHA.3808@TK2MSFTNGP04.phx.gbl...
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>
 >> Stay informed about: Calculated field in a query 

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


"Jan" <jp@hotmail> wrote in message
news:erSSFxULJHA.3744@TK2MSFTNGP06.phx.gbl...
> 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
>
>
> "Jeff Boyce" <nonsense.RemoveThis@nonsense.com> wrote in message
> news:%23JpDSkULJHA.5164@TK2MSFTNGP04.phx.gbl...
>> What happens if you substitute actual values?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Jan" <jp@hotmail> wrote in message
>> news:uTtJ$cULJHA.5952@TK2MSFTNGP03.phx.gbl...
>>> Just tried what you suggest and DepartureDate:
>>> DateAdd("d",([Bookings]![Nights]),([Bookings]![ArrivalDate])) but still
>>> getting the same error message!!
>>>
>>>
>>>
>>>
>>> "Jeff Boyce" <nonsense.RemoveThis@nonsense.com> wrote in message
>>> news:%23QjsDWULJHA.1156@TK2MSFTNGP04.phx.gbl...
>>>> [Bookings]![Nights],[Bookings]![ArrivalDate]
>>>>
>>>> Regards
>>>>
>>>> Jeff Boyce
>>>> Microsoft Office/Access MVP
>>>>
>>>>
>>>> "Jan" <jp@hotmail> wrote in message
>>>> news:%23GSNsRULJHA.3808@TK2MSFTNGP04.phx.gbl...
>>>>>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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
 >> Stay informed about: Calculated field in a query 

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, Microsoft Access MVP

Jeff Boyce wrote:
> 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#)
>
 >> Stay informed about: Calculated field in a query 

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

Jan wrote:
> 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
>
>
>
>
 >> Stay informed about: Calculated field in a query 

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

DepartureDate: [ArrivalDate]+[Nights]

--
Steve Schapel, Microsoft Access MVP

Steve Schapel wrote:
> 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.
>
 >> Stay informed about: Calculated field in a query 

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

Jeff

"Steve Schapel" <schapel.RemoveThis@mvps.org.ns> wrote in message
news:u%23QXYVXLJHA.1308@TK2MSFTNGP02.phx.gbl...
> 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, Microsoft Access MVP
>
> Jeff Boyce wrote:
>> 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#)
>>
 >> Stay informed about: Calculated field in a query 

Steve Schapel wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
 >> Stay informed about: Calculated field in a query 

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.

--
Steve Schapel, Microsoft Access MVP

Jeff Boyce wrote:
> Thank you, Steve. So, I'm batting .500?! That's good enough for any
> baseball team...<g>
>
 >> Stay informed about: Calculated field in a query 

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.

--
Steve Schapel, Microsoft Access MVP

Rick Brandt wrote:

> 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.
>
 >> Stay informed about: Calculated field in a query 

Steve Schapel wrote:
> 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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
 >> Stay informed about: Calculated field in a query 

Rick Brandt wrote:

>> 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?

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.

--
Steve Schapel, Microsoft Access MVP
 >> Stay informed about: Calculated field in a query