Searching for dates (1 Viewer)

[Guinifi]

Hi,
I'm new to the forum and have learned alot just lurking but I can't seem to find what I am looking for specifically.
I have a table of clients and a field for their birthdate. This birthdate field included month, day, and year.
I want to do a querry to see who's birthday is coming up in the next 10 days. I don't know how to figure out how to get the system to ignore the year in this field. If I search for birthdays between now and ten days from now I get zero results, of course, because none of my clients are going to be born in the next ten days. heh heh. How do I get my search to ignore the year?
Thanks for any help, in advance.
Guinifi

 

[jjturner]

Hello and Welcome!

Upcoming_Birthday:

IIf(CDate(Format([Birthday], "m/d") & "/" & Year(Date())) < Date(),
    CDate(Format([Birthday], "m/d") & "/" & (Year(Date())+1)),
    CDate(Format([Birthday], "m/d") & "/" & (Year(Date())))

Criteria would be:

Between Date() And Date()+10

The calculated field ensures that Upcoming_Birthday is always >= today's date, while the criteria will filter your recordset for anyone whose birthday will occur up to 10 days out (including current day, this will be a range of 11 total days)

btw, what do they do for fun over in Guam (besides make databases) ?

HTH,
John

 

[Guinifi]

Hi John, and thanks for your quick reply.
Since I'm extremely new to this Access thing, I only opened the program for the first time the day before yesterday, I have another question.

I know I put the

Between Date() And Date()+10

in the criteria line. Where do I put the code

IIf(CDate(Format([Birthday], "m/d") & "/" & Year(Date())) < Date(),
CDate(Format([Birthday], "m/d") & "/" & (Year(Date())+1)),
CDate(Format([Birthday], "m/d") & "/" & (Year(Date()))

??

As for fun on Guam, it's already 4th of July here. (We are 17 hours ahead of California.) We're kinda watching what North Korea is gonna do today, heh heh.

As for other fun, I enjoy night hunting snakes. I know, sounds wierd, but it's fun. See one of my trophies here: http://twitpic.com/7y78z.. i know it can't be a clickable link, but the address is short.

Thanks again for your help.
Guinifi

 

[jjturner]

In a blank column in the query design grid, type (verbatim):
Upcoming_Birthday: IIf(CDate(Format([Birthday], "m/d") & "/" & Year(Date())) < Date(),
CDate(Format([Birthday], "m/d") & "/" & (Year(Date())+1)),
CDate(Format([Birthday], "m/d") & "/" & (Year(Date()))

That's called a calculated field, which is basically something you create (meaning not a pre-existing field from your table) - but Access turns it into a field when you run the query.

Happy 4th and I hope those snakes aren't poisonous! I figured you were gonna say "deep sea fishing" or something like that, being surrounded by all that deep water...

HTH,
John

 

[Guinifi]

Thanks JOhn,
I've tried this, cut and paste and also typing it in by hand, being careful of course to get it right. In both instances i get this error msg:
"The expression you entered is missing a closing paranthesis, bracket (]),
or verticle bar (|)."
hmmm
Guinifi

 

[jjturner]

Hi Guinifi,

My bad - and my apologies! (Lack of testing it for real was bound to bite me )

Try putting a closing parentheses at the very end -
Also, I neglected to mention that you'll probably need to take the implicit carriage returns out (it should all be on one line when you input it in the Field Row cell)

Hope that sorts it ...

John

 

[Guinifi]

Ok John,
I guess this is on the right track, but still a problem.

When I entered the code as instructed and ran the query I got a pop-up box asking for input on "birthday". It occurred to me that it is looking for a field in the record named "Birthday". My birthday field is designated as "c_DOB" so I substituted all three words "Birthday" in the brackets to the word "c_DOB". Now, there are no error messages or anything, but I get no results. When I run the query, it seems to work, but doesn't select any records. It should, as I know there are records with birthdates within the next 10 days. (I also tried substituting "30" where 10 is in the criteria box and still zero results.) Zero records are selected.

Thanks again for any help and sorry for being a pain. As I said, it's only been three days since I first tried using Access. I guess you could spend a lot more time than that and still not know everything there is, huh.
Guinifi

 

[Guinifi]

I've just discovered something. The query is still looking at the year.
When I change the query to this:

Between Date() And Date()-10000

I get the desired results. So the code I've placed in the field line is not doing anything, it seems.

Guinifi

 

[jjturner]

Hi Guinifi,
No worries about being a pain - working out the glitches is what this forum is for!

Hmmm, that's strange. You did right by subbing your actual "c_DOB" field in for "Birthday" (another oversight on my part).

May I ask what data type your "c_DOB" field is - is it Text or Date?

If it's Text type, then I would suggest changing it to Date type in the table design.
If it's already Date, then try running the query without any criteria and see what records get returned. At least that will give us a clue as to what might be going on.

Hang in there, you're getting it!

Cheers,
John

 

[Guinifi]

Hi John,
I guess we're posting at the same time. As I said, when I run the query at a -10000, I get results for all the people who are 27 years old and less.

And the c_DOB field is already set to "date".

Thanks again for your assistance.

Guinifi

 

[jjturner]

Hi Guinifi,
But I thought your intention was to return the names of folks with birthdays coming up within the next 10 days?

What happens when you leave out the criteria entirely? Do you get records displayed and do the values look right in the calculated field?

We're probably missing something simple - just trying to pin it down

John

 

[Guinifi]

Yes, my intent is to get a return of all records where the birthday falls in the next ten days. If I remove the criteria completely, the query returns ALL records.
As I said, I just used the -10000 to see if it would give me past birthdays, and it does perfectly, because it's looking at the year. As I said, with -10000 it returns only those records of people who are 27 and under. That tells me that the query is still looking at the year and not just the month and day.
Does that sound right?
Thanks
Guinifi

 

[jjturner]

OK - I think I might know what's happening.

Is the criteria under your "c_DOB" column or the calculated field column? I suspect it's under the "c_DOB" column, in which case, you'll need to move it under the calculated field column in query design.

Remember, the calculated field is supposed to calculate a *future* date within 1 year of today's date. If the *Between...* criteria is applied to that column, then it should be filtering correctly.

And if that doesn't get you sorted, I'll give it all in SQL by which you can just copy and paste the entire query in SQL view.

(this is a learning experience for me too - I've never tried using the Between statement in reverse chronological order - now I know it's possible )

Cheers,
John

 

[Guinifi]

Thanks John,
I've moved the criteria to the criteria line in the calculated field column which is titled "Upcoming_Birthday". Now, when I run the query, I get an error msg saying "data type mismatch in criteria expression".

Once again, thank you.
Guinifi

 

[jjturner]

oooh - this is a tougher nut to crack than I thought. Give me a minute to test out on my side (time to fire up the Access app remotely)

I should have it for you in a bit...

In the meantime, try taking out the criteria again and see if the output in the calculated field is left justified or right justified. It should be right justified - meaning it has successfully converted the values to Date data type.

Cheers,
John

 

[Guinifi]

Ok John,
Please don't knock yourself out about this. I'm just trying to learn this thing to help my business a little, but it's not a crisis, heh heh. I really appreciate your help.
Take your time. It's Sunday morning here and we're getting set to move along to church here in a few minutes.

By the way, I removed the criteria and ran the query. The results in the calculated field column are definitely right justified so I guess that's a good thing.
Guinifi

 

[jjturner]

ok - I just tried it with some test data and it seems to work fine here, so maybe there's an issue with the criteria you've input.

I don't want to hold you up from Sunday obligations so don't mind me.
Just trying to get us across the goal line - no rush on my side either

Good to hear that we're getting actual dates in the calculated field.

When you get around to it, try putting ">Date()" (without the quotes) in the criteria and see how that works. It should return everything...

Cheers,
John

 

[raskew]

Hi -

Take a look at this post. My post looks kindof long and complicated, but all you'll need to do is copy/paste it to a standard module, then call it as shown in the description. No modifications should be necessary

It allows you to forecast the next anniversary of an event (e.g. Hire Date, Birth Date, etc.) Using this, if you were to enter in the criteria cell: Between date() and Date() + 10, you'd be able to forecast upcoming events as of any given date.

HTH - Bob