Too complex? Really?

[Cornfused]

I have a parameter query with the field [Start_Date] set as one of the user
input fields. For criteria, I've entered the following:

IIf(Format([Enter Start Date of ILT],"dddd")="Tuesday",([Enter Start Date of
ILT]-1),IIf(Format([Enter Start Date of ILT],"dddd")="Wednesday",([Enter
Start Date of ILT]-2),IIf(Format([Enter Start Date of
ILT],"dddd")="Thursday",([Enter Start Date of ILT]-3),IIf(Format([Enter Start
Date of ILT],"dddd")="Friday",([Enter Start Date of ILT]-4),[Enter Start Date
of ILT]))))

So, I get the error that the calculation is too complex or that the syntax
is bad. When I did some experimentation to try to locate the problem area,
it seems that the Format function may be the culprit.

Please make suggestions as to how I can do this differently and get the same
result - that of setting the [Start_Date] to the most recent Monday
regardless of the date entered by the user.

Thanks!

 

[Allen Browne]

JET says "too complex" when it can't understand waht you are doing. You have
heaps of nested IIf(), Format() which generates strings, and probably an
undeclared (and so untyped) parameter.

1. Declare the parameter
Choose Parameters on the Query menu (in query design.)
Access opens a dialog. Enter:
[Enter Start Date of ILT] Date/Time

2. Simplify the expression.
If I understand you correctly you want to return the Monday of the start of
the week. Something like this might work in the Criteria row under your
date/time field:
[Enter Start Date of ILT] - Weekday([Enter Start Date of ILT], 2) + 1

 

[Ron2006]

iif(datepart("w",[Start Date])=1,[Start Date]-6,[Start Date] -
(Datepart("w",[Start Date])-2))

Watch for line wrap.

Basically: If the day of the week is sunday give me last monday else
give me the entered date less the day of the week minus 2.

If you enter monday it will subtract (2 - 2) {or 0} days from the
entered date.
If you enter Wednesday it will subtract (4-2) {or 2} days from the
entered date

Ron

 

[Cornfused]

Mr. Browne,

My hat is off to you. Thank you so much for this tidbit of declaring the
parameters in the query. In all of my formal education with databases,
nobody has ever even touched on that simply piece.

Thanks for opening my eyes that much more.

JET says "too complex" when it can't understand waht you are doing. You have
heaps of nested IIf(), Format() which generates strings, and probably an
undeclared (and so untyped) parameter.

1. Declare the parameter
Choose Parameters on the Query menu (in query design.)
Access opens a dialog. Enter:
[Enter Start Date of ILT] Date/Time

2. Simplify the expression.
If I understand you correctly you want to return the Monday of the start of
the week. Something like this might work in the Criteria row under your
date/time field:
[Enter Start Date of ILT] - Weekday([Enter Start Date of ILT], 2) + 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a parameter query with the field [Start_Date] set as one of the user
input fields. For criteria, I've entered the following:

IIf(Format([Enter Start Date of ILT],"dddd")="Tuesday",([Enter Start Date
of
ILT]-1),IIf(Format([Enter Start Date of ILT],"dddd")="Wednesday",([Enter
Start Date of ILT]-2),IIf(Format([Enter Start Date of
ILT],"dddd")="Thursday",([Enter Start Date of ILT]-3),IIf(Format([Enter
Start
Date of ILT],"dddd")="Friday",([Enter Start Date of ILT]-4),[Enter Start
Date
of ILT]))))

So, I get the error that the calculation is too complex or that the syntax
is bad. When I did some experimentation to try to locate the problem
area,
it seems that the Format function may be the culprit.

Please make suggestions as to how I can do this differently and get the
same
result - that of setting the [Start_Date] to the most recent Monday
regardless of the date entered by the user.

Thanks!