MS Jet Engine does not recognize as valid field or expression

[Glencannon4424 via AccessMonster.com]

Hello,

I'm built a series of calculation queries based off one table. The one
commonality between my queries is an employee ID. I entered a prompt into
the very first query: [Please enter ID:]. This first query runs without a
hitch.

When I try to run a calculation query off on my first query, I get "MS Jet
Engine does not recognize [Please enter ID:] as a valid field or expression".
If I remove the prompt, this second query runs without a hitch. Any thoughts
on why this might be? Both seem to be plain-vanilla select queries . . .

1st query (qryHoursbyWeek_Cumulative):
SELECT tblHOURSbyWEEK.ID, tblHOURSbyWEEK.NAME, tblHOURSbyWEEK.[YR-WEEK], Sum
(tblHOURSbyWEEK.MONDAY) AS MON, Sum(tblHOURSbyWEEK.TUESDAY) AS TUES, Sum
(tblHOURSbyWEEK.WEDNESDAY) AS WED, Sum(tblHOURSbyWEEK.MONDAY) AS ThruMonday,
Sum([MONDAY]+[TUESDAY]) AS ThruTuesday, Sum([MONDAY]+[TUESDAY]+[WEDNESDAY])
AS ThruWednesday, FROM tblHOURSbyWEEK
GROUP BY tblHOURSbyWEEK.ID, tblHOURSbyWEEK.NAME, tblHOURSbyWEEK.[YR-WEEK]
HAVING (((tblHOURSbyWEEK.ID)=[Please enter ID:]));

2nd query:
SELECT qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK], IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON]))
AS RULE1_1_MONDAY, IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])) AS
RULE1_1_TUESDAY, IIf([WEDNESDAY]=-1,0,IIf([WED]>8,8,[WED])) AS
RULE1_1_WEDNESDAY, IIf([MON]<8 Or [MONDAY]=-1,0,IIf([MON]>10,2,[MON]-8)) AS
[RULE1_1&1/2_MONDAY], IIf([TUES]<8 Or [TUESDAY]=-1,0,IIf([TUES]>10,2,[TUES]-8)
) AS [RULE1_1&1/2_TUESDAY], IIf([WED]<8 Or [WEDNESDAY]=-1,0,IIf([WED]>10,2,
[WED]-8)) AS [RULE1_1&1/2_WEDNESDAY], IIf([MONDAY]=-1,[MON],IIf([MON]>10,[MON]
-10,0)) AS RULE1_2_MONDAY, IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,[TUES]-10,0))
AS RULE1_2_TUESDAY, IIf([WEDNESDAY]=-1,[WED],IIf([WED]>10,[WED]-10,0)) AS
RULE1_2_WEDNESDAY,
FROM qryHoursByWeek_Cumulative INNER JOIN qryHoliday_Cumulative ON
qryHoursByWeek_Cumulative.[YR-WEEK] = qryHoliday_Cumulative.[YR-WEEK]
GROUP BY qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK], IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON])),
IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])), IIf([WEDNESDAY]=-1,0,IIf([WED]>8,
8,[WED])), IIf([MON]<8 Or [MONDAY]=-1,0,IIf([MON]>10,2,[MON]-8)), IIf([TUES]
<8 Or [TUESDAY]=-1,0,IIf([TUES]>10,2,[TUES]-8)), IIf([WED]<8 Or [WEDNESDAY]=-
1,0,IIf([WED]>10,2,[WED]-8)), IIf([MONDAY]=-1,[MON],IIf([MON]>10,[MON]-10,0)),
IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,[TUES]-10,0)), IIf([WEDNESDAY]=-1,[WED],
IIf([WED]>10,[WED]-10,0)).

thanks for your assistance . . . .

 

[John Spencer]

I suspect that you have a cross tab query buried in one of your queries.

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

 

[Glencannon4424 via AccessMonster.com]

My source table was created off a cross-tab query, yes. I changed the
parameters
Column 1: ID
Column 2: Text

When I ran the queries, it kept prompting for "ID" (not my original prompt
which was "Please enter ID:") and, when the results appeared, they included
the results for all IDs in my query, instead of just the ID I had specified.

I'm guessing I'm not doing something right here . . . ..

 

[John Spencer]

Put in your PROMPT in the parameter. Is your Prompt "ID" or is it "Please Enter ID"

If you can't get this straight, then post the SQL of the modified query.

 

[Glencannon4424 via AccessMonster.com]

I changed the parameters to:
Column 1: Please enter ID:
Column 2: Text

When I run the queries, it keeps prompting "Please enter ID:"). The results
continue to include
the results for all IDs in my query, instead of just the ID I had specified.
Do I need to write anything in the criteria?

