NOW what! Need help with ANOTHER expression.

[Sue]

I have an anniversary date for some folks in my contacts database & want to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

I get an error message.

How come?

 

[Sue]

Duh.
Just answered my own question, but for the benefit of others who may make
the same mistake (could there be anyone so dumb???), I had the formatting
set properly, but the data type was text rather than date/time. Access
couldn't figure out how to subtract a text field from a date/time field.
Again, Duh.

 

[Duane Hookom]

There is a more accurate calculation at
http://www.mvps.org/access/datetime/date0001.htm.

--
Duane Hookom
Microsoft Access MVP

Duh.
Just answered my own question, but for the benefit of others who may make
the same mistake (could there be anyone so dumb???), I had the formatting
set properly, but the data type was text rather than date/time. Access
couldn't figure out how to subtract a text field from a date/time field.
Again, Duh.

I have an anniversary date for some folks in my contacts database & want to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

I get an error message.

How come?

 

[Marshall Barton]

I have an anniversary date for some folks in my contacts database & want to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

This is more accurate than your expression:
http://www.mvps.org/access/datetime/date0001.htm

If you do not care about the hour/min/sec of the anniversary
;-)
use Date() instead of Now().

 

[Sue]

I enter this entire thing in the control source just like this?????????

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

There is a more accurate calculation at
http://www.mvps.org/access/datetime/date0001.htm.

--
Duane Hookom
Microsoft Access MVP

Duh.
Just answered my own question, but for the benefit of others who may make
the same mistake (could there be anyone so dumb???), I had the formatting
set properly, but the data type was text rather than date/time. Access
couldn't figure out how to subtract a text field from a date/time field.
Again, Duh.

I have an anniversary date for some folks in my contacts database & want
to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

I get an error message.

How come?

 

[Sue]

I enter this entire thing in the control source just like this?????????

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

I have an anniversary date for some folks in my contacts database & want
to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

This is more accurate than your expression:
http://www.mvps.org/access/datetime/date0001.htm

If you do not care about the hour/min/sec of the anniversary
;-)
use Date() instead of Now().

 

[Sue]

OK - I got it to work, but why is this more accurate than what I used? The
figures are the same. What's the difference between the 2 expressions?

Thanks.

I enter this entire thing in the control source just like this?????????

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

I have an anniversary date for some folks in my contacts database & want
to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

This is more accurate than your expression:
http://www.mvps.org/access/datetime/date0001.htm

If you do not care about the hour/min/sec of the anniversary
;-)
use Date() instead of Now().

 

[Sue]

One other related question...

I have 2 fields - birthday & anniversary. Each are date/time fields with
mm/dd/yy formatting.
I've got 2 separate queries & reports - one for anniversaries & one for
birthdays - that I've designed to serve as a crutch for my feeble mind - a
reminder that birthdays or anniversaries are coming up & that there are
cards to send, presents to buy...
Is there any way to combine the 2 of these so that both birthdays and
anniversaries are both sorted all at once & thus I'll have a report that
shows me BOTH birthdays & anniversaries sorted by month & day?

Thanks.

I enter this entire thing in the control source just like this?????????

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

I have an anniversary date for some folks in my contacts database & want
to
know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)

This is more accurate than your expression:
http://www.mvps.org/access/datetime/date0001.htm

If you do not care about the hour/min/sec of the anniversary
;-)
use Date() instead of Now().

 

[Marshall Barton]

Your expression relies on on rounding to get the right
answer. It will never be off by more than one day, but why
take a chance when you can be precise.
--
Marsh
MVP [MS Access]

OK - I got it to work, but why is this more accurate than what I used? The
figures are the same. What's the difference between the 2 expressions?

I enter this entire thing in the control source just like this?????????

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

Sue wrote:
I have an anniversary date for some folks in my contacts database & want
to know how long these folks have been married.

YearsMarried:=Int((YearsMarried: Now()-[AnniversaryDate])/365.25)


This is more accurate than your expression:
http://www.mvps.org/access/datetime/date0001.htm

If you do not care about the hour/min/sec of the anniversary
;-)
use Date() instead of Now().

 

[Marshall Barton]

I have 2 fields - birthday & anniversary. Each are date/time fields with
mm/dd/yy formatting.
I've got 2 separate queries & reports - one for anniversaries & one for
birthdays - that I've designed to serve as a crutch for my feeble mind - a
reminder that birthdays or anniversaries are coming up & that there are
cards to send, presents to buy...
Is there any way to combine the 2 of these so that both birthdays and
anniversaries are both sorted all at once & thus I'll have a report that
shows me BOTH birthdays & anniversaries sorted by month & day?

Not exactly sure what you want here, but I think you can get
a nice effect this way.

First create a query that uses a combined field for the
dates:

SELECT person, "Birthday" As CardType, birthday As XDate
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]
UNION ALL
SELECT person, "Anniversary" As CardType, anniversary
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]

Then change one of your reports to use the CardType and
XDate (instead of birthday).

 

[Tom Lake]

Your expression relies on on rounding to get the right
answer. It will never be off by more than one day, but why
take a chance when you can be precise.

Couldn't her expression can be off by a year if the anniversary hasn't
occurred in this year yet?

Tom Lake

 

[Marshall Barton]

"Marshall Barton" wrote

Couldn't her expression can be off by a year if the anniversary hasn't
occurred in this year yet?

