I don't care about the Year

[SharonC]

Hi -
I have a simple database with one date field named [Date] formatted for Short Date.

I'm keeping information about Anniversaries, Birthdays, holidays, etc. Anniversaries/Birthdays have dates that "are in the past".

I want to develop a query that will retrieve those records that fall within the computer system's date (month & day) and the next 14 days (2 weeks). In other words, I'm trying to find out "what will be happening in the next two weeks". I don't care about the fields YEAR.

I've been trying to use the BETWEEN...AND, but as we all know, you must use the # symbol for exact dates. I've fooled around with the DatePart function, DateAdd, Month & Day functions, etc. to no avail.

Any suggestion or ideas will be gladly welcomed.

 

[Alexandre]

"Between " & Format(date(), "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & " and " & Format(DateAdd("d",14,Date()), "\#mm\/dd\/yyyy\#;;;\N\u\l\l")


Should be the right syntax
Hope this helps

Alex

 

[Alexandre]

Well, I see I only answered partially.
Following the above, you may like to declare

"\#mm\/dd\/yyyy\#;;;\N\u\l\l" as a global constant (say: USFormatDate) in your database, since you will probably use it often when manipulating dates underVBA.

Then your where statement would look to something like this (Assuming that MyDate is the name of the date field you are using as a criteria):

"Where " Format ([MyDate], "\#mm\/dd;;;\N\u\l\l") & _
Format(Date(), "\/yyyy\#;;;\N\u\l\l") & _
" Between " & _
Format (DateAdd("d",14,Date()), USFormatDate) & " and " & Format(Date(), USFormatDate)

In bold: we extract the days and month from my date and format them into #mm/dd, then we add the present year (extracted from the present date and formatted into: /yyy#)

In italics, the criteria 'between today and today+14' as seen above

Alex

[This message has been edited by Alexandre (edited 10-19-2001).]

 

[Rich]

You have a field named Date, which is a reserved word in access and will cause you problems unless you change it to something else. fldDate MyDate anything but Date

 

[SharonC]

Thanks Alexandre, but alas! Your suggestion "doesn't work"(?)

After studying your reply, I think that your using the "\" to mean "literally these characters(?) The \/yyyy part confuses me. Again, I'm using short date (mm/dd/yy) format. Could this be the problem? Another possibility, Alexandre....your two messages seems to be showing different "ways" of doing this....could there be a typo? (Just asking...<g> )

And Rich...you're absolutely correct! That is a dumb habit I've gotten myself into. I have renamed the field [MyDate]. Thanks for "slapping my hands" so lightly <g>

Again, if anyone else can see an answer to this problem, I'd appreciate it, but I'm getting to the point that I think Access can't "read" a calendar for months and days only ;/

SharonC

 

[SteveA]

The hardest part is the fact that you are storing a date and dates are dependant on day, month and year.

Perhaps you could look at having a process that bumps the date by a year once it has elapsed. You could write an update query that says if the Date is in the past, add one year to it. If some of your dates are more than a year in the past, you may need to do it again. You could make this query execute as part of your start-up process.

That way your standard date checks DateAdd("d",14, Date()) would be able to find anything with a date in the next fourteen days.

Is this a possible solution?
Cheers,
SteveA

 

[raskew]

(1) As suggested, first rename your 'Date' field (I've used 'DOB' in the following example).
(2) Copy paste the following to a new query, then modify the table/field names as required.

SELECT tblStaff.Name, tblStaff.DOB, DateSerial(Year(Date()),Month([DOB]),Day([DOB])) AS MyDate
FROM tblStaff
WHERE ((Not (tblStaff.DOB) Is Null) AND ((DateSerial(Year(Date()),Month([DOB]),Day([DOB]))) Between Date() And Date()+14))
ORDER BY tblStaff.Name;

 

[SharonC]

Raskew! Thank you, thank you, THANK YOU!!!

I understand PERFECTLY everything you "did" and it worked!

To all the rest of you....MANY, MANY thanks for your persistent help and comments.

To each and every one of you: May all your future roads be strown with rose petals <g>

SharonC