qry 1
PARAMETERS [Please enter ID:] Text ( 255 );
SELECT [tblHOURSbyWEEK].[ID], [tblHOURSbyWEEK].[NAME], [tblHOURSbyWEEK].[YR-
WEEK], Sum([tblHOURSbyWEEK].[MONDAY]) AS MON, Sum([tblHOURSbyWEEK].[TUESDAY])
AS TUES, Sum([tblHOURSbyWEEK].[WEDNESDAY]) AS WED, Sum([tblHOURSbyWEEK].
[THURSDAY]) AS THURS, Sum([tblHOURSbyWEEK].[FRIDAY]) AS FRI, Sum(
[tblHOURSbyWEEK].[SATURDAY]) AS SAT, Sum([tblHOURSbyWEEK].[SUNDAY]) AS SUN,
Sum([tblHOURSbyWEEK].[MONDAY]) AS ThruMonday, Sum([MONDAY]+[TUESDAY]) AS
ThruTuesday, Sum([MONDAY]+[TUESDAY]+[WEDNESDAY]) AS ThruWednesday, Sum(
[MONDAY]+[TUESDAY]+[WEDNESDAY]+[THURSDAY]) AS ThruThursday, Sum([MONDAY]+
[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]) AS ThruFriday, Sum([MONDAY]+
[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]+[SATURDAY]) AS ThruSaturday, Sum(
[MONDAY]+[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]+[SATURDAY]+[SUNDAY]) AS
ThruSunday
FROM tblHOURSbyWEEK
GROUP BY [tblHOURSbyWEEK].[ID], [tblHOURSbyWEEK].[NAME], [tblHOURSbyWEEK].[YR-
WEEK];


qry 2
SELECT qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK], IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON]))
AS RULE1_1_MONDAY, IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])) AS
RULE1_1_TUESDAY, IIf([WEDNESDAY]=-1,0,IIf([WED]>8,8,[WED])) AS
RULE1_1_WEDNESDAY, IIf([THURSDAY]=-1,0,IIf([THURS]>8,8,[THURS])) AS
RULE1_1_THURSDAY, IIf([FRIDAY]=-1,0,IIf([FRI]>8,8,[FRI])) AS RULE1_1_FRIDAY,
IIf([SAT]>8,8,[SAT]) AS RULE1_1_SATURDAY, IIf([MON]<8 Or [MONDAY]=-1,0,IIf(
[MON]>10,2,[MON]-8)) AS [RULE1_1&1/2_MONDAY], IIf([TUES]<8 Or [TUESDAY]=-1,0,
IIf([TUES]>10,2,[TUES]-8)) AS [RULE1_1&1/2_TUESDAY], IIf([WED]<8 Or
[WEDNESDAY]=-1,0,IIf([WED]>10,2,[WED]-8)) AS [RULE1_1&1/2_WEDNESDAY], IIf(
[THURS]<8 Or [THURSDAY]=-1,0,IIf([THURS]>10,2,[THURS]-8)) AS
[RULE1_1&1/2_THURSDAY], IIf([FRI]<8 Or [FRIDAY]=-1,0,IIf([FRI]>10,2,[FRI]-8))
AS [RULE1_1&1/2_FRIDAY], IIf([SAT]<8,0,IIf([SAT]>10,2,[SAT]-8)) AS
[RULE1_1&1/2_SATURDAY], IIf([MONDAY]=-1,[MON],IIf([MON]>10,[MON]-10,0)) AS
RULE1_2_MONDAY, IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,[TUES]-10,0)) AS
RULE1_2_TUESDAY, IIf([WEDNESDAY]=-1,[WED],IIf([WED]>10,[WED]-10,0)) AS
RULE1_2_WEDNESDAY, IIf([THURSDAY]=-1,[THURS],IIf([THURS]>10,[THURS]-10,0)) AS
RULE1_2_THURSDAY, IIf([FRIDAY]=-1,[FRI],IIf([FRI]>10,[FRI]-10,0)) AS
RULE1_2_FRIDAY, IIf([SAT]>10,[SAT]-10,0) AS RULE1_2_SATURDAY,
qryHoursByWeek_Cumulative.SUN AS RULE1_2_SUNDAY
FROM qryHoursByWeek_Cumulative INNER JOIN qryHoliday_Cumulative ON
qryHoursByWeek_Cumulative.[YR-WEEK] = qryHoliday_Cumulative.[YR-WEEK]
GROUP BY qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK], IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON])),
IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])), IIf([WEDNESDAY]=-1,0,IIf([WED]>8,
8,[WED])), IIf([THURSDAY]=-1,0,IIf([THURS]>8,8,[THURS])), IIf([FRIDAY]=-1,0,
IIf([FRI]>8,8,[FRI])), IIf([SAT]>8,8,[SAT]), IIf([MON]<8 Or [MONDAY]=-1,0,IIf
([MON]>10,2,[MON]-8)), IIf([TUES]<8 Or [TUESDAY]=-1,0,IIf([TUES]>10,2,[TUES]-
8)), IIf([WED]<8 Or [WEDNESDAY]=-1,0,IIf([WED]>10,2,[WED]-8)), IIf([THURS]<8
Or [THURSDAY]=-1,0,IIf([THURS]>10,2,[THURS]-8)), IIf([FRI]<8 Or [FRIDAY]=-1,0,
IIf([FRI]>10,2,[FRI]-8)), IIf([SAT]<8,0,IIf([SAT]>10,2,[SAT]-8)), IIf([MONDAY]
=-1,[MON],IIf([MON]>10,[MON]-10,0)), IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,
[TUES]-10,0)), IIf([WEDNESDAY]=-1,[WED],IIf([WED]>10,[WED]-10,0)), IIf(
[THURSDAY]=-1,[THURS],IIf([THURS]>10,[THURS]-10,0)), IIf([FRIDAY]=-1,[FRI],
IIf([FRI]>10,[FRI]-10,0)), IIf([SAT]>10,[SAT]-10,0),
qryHoursByWeek_Cumulative.SUN;