I don't think so because it's working on the number of days
in the difference I think you can be slightly more accurate
by dividing by 365.2525. But, I don't feel like putting a
lot of analysis into a less than precise calculation.

 

[Sue]

I'm doofus when it comes to SQL, Marshall.
Part of my problem is that I don't know the YEAR for quite a few of my
contacts' birthdays and/or anniversaries... so I have a field with
month/day/1800 on which I'm building this query & report. (Just explaining
why the field names are "bogus_____date". Here's my SQL statement:

SELECT tblContacts.LastName, tblContacts.FirstName,
tblContacts.BirthdayCardList, tblContacts.BogusBirthday,
tblContacts.AnniversaryCardList, tblContacts.BogusAnniversary
FROM tblContacts
WHERE (((tblContacts.BirthdayCardList)=Yes)) OR
(((tblContacts.AnniversaryCardList)=Yes))
ORDER BY tblContacts.LastName, tblContacts.FirstName;

If I'm understanding you correctly, I should try something like this:

SELECT tblContacts.LastName, tblContacts.FirstName,
tblContacts.BirthdayCardList, "BogusBirthday" As CardType, bogusbirthday As
XDate
FROM tblContacts
WHERE BogusBirthday Between [Start Date] And [End Date]
UNION ALL
SELECT tblContacts.LastName, tblContacts.FirstName, "BogusAnniversary" As
CardType, bogusanniversary
FROM tblContacts
WHERE bogusanniversary Between [Start Date] And [End Date]

I tried that & got the following message: "The number of columns in the two
selected tables or queries of a union query do not match."

Can you please help to correct the statement? I truly appreciate your help.

I have 2 fields - birthday & anniversary. Each are date/time fields with
mm/dd/yy formatting.
I've got 2 separate queries & reports - one for anniversaries & one for
birthdays - that I've designed to serve as a crutch for my feeble mind - a
reminder that birthdays or anniversaries are coming up & that there are
cards to send, presents to buy...
Is there any way to combine the 2 of these so that both birthdays and
anniversaries are both sorted all at once & thus I'll have a report that
shows me BOTH birthdays & anniversaries sorted by month & day?

Not exactly sure what you want here, but I think you can get
a nice effect this way.

First create a query that uses a combined field for the
dates:

SELECT person, "Birthday" As CardType, birthday As XDate
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]
UNION ALL
SELECT person, "Anniversary" As CardType, anniversary
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]

Then change one of your reports to use the CardType and
XDate (instead of birthday).

 

[Marshall Barton]

I see from your original query that you are not using a date
range, but you are using some kind of list field to
include/exclude some records. The error message was because
you were missing the AnniversaryCardList field in the second
select's field list (I don't understand why you want either
list field in the select fields).

I'm still not 100% sure what you need, but this should be
closer:

SELECT LastName, FirstName,
"Birthday" As CardType, BogusBirthday As Xdate
FROM tblContacts
WHERE BirthdayCardList=Yes
UNION ALL
SELECT LastName, FirstName,
"Anniversary", BogusAnniversary
FROM tblContacts
WHERE AnniversaryCardList=Yes
ORDER BY LastName, FirstName
--
Marsh
MVP [MS Access]

I'm doofus when it comes to SQL, Marshall.
Part of my problem is that I don't know the YEAR for quite a few of my
contacts' birthdays and/or anniversaries... so I have a field with
month/day/1800 on which I'm building this query & report. (Just explaining
why the field names are "bogus_____date". Here's my SQL statement:

SELECT tblContacts.LastName, tblContacts.FirstName,
tblContacts.BirthdayCardList, tblContacts.BogusBirthday,
tblContacts.AnniversaryCardList, tblContacts.BogusAnniversary
FROM tblContacts
WHERE (((tblContacts.BirthdayCardList)=Yes)) OR
(((tblContacts.AnniversaryCardList)=Yes))
ORDER BY tblContacts.LastName, tblContacts.FirstName;

If I'm understanding you correctly, I should try something like this:

SELECT tblContacts.LastName, tblContacts.FirstName,
tblContacts.BirthdayCardList, "BogusBirthday" As CardType, bogusbirthday As
XDate
FROM tblContacts
WHERE BogusBirthday Between [Start Date] And [End Date]
UNION ALL
SELECT tblContacts.LastName, tblContacts.FirstName, "BogusAnniversary" As
CardType, bogusanniversary
FROM tblContacts
WHERE bogusanniversary Between [Start Date] And [End Date]

I tried that & got the following message: "The number of columns in the two
selected tables or queries of a union query do not match."


"Marshall Barton" wrote

I have 2 fields - birthday & anniversary. Each are date/time fields with
mm/dd/yy formatting.
I've got 2 separate queries & reports - one for anniversaries & one for
birthdays - that I've designed to serve as a crutch for my feeble mind - a
reminder that birthdays or anniversaries are coming up & that there are
cards to send, presents to buy...
Is there any way to combine the 2 of these so that both birthdays and
anniversaries are both sorted all at once & thus I'll have a report that
shows me BOTH birthdays & anniversaries sorted by month & day?

Not exactly sure what you want here, but I think you can get
a nice effect this way.

First create a query that uses a combined field for the
dates:

SELECT person, "Birthday" As CardType, birthday As XDate
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]
UNION ALL
SELECT person, "Anniversary" As CardType, anniversary
FROM yourtable
WHERE Birthdate Between [Start Date] And [End Date]

Then change one of your reports to use the CardType and
XDate (instead of birthday).