expression to come up with a day that is within 30 days in access

[Guest]

I need an expression that will come up with a day of the week within 30 days
of an existing date field. Another way of putting this is that we are
requrired to have a meeting within 30 days at my clinic, but our meeting is
always on thursday. I believe this is a fairly simple expression, and I
already have the necessary date fields, and a date add expression which of
course return the 30 days, but obviously that maybe on sunday

 

[Guest]

Mark

Try using the format function to get the day, format([date_field].'dddd').
This will get you the day of the week.

 

[Guest]

Thanks I will give that a try, I am not sure what specifies Thursday as the
dat of the week within 30 days, but I will certainly see what it comes up
with Thanks again especially for being so quick

Mark

Try using the format function to get the day, format([date_field].'dddd').
This will get you the day of the week.

I need an expression that will come up with a day of the week within 30 days
of an existing date field. Another way of putting this is that we are
requrired to have a meeting within 30 days at my clinic, but our meeting is
always on thursday. I believe this is a fairly simple expression, and I
already have the necessary date fields, and a date add expression which of
course return the 30 days, but obviously that maybe on sunday

 

[Guest]

Hi taps54 your suggestion returns a day but not the corredt day within 30 I
will continue to work with it and let you know

 

[John Nurick]

How about this:

SELECT MAX(TheDate) As TheThursday
FROM tblDates
WHERE (DateDiff("d", Date(), TheDate) <= 30)
AND (Weekday(TheDate) = 5)
;

Or you could use DMax() with a similar where clause.

 

[Edward Reid]

Mark,

The expression

=dateadd("d",[startdate],30-((weekday([startdate])+4) mod 7))

will give you the last Thursday within 30 days.

Edward

 

[Guest]

John thanks I believe this will work, the other suggestion seems to work also
from Edward thats what I like about access their are usually several ways to
an end. Thanks again Mark

 

[Guest]

Hello Ed, Thank you, This seems to work as well as Johns but is actually
simpler for my particular use. Thanks again Hope I can help you sometime.
Mark

 

[Guest]

Hi Ed, This expression was corrected in office 2003, but when I went to work
and tried this it return the #name error. I identified which field to extract
the date from so I don't understand what is missing any suggestions? Oh by
the by we still use office 2000 at my work
Thanks again

 

[Edward Reid]

Mark,

Someone else can probably give a lot more idea of the most common
causes of #Name errors, or maybe you can search this newsgroup for
"name error". It's almost certainly that the name you are using isn't
known in the context -- and "in the context" is important, as you
probably have the name right (barring one of those hard-to-see typos
like Sa11y for Sally), so it's probably the context, which can get
confusing in Access.

I would probably try to debug it by creating a parallel item (in the
form, if that's where you're using it) with just the name of the start
date. If you still get the #Name error, then you have to figure out
what context qualifier you need. If that succeeds, try writing the
expression with a date constant (eg #6/14/2006#). Even function names
have context (that is, they are methods of some object, even if that
object is Application), and though you usually don't have to write the
context, never assume.

Edward

 

[Guest]

Edward,
Thanks so much for the last thread which solved my problem, yes I was
assuming that was the key word that reminded me of the obvious. The solution
was not incorrect TO or reference, but the form I was trying to apply this to
was linked to a query that has an if reference that I believe confused the
application. It worked beautifully elsewhere. Thanks again!!!