How to use DateDiff?

[teirrah1995]

Hey. I'm awful at using online tutorials, unless I find an example which is almost exactly what I want to do, so could you help me understand DateDiff?

I have a field, Recall_Date. I've made a query, and I need to find everyone whose recall date is in one month's time from the current day.

Thanks for any help.

[slave138]

Does it need to be exactly one month from the current day or just within one month?

[teirrah1995]

Well, it will eventually be for reminder letters which would either be printed off every morning, meaning they need to be the exact day, or at the beginning of the week, meaning they have to be during a week that's a month away. The every day option would be simpler though, I'm guessing.
Thanks.

[slave138]

I think the DateAdd function would be better suited for what you are trying to do. Set the criteria for Recall_Date in your query to:

DateAdd("m", -1, Date)

If you would prefer to send them out for a week's worth at a time you could use something like this:

Between DateAdd("m",-1,Date()) And DateAdd("ww",+1,DateAdd("m",-1,Date()))

[ajetrumpet]

Hey. I'm awful at using online tutorials, unless I find an example which is almost exactly what I want to do, so could you help me understand DateDiff?

I have a field, Recall_Date. I've made a query, and I need to find everyone whose recall date is in one month's time from the current day.

Thanks for any help.

also, here are more absolutes for you to browse through: http://www.ajenterprisesonline.com/_dates

[weekend00]

I think the DateAdd function would be better suited for what you are trying to do. Set the criteria for Recall_Date in your query to:

DateAdd("m", -1, Date)

If you would prefer to send them out for a week's worth at a time you could use something like this:

Between DateAdd("m",-1,Date()) And DateAdd("ww",+1,DateAdd("m",-1,Date()))

I don't think he want 7 days from a month ago, he was wondering in choosing a week ago or a month ago, then he pick a month ago.

And we must be careful that if we want only one day in one month ago, using dateadd("m",-1,date) will miss the 31st day of last month if this month has only 30 days, e.g. we can never catch (Jan 29,) Jan 30 and Jan 31 by using dateadd("m",-1,date).

[teirrah1995]

And we must be careful that if we want only one day in one month ago, using dateadd("m",-1,date) will miss the 31st day of last month if this month has only 30 days, e.g. we can never catch (Jan 29,) Jan 30 and Jan 31 by using dateadd("m",-1,date).

That's annoying. Would that be any better if I say 4 weeks, or 30 days?
Also, it's she ^^

Thanks for the replies.

[slave138]

That's annoying. Would that be any better if I say 4 weeks, or 30 days?
Also, it's she ^^

Thanks for the replies.

Weeks or Days should be less prone to problems. Just to clarify your original goal, are you ideally trying to get a list of results for an entire week going back x amount of time?

...at the beginning of the week, meaning they have to be during a week that's a month away

[teirrah1995]

Well, the whole thing's going forward in time. And yes, ideally, it would be for a whole week so you only have to print of the letters on, say, Monday, rather than everyday.

[slave138]

I added a module with the LastMonday function:

Public Function LastMonday(dDate As Date) As Date
    'if today is Monday, then return today's date
    If Weekday(dDate, vbSunday) = 2 Then
        LastMonday = dDate
    Else
        LastMonday = dDate - Weekday(dDate, vbSunday) + 2
    End If
End Function

(Source)

Then in the date field of the query I used this as the criteria:

Between DateAdd("ww",4,LastMonday(Date())) And DateAdd("d",6,DateAdd("ww",4,LastMonday(Date())))

This method allows you to process the data any time during the week (in case someone forgets to do it Monday) by using the last Monday as the base date. It then returns all records between 4 weeks and the 6 days following that from the base day.

You can try it out in the attached sample.

[teirrah1995]

Wow, that's perfect! And to change the number of weeks, I just replace the two '4's in the second code to whatever? Thank you!