Put in your PROMPT in the parameter. Is your Prompt "ID" or is it "Please Enter ID"

If you can't get this straight, then post the SQL of the modified query.

My source table was created off a cross-tab query, yes. I changed the
parameters

[quoted text clipped - 16 lines]

 

[John Spencer]

Yes. You do need to keep the WHERE or HAVING clause. Declaring the
parameter only tells the query what data type the parameter is, it does not
tell the query where or how to use the parameter.

HAVING (((tblHOURSbyWEEK.ID)=[Please enter ID:]))

For efficiency you should be able to move that HAVING statement into a WHERE
clause. The criteria in a HAVING clause gets applied after all the
grouping, summing, etc has taken place. The Where clause gets applied
before the grouping, summing, etc.

If you are building your query in the query grid, then add tblHoursByWeek.Id
to the grid a second time, change the total line to "WHERE", uncheck show,
and finally put the "[Please enter ID:] in the criteria for this field.
Remove the criteria from under the first tblHoursByWeek.Id and simply
"Group" on the field.

I changed the parameters to:
Column 1: Please enter ID:
Column 2: Text

When I run the queries, it keeps prompting "Please enter ID:"). The
results
continue to include
the results for all IDs in my query, instead of just the ID I had
specified.
Do I need to write anything in the criteria?

qry 1
PARAMETERS [Please enter ID:] Text ( 255 );
SELECT [tblHOURSbyWEEK].[ID], [tblHOURSbyWEEK].[NAME],
[tblHOURSbyWEEK].[YR-
WEEK], Sum([tblHOURSbyWEEK].[MONDAY]) AS MON,
Sum([tblHOURSbyWEEK].[TUESDAY])
AS TUES, Sum([tblHOURSbyWEEK].[WEDNESDAY]) AS WED, Sum([tblHOURSbyWEEK].
[THURSDAY]) AS THURS, Sum([tblHOURSbyWEEK].[FRIDAY]) AS FRI, Sum(
[tblHOURSbyWEEK].[SATURDAY]) AS SAT, Sum([tblHOURSbyWEEK].[SUNDAY]) AS
SUN,
Sum([tblHOURSbyWEEK].[MONDAY]) AS ThruMonday, Sum([MONDAY]+[TUESDAY]) AS
ThruTuesday, Sum([MONDAY]+[TUESDAY]+[WEDNESDAY]) AS ThruWednesday, Sum(
[MONDAY]+[TUESDAY]+[WEDNESDAY]+[THURSDAY]) AS ThruThursday, Sum([MONDAY]+
[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]) AS ThruFriday, Sum([MONDAY]+
[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]+[SATURDAY]) AS ThruSaturday,
Sum(
[MONDAY]+[TUESDAY]+[WEDNESDAY]+[THURSDAY]+[FRIDAY]+[SATURDAY]+[SUNDAY]) AS
ThruSunday
FROM tblHOURSbyWEEK
GROUP BY [tblHOURSbyWEEK].[ID], [tblHOURSbyWEEK].[NAME],
[tblHOURSbyWEEK].[YR-
WEEK];


