Working with dates

[JerryZ]

This is my first post and I am learning to program Access 2010 on my own. So, please be gentle and very specific in your response.



I have a date field. I want to filter that date field to give me the records that share today's day, regardless of year, and to present the year.

FYI: This is for a "today in history" type of thing. I want to see the records I entered for events that occurred "today" in any given year.

Thank you in advance for your help.

[pbaldy]

In a query, it would look like:

WHERE Month(DateField) = Month(Date()) AND Day(DateField) = Day(Date())

In a filter, same thing but without the word "WHERE".

[JerryZ]

I'll give that a shot.

Thanks Paul

[pbaldy]

No problem; post back if you get stuck. Welcome to the site by the way.

[JerryZ]

That worked for one date field but I can't get the other date field to work with OR. I have birth and death fields where I want one and/or both.

Example: Jerry Jones (not that Jerry Jones) was born on this day in 1863. Billy Smith died on this day in 1999. The record for Jones shows in my query run but not Smith when I used

WHERE (((Month([qry_Test]![DateOfBirth]))=Month(Date())) AND ((Day([qry_Test]![DateOfBirth]))=Day(Date()))) OR (((Month([qry_Test]![DateOfBirth]))=Month(Date())) AND ((Day([qry_Test]![DateOfBirth]))=Day(Date())));

HAHAHAHAHA!! Never mind. After posting, I checked the fields and saw the problem. Fixed it and it worked....thanks.

[JerryZ]

Thanks, Paul.

OK, now I can't figure out how to extract the year from one or the other date field.

When I run

1. Year: Year([qry_Test]![DateOfBirth]) I get the year of birth in the new Year field.
2. Year: Year([qry_Test]![DateOfDeath]), I get the year of death in the new Year field.
3. Year: Year([qry_Test]![DateOfBirth]) OR Year([qry_Test]![DateOfDeath]), I get -1 in the new Year field for both Jones and Smith.
4. Year: Year([qry_Test]![DateOfBirth] OR Year([qry_Test]![DateOfDeath])), I get 1899 in the new Year field for both Jones and Smith (Jones was born in 1863 and Smith died in 1999).

I also tried replacing "OR" with "AND" and they both came back empty Year fields. I just want the year from either field. This can't be that difficult.

[pbaldy]

Which do you want? How about:

TheYear: IIf(IsDate(DateOfDeath), Year(DateOfDeath), Year(DateOfBirth))

[JerryZ]

I don't know how, but that worked. You are the man, Paul. Thanks again.

[pbaldy]

No problemo. Look up the IIf() and IsDate() functions in help and you'll understand how it works.