Query by Date and Month

[jerryg72]

I have a db with Name of Person and their DoB and Wedding Anniversary by Date and Month only (dd-mmm), but when I do the query, it does not give me result for the inputed dates. It was working perfectly earlier, but I don't know what happened now. Please help. Thank you

 

[Joe4]

Is your Anniversary date field Text or Date data type?
Note that if it is Date, even though you have formatted it NOT to show the year, the year really is part of the field, so that needs to be considered.

Can you also post the SQL code of your query so we can see your criteria for this field?

 

[jerryg72]

Thank you Joe4 for the quick response. YES! my anniversary and B'day field is Date type. But, I tried to change to "short text" but as you said it was showing the year 2016 in all the date fields.

The SQL code is as follows:

SELECT RelateT.Names, RelateT.DoB, RelateT.Phone, RelateT.Email
FROM RelateT
WHERE (((RelateT.DoB) Between [Enter the start date:] And [Enter the end date:]));

 

[Joe4]

Do you Start and End Dates ever cross years?
Are you always entering in current year dates?

 

[jerryg72]

I didn't get your 1st point Joe4. When I enter date I only give the day and month.
And the birth years are different for each person.

 

[Norie]

If you only enter the day and month in a date field Access will automatically add the current year.

 

[Joe4]

I think we want to do something related to this:
https://www.youtube.com/watch?v=IX80qhTYick
or this (which builds off the previous one:
https://www.youtube.com/watch?v=FTjyuSTcTII

 

[xenou]

I have a db with Name of Person and their DoB and Wedding Anniversary by Date and Month only (dd-mmm)

my anniversary and B'day field is Date type.

It doesn't entirely make sense that you have anniversaries stored by date and month (dd-mmm) in a database using Date fields. Date fields will always include a year.

If so, you will have to make sure your query looks for the month and day parts of the anniversary dates, and not just match directly on the field value.

something like:
....where Month(myDate) = Month(AnniversaryDate) AND Day(myDate) = Day(AnniversaryDate)

and of course you have to take leap year into account for anniversaries that only come once every four years or so.