Message undesired @ query run

[Guest]

I have a database with the progress history of data:
for same jobnum I have records with increased periodnumber and periodend.
The query create a view with jobnum, periodend and periodnumber of previous
record for records with periodend = last friday:
SELECT jc.Jobnum, jc.periodnumber, Max(jc1.periodnumber) AS PrevPeriod
FROM jobcardprogresshistory AS jc LEFT JOIN jobcardprogresshistory AS jc1 ON
(jc.Jobnum = jc1.Jobnum) AND (jc.periodnumber > nz(jc1.periodnumber))
WHERE jc.periodend=Date()-weekday(date(),7) mod 7
GROUP BY jc.Jobnum, jc.periodnumber;
When I run the query I receive a window with the message:
"SQL Statement.periodend".
1. Why and how can I avoid this?
2. Is other more elegant method to create the query?

Thanx!

 

[John W. Vinson]

On Mon, 23 Apr 2007 08:14:03 -0700, Mihai Zamfirescu <Mihai

I have a database with the progress history of data:
for same jobnum I have records with increased periodnumber and periodend.
The query create a view with jobnum, periodend and periodnumber of previous
record for records with periodend = last friday:
SELECT jc.Jobnum, jc.periodnumber, Max(jc1.periodnumber) AS PrevPeriod
FROM jobcardprogresshistory AS jc LEFT JOIN jobcardprogresshistory AS jc1 ON
(jc.Jobnum = jc1.Jobnum) AND (jc.periodnumber > nz(jc1.periodnumber))
WHERE jc.periodend=Date()-weekday(date(),7) mod 7
GROUP BY jc.Jobnum, jc.periodnumber;
When I run the query I receive a window with the message:
"SQL Statement.periodend".
1. Why and how can I avoid this?
2. Is other more elegant method to create the query?

Thanx!

Evidently there is no field named periodend in the table
jobcardprogresshistory. Check the spelling of the field!

John W. Vinson [MVP]

 

[Guest]

Evidently u r wrong. It is a periodend field (date/time) in the
jobcardprogresshistory table. The query work fine, but anytime I run it I
have to press OK in the window with the message "SQL Statement.periodend" and
with an input field (I put nothing in it).

 

[Guest]

as John pointed out,It is asking for a parameter or field that cannot be
found, check your spelling. you will get more responses in this group if you
are not rude to people who give their time to respond to your problems

 

[John Spencer]

Backup your data first - just in case

I would open the query in SQL view and Copy and paste the SQL into a new
query.
Run the new query.

If you don't get the prompt then I would save the new query over the top of
the old query. If you do get the prompt, then I would definitely check
field and table names for misspellings, extra spaces, etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Evidently u r wrong.

So it would seem. I cannot see your database, I cannot see your table
structure, and I cannot see your query, so I am limited in what I know about
your application.

John W. Vinson [MVP]