qry 2
SELECT qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK],
IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON]))
AS RULE1_1_MONDAY, IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])) AS
RULE1_1_TUESDAY, IIf([WEDNESDAY]=-1,0,IIf([WED]>8,8,[WED])) AS
RULE1_1_WEDNESDAY, IIf([THURSDAY]=-1,0,IIf([THURS]>8,8,[THURS])) AS
RULE1_1_THURSDAY, IIf([FRIDAY]=-1,0,IIf([FRI]>8,8,[FRI])) AS
RULE1_1_FRIDAY,
IIf([SAT]>8,8,[SAT]) AS RULE1_1_SATURDAY, IIf([MON]<8 Or
[MONDAY]=-1,0,IIf(
[MON]>10,2,[MON]-8)) AS [RULE1_1&1/2_MONDAY], IIf([TUES]<8 Or
[TUESDAY]=-1,0,
IIf([TUES]>10,2,[TUES]-8)) AS [RULE1_1&1/2_TUESDAY], IIf([WED]<8 Or
[WEDNESDAY]=-1,0,IIf([WED]>10,2,[WED]-8)) AS [RULE1_1&1/2_WEDNESDAY], IIf(
[THURS]<8 Or [THURSDAY]=-1,0,IIf([THURS]>10,2,[THURS]-8)) AS
[RULE1_1&1/2_THURSDAY], IIf([FRI]<8 Or
[FRIDAY]=-1,0,IIf([FRI]>10,2,[FRI]-8))
AS [RULE1_1&1/2_FRIDAY], IIf([SAT]<8,0,IIf([SAT]>10,2,[SAT]-8)) AS
[RULE1_1&1/2_SATURDAY], IIf([MONDAY]=-1,[MON],IIf([MON]>10,[MON]-10,0)) AS
RULE1_2_MONDAY, IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,[TUES]-10,0)) AS
RULE1_2_TUESDAY, IIf([WEDNESDAY]=-1,[WED],IIf([WED]>10,[WED]-10,0)) AS
RULE1_2_WEDNESDAY, IIf([THURSDAY]=-1,[THURS],IIf([THURS]>10,[THURS]-10,0))
AS
RULE1_2_THURSDAY, IIf([FRIDAY]=-1,[FRI],IIf([FRI]>10,[FRI]-10,0)) AS
RULE1_2_FRIDAY, IIf([SAT]>10,[SAT]-10,0) AS RULE1_2_SATURDAY,
qryHoursByWeek_Cumulative.SUN AS RULE1_2_SUNDAY
FROM qryHoursByWeek_Cumulative INNER JOIN qryHoliday_Cumulative ON
qryHoursByWeek_Cumulative.[YR-WEEK] = qryHoliday_Cumulative.[YR-WEEK]
GROUP BY qryHoursByWeek_Cumulative.ID, qryHoursByWeek_Cumulative.NAME,
qryHoursByWeek_Cumulative.[YR-WEEK],
IIf([MONDAY]=-1,0,IIf([MON]>8,8,[MON])),
IIf([TUESDAY]=-1,0,IIf([TUES]>8,8,[TUES])),
IIf([WEDNESDAY]=-1,0,IIf([WED]>8,
8,[WED])), IIf([THURSDAY]=-1,0,IIf([THURS]>8,8,[THURS])),
IIf([FRIDAY]=-1,0,
IIf([FRI]>8,8,[FRI])), IIf([SAT]>8,8,[SAT]), IIf([MON]<8 Or
[MONDAY]=-1,0,IIf
([MON]>10,2,[MON]-8)), IIf([TUES]<8 Or
[TUESDAY]=-1,0,IIf([TUES]>10,2,[TUES]-
8)), IIf([WED]<8 Or [WEDNESDAY]=-1,0,IIf([WED]>10,2,[WED]-8)),
IIf([THURS]<8
Or [THURSDAY]=-1,0,IIf([THURS]>10,2,[THURS]-8)), IIf([FRI]<8 Or
[FRIDAY]=-1,0,
IIf([FRI]>10,2,[FRI]-8)), IIf([SAT]<8,0,IIf([SAT]>10,2,[SAT]-8)),
IIf([MONDAY]
=-1,[MON],IIf([MON]>10,[MON]-10,0)),
IIf([TUESDAY]=-1,[TUES],IIf([TUES]>10,
[TUES]-10,0)), IIf([WEDNESDAY]=-1,[WED],IIf([WED]>10,[WED]-10,0)), IIf(
[THURSDAY]=-1,[THURS],IIf([THURS]>10,[THURS]-10,0)),
IIf([FRIDAY]=-1,[FRI],
IIf([FRI]>10,[FRI]-10,0)), IIf([SAT]>10,[SAT]-10,0),
qryHoursByWeek_Cumulative.SUN;

Put in your PROMPT in the parameter. Is your Prompt "ID" or is it "Please
Enter ID"

If you can't get this straight, then post the SQL of the modified query.

My source table was created off a cross-tab query, yes. I changed the
parameters

[quoted text clipped - 16 lines]

Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2