subtracting dates

[Guest]

Microsoft Access 97. No experience in writing code, but program in queries
regularly.
Have query that includes two dates. Need to subtract date 1 from date 2 and
get response in ##years ## months ## days.

Example
10/03/1984-07/06/1954 = 30 years 2 months 27 days

Have tried writing expression:
Expr1: [david dob]-[mom dob] and result shows as 11047

also: Expr1: [david dob (y,m,d)]-[mom dob(y,m,d)] When running the query
it now asks me to enter david dob and then mom dob and returns a result of 30
However david dob and mom dob are fields that already includes the date and
I am trying to avoid entering the date again in a pop up box as well as
trying to get the true result of 30 years 2 months 27 days.

By the way, I tried reading through the help screens in the database as
well as searching through past messages here.

Thanks, for any help.

 

[Allen Browne]

See the more complete DateDiff() function here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[raskew via AccessMonster.com]

Try plugging this into a standard module, then call it from the debug window
as shown:

Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose: Returns the difference between StartDate and EndDate in full
years, months and days
'Coded by: raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days

Dim intHold As Integer
Dim dayHold As Integer

intHold = Int(DateDiff("m", StartDate, EndDate)) + _
(EndDate < DateSerial(year(EndDate), month(EndDate), Day
(StartDate)))

If Day(EndDate) < Day(StartDate) Then
dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month
(StartDate) + 1, 0)) & Day(EndDate)
Else
dayHold = Day(EndDate) - Day(StartDate)
End If

fAgeYMD = Int(intHold / 12) & " year" & IIf(Int(intHold / 12) <> 1, "s ",
" ") _
& intHold Mod 12 & " month" & IIf(intHold Mod 12 <> 1, "s ", " ")
_
& LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")

End Function

HTH - Bob

Microsoft Access 97. No experience in writing code, but program in queries
regularly.
Have query that includes two dates. Need to subtract date 1 from date 2 and
get response in ##years ## months ## days.

Example
10/03/1984-07/06/1954 = 30 years 2 months 27 days

Have tried writing expression:
Expr1: [david dob]-[mom dob] and result shows as 11047

also: Expr1: [david dob (y,m,d)]-[mom dob(y,m,d)] When running the query
it now asks me to enter david dob and then mom dob and returns a result of 30
However david dob and mom dob are fields that already includes the date and
I am trying to avoid entering the date again in a pop up box as well as
trying to get the true result of 30 years 2 months 27 days.

By the way, I tried reading through the help screens in the database as
well as searching through past messages here.

Thanks, for any help.

 

[Guest]

As I said above, I don't know how to program in code, and I haven't ever
used modules either. I don't feel comfortable trying code or modules without
having someone stand over my shoulder and explaining everything from booleans
to dim to exactly where to put the code and how to access it. I.T people are
under contract to NOT have any access to client records so they refuse to
work with access tables/queries etc.

I am self taught, and there isn't anybody at work to guide me.

Is there a simpler way to do it that just involves a statement in queries?

Thanks for trying.
GenE Shockley

See the more complete DateDiff() function here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

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

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

Microsoft Access 97. No experience in writing code, but program in
queries
regularly.
Have query that includes two dates. Need to subtract date 1 from date 2
and
get response in ##years ## months ## days.

Example
10/03/1984-07/06/1954 = 30 years 2 months 27 days

Have tried writing expression:
Expr1: [david dob]-[mom dob] and result shows as 11047

also: Expr1: [david dob (y,m,d)]-[mom dob(y,m,d)] When running the query
it now asks me to enter david dob and then mom dob and returns a result of
30
However david dob and mom dob are fields that already includes the date
and
I am trying to avoid entering the date again in a pop up box as well as
trying to get the true result of 30 years 2 months 27 days.

By the way, I tried reading through the help screens in the database as
well as searching through past messages here.

Thanks, for any help

 

[Allen Browne]

Gene, trying to do this just with expression in in queries will be *much*
more complicated than using code.

Let's see if we can talk you through how to copy'n'paste the code.

1. Click the Modules tab of the Database Window.
Click New. Access opens a new module.

2. From the article, copy all the text between:
'***************** Code Start **************
and:
'************** Code End *****************
Paste into your module (Ctrl+V.)

3. To check that Access understands it choose Compile on the Debug menu (in
the code window.) If it gives no errors, you're fine.

4. Save the module with a name such as Module1. Close the module.

5. Open your query. You can now use Diff2Dates() just like any of the other
functions in Access. Assuming you have Date/Time fields named [david dob
(y,m,d)] and [mom dob(y,m,d)], you would type something like this into the
Field row in query design:
Diff2Dates("ymd", [david dob (y,m,d)], [mom dob(y,m,d)])

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

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

As I said above, I don't know how to program in code, and I haven't ever
used modules either. I don't feel comfortable trying code or modules
without
having someone stand over my shoulder and explaining everything from
booleans
to dim to exactly where to put the code and how to access it. I.T people
are
under contract to NOT have any access to client records so they refuse to
work with access tables/queries etc.

I am self taught, and there isn't anybody at work to guide me.

Is there a simpler way to do it that just involves a statement in queries?

Thanks for trying.
GenE Shockley

See the more complete DateDiff() function here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

message

Microsoft Access 97. No experience in writing code, but program in
queries
regularly.
Have query that includes two dates. Need to subtract date 1 from date 2
and
get response in ##years ## months ## days.

Example
10/03/1984-07/06/1954 = 30 years 2 months 27 days

Have tried writing expression:
Expr1: [david dob]-[mom dob] and result shows as 11047

also: Expr1: [david dob (y,m,d)]-[mom dob(y,m,d)] When running the
query
it now asks me to enter david dob and then mom dob and returns a result
of
30
However david dob and mom dob are fields that already includes the
date
and
I am trying to avoid entering the date again in a pop up box as well as
trying to get the true result of 30 years 2 months 27 days.

By the way, I tried reading through the help screens in the database
as
well as searching through past messages here.

Thanks, for any help

 

[Guest]

Allen, thanks for the response.

It does sound easy the way you have explained it and I will try it as soon
as I can.

I copied the hint into an e-mail to send to myself, but am wondering if
there is any way to bookmark a particular response?

Thanks,
GenE Shockley

 

[Allen Browne]

Depends how you read the newsgroup.

I'm using Outlook Express. Under Tools | Accounts, you Add a News account on
the server:
news.microsoft.com

Then subscribe to the groups, e.g.:
microsoft.public.access.queries

You can now:
- Flag messages
- mark them as Watched
- use Ctrl+H to see just the threads you contributed to.

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

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

Allen, thanks for the response.

It does sound easy the way you have explained it and I will try it as soon
as I can.

I copied the hint into an e-mail to send to myself, but am wondering if
there is any way to bookmark a particular response?

Thanks,
GenE Shockley

Gene, trying to do this just with expression in in queries will be *much*
more complicated than using code.

Let's see if we can talk you through how to copy'n'paste the code.

1. Click the Modules tab of the Database Window.
Click New. Access opens a new module.

2. From the article, copy all the text between:
'***************** Code Start **************
and:
'************** Code End *****************
Paste into your module (Ctrl+V.)

3. To check that Access understands it choose Compile on the Debug menu
(in
the code window.) If it gives no errors, you're fine.

4. Save the module with a name such as Module1. Close the module.

5. Open your query. You can now use Diff2Dates() just like any of the
other
functions in Access. Assuming you have Date/Time fields named [david dob
(y,m,d)] and [mom dob(y,m,d)], you would type something like this into
the
Field row in query design:
Diff2Dates("ymd", [david dob (y,m,d)], [mom dob(y,m,d)])

--