Parameter prompt where there shouldn't be

[Tom]

I have an update query that is supposed to update one local table from another
local table:

UPDATE
tblDmdYearlyReviewByMonthSummary RIGHT JOIN
tblDmdYearlyReviewByMonthSummary_KW ON
tblDmdYearlyReviewByMonthSummary.[Loc ID] =
tblDmdYearlyReviewByMonthSummary_KW.[Loc ID]

SET
tblDmdYearlyReviewByMonthSummary.[# Dem] = qryDmdYearlyReviewByMonth_KW.[#
Dem],
tblDmdYearlyReviewByMonthSummary.[Avg Dem] = qryDmdYearlyReviewByMonth_KW.[Avg
Dem];

This is similar to several others I have for this particular job. They run
without incident. But when I try to execute this one, I get an "Enter Parameter
Value" prompt with "qryDmdYearlyReviewByMonth_KW.# Dem" displayed above the
input field.

I'm not asking for a value. I've checked the parameter window, and it's empty.
Then I thought perhaps Access (2000) didn't like the "#" sign. Changing this
hasn't changed getting the prompt. I don't know why this is happening. Does
anyone else have an idea/suggestion?

I'm beginning to think perhaps the parameter prompt I'm getting is merely a
symptom of a different problem. "Avg Dem" is a Long Int (auto) in the summary
table I wish to update. It's a decimal (4,0) in the temp table I'm getting the
value from. Could *this* be a problem?

Any help would be appreciated. Thanks in advance,

Tom

 

[John Vinson]

I have an update query that is supposed to update one local table from another
local table:

UPDATE
tblDmdYearlyReviewByMonthSummary RIGHT JOIN
tblDmdYearlyReviewByMonthSummary_KW ON
tblDmdYearlyReviewByMonthSummary.[Loc ID] =
tblDmdYearlyReviewByMonthSummary_KW.[Loc ID]

SET
tblDmdYearlyReviewByMonthSummary.[# Dem] = qryDmdYearlyReviewByMonth_KW.[#
Dem],
tblDmdYearlyReviewByMonthSummary.[Avg Dem] = qryDmdYearlyReviewByMonth_KW.[Avg
Dem];

This is similar to several others I have for this particular job. They run
without incident. But when I try to execute this one, I get an "Enter Parameter
Value" prompt with "qryDmdYearlyReviewByMonth_KW.# Dem" displayed above the
input field.

You don't have qryDmdYearlyReviewByMonth_KW included in your UPDATE
clause - only in the SET clause. Do you have a typo, tbl for qry?

John W. Vinson[MVP]

 

[Michel Walsh]

Hi,


I suspect your query

qryDmdYearlyReviewByMonth_KW


CANNOT run by itself and that it is that query that has the problem. Is
there the illegal field name # Dem, without its [ ] delimiter? or is it
[#Dem], or [# Dem].


Sounds to me it is a problem of using illegal field names: those with a
space, with a punctuation, including - seen as subtraction operator, and #,
in your case, those not starting with a letter, or those as their name a
reserved name (SELECT, FROM, ... ), or ambiguity (DATE and DATE() ).


Hoping it may help,
Vanderghast, Access MVP

 

[Tom]

Typo? Yes. Which I found after I sent this initial request for help. But
fixing the typo didn't fix the problem. I had to shorten my field names. I
guess there really is such a thing as "too long"

I have an update query that is supposed to update one local table from another
local table:

UPDATE
tblDmdYearlyReviewByMonthSummary RIGHT JOIN
tblDmdYearlyReviewByMonthSummary_KW ON
tblDmdYearlyReviewByMonthSummary.[Loc ID] =
tblDmdYearlyReviewByMonthSummary_KW.[Loc ID]

SET
tblDmdYearlyReviewByMonthSummary.[# Dem] = qryDmdYearlyReviewByMonth_KW.[#
Dem],
tblDmdYearlyReviewByMonthSummary.[Avg Dem] = qryDmdYearlyReviewByMonth_KW.[Avg
Dem];

This is similar to several others I have for this particular job. They run
without incident. But when I try to execute this one, I get an "Enter Parameter
Value" prompt with "qryDmdYearlyReviewByMonth_KW.# Dem" displayed above the
input field.

You don't have qryDmdYearlyReviewByMonth_KW included in your UPDATE
clause - only in the SET clause. Do you have a typo, tbl for qry?

John W. Vinson[MVP]