Subtracting a fixed date from a date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a field called Booking in Date and I need to subtract that from
1/25/05 to get a new field called Days in Jail. I am not sure how to go about
this. This is what I tried in my query and it returns #Error.
Days in Jail: [INMATE DATA 1-25-05]![Booking in Date]-"1/25/2005"

Thanks,
Kate
 
Use

DateDiff ("d", [INMATE DATA 1-25-05]![Booking in Date]- #1/25/2005#)

Assuming 'Booking in Date' is after 1/25/2005, this will be the number of
days since then. And I'm never sure about these things, but you might need
to add/subtract 1 from the value of the entire expression to get it correct.

Good Luck!
 
KateM said:
Hi,
I have a field called Booking in Date and I need to subtract that from
1/25/05 to get a new field called Days in Jail. I am not sure how to go about
this. This is what I tried in my query and it returns #Error.
Days in Jail: [INMATE DATA 1-25-05]![Booking in Date]-"1/25/2005"

Thanks,
Kate
Click to expand...

Kate,

Dates need to be delimited with the "#" sign.

Try
Days in Jail: [INMATE DATA 1-25-05]![Booking in Date]-#1/25/2005#


A better way would be to use the DateDiff() function. See Help...
 
Try this

Select [INMATE DATA 1-25-05].[Booking in Date],
DateDiff("d",#1/25/2005#,[INMATE DATA 1-25-05].[Booking in Date]) as [Days in
Jail] From [INMATE DATA 1-25-05]
 
Thanks so much, the # was it. Thanks again!
--
Center for Governmental Research
Research/Technology


SteveS said:
KateM said:
Hi,
I have a field called Booking in Date and I need to subtract that from
1/25/05 to get a new field called Days in Jail. I am not sure how to go about
this. This is what I tried in my query and it returns #Error.
Days in Jail: [INMATE DATA 1-25-05]![Booking in Date]-"1/25/2005"

Thanks,
Kate
Click to expand...

Kate,

Dates need to be delimited with the "#" sign.

Try
Days in Jail: [INMATE DATA 1-25-05]![Booking in Date]-#1/25/2005#


A better way would be to use the DateDiff() function. See Help...
Click to expand...
 
Back
Top