update query with iff

S

seeker

Why doesn't the following query run?

UPDATE (member_time INNER JOIN member_los ON member_time.member_number =
member_los.member_number) INNER JOIN qry_select_los_service ON member_los.los
= qry_select_los_service.los_id SET member_time.service =
qry_select_los_service.service, member_time.pr_staff =
member_los.pr_staff,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

I want the field locnbr to be updated to 1 only if the field service is
prag. Thank you.
 
J

John Spencer

What do you mean by run?

Do you get an error message?

Do you see records but the data is not updated? If that is the case, you
are probably switching to datasheet view. Doing that will show which
records WILL be updated, but it does not update anything.

If you want to execute the query, you must select Query: Run from the
menu or click on the Run button (Red Exclamation). If you do that you
should get a message that you are about to update n records. Do you get
this message?

UPDATE (member_time INNER JOIN member_los
ON member_time.member_number = member_los.member_number)
INNER JOIN qry_select_los_service
ON member_los.los = qry_select_los_service.los_id
SET member_time.service = qry_select_los_service.service
, member_time.pr_staff = member_los.pr_staff
,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

If you get a message that the query is not updateable, I suspect that
qry_select_los_service may be the cause. If it is not updateable then
the update query may not be updateable. You MAY be able to work around
that by changing the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
S

seeker

I fixed the problem in a different way. But to continue this discussion for
knowledge share purposes it appears that the query hangs on the iff
statement. When I delete it from the query the update query updates the
fields as it should. So the question is what is the problem with the iff
statement when found in an update.

John Spencer said:
What do you mean by run?

Do you get an error message?

Do you see records but the data is not updated? If that is the case, you
are probably switching to datasheet view. Doing that will show which
records WILL be updated, but it does not update anything.

If you want to execute the query, you must select Query: Run from the
menu or click on the Run button (Red Exclamation). If you do that you
should get a message that you are about to update n records. Do you get
this message?

UPDATE (member_time INNER JOIN member_los
ON member_time.member_number = member_los.member_number)
INNER JOIN qry_select_los_service
ON member_los.los = qry_select_los_service.los_id
SET member_time.service = qry_select_los_service.service
, member_time.pr_staff = member_los.pr_staff
,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

If you get a message that the query is not updateable, I suspect that
qry_select_los_service may be the cause. If it is not updateable then
the update query may not be updateable. You MAY be able to work around
that by changing the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Why doesn't the following query run?

UPDATE (member_time INNER JOIN member_los ON member_time.member_number =
member_los.member_number) INNER JOIN qry_select_los_service ON member_los.los
= qry_select_los_service.los_id SET member_time.service =
qry_select_los_service.service, member_time.pr_staff =
member_los.pr_staff,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

I want the field locnbr to be updated to 1 only if the field service is
prag. Thank you.
Click to expand...
Click to expand...
 
J

John Spencer

Well part of this is the function is IIF not IFF. Sorry, I should have
spotted that earlier.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I fixed the problem in a different way. But to continue this discussion for
knowledge share purposes it appears that the query hangs on the iff
statement. When I delete it from the query the update query updates the
fields as it should. So the question is what is the problem with the iff
statement when found in an update.

John Spencer said:
What do you mean by run?

Do you get an error message?

Do you see records but the data is not updated? If that is the case, you
are probably switching to datasheet view. Doing that will show which
records WILL be updated, but it does not update anything.

If you want to execute the query, you must select Query: Run from the
menu or click on the Run button (Red Exclamation). If you do that you
should get a message that you are about to update n records. Do you get
this message?

UPDATE (member_time INNER JOIN member_los
ON member_time.member_number = member_los.member_number)
INNER JOIN qry_select_los_service
ON member_los.los = qry_select_los_service.los_id
SET member_time.service = qry_select_los_service.service
, member_time.pr_staff = member_los.pr_staff
,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

If you get a message that the query is not updateable, I suspect that
qry_select_los_service may be the cause. If it is not updateable then
the update query may not be updateable. You MAY be able to work around
that by changing the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Why doesn't the following query run?

UPDATE (member_time INNER JOIN member_los ON member_time.member_number =
member_los.member_number) INNER JOIN qry_select_los_service ON member_los.los
= qry_select_los_service.los_id SET member_time.service =
qry_select_los_service.service, member_time.pr_staff =
member_los.pr_staff,locnbr=iff([service]="PRAG",1,0)
WHERE (((member_time.pr_staff)=0) AND ((member_time.date)>=[begin_service])
AND ((member_los.closed)=No));

I want the field locnbr to be updated to 1 only if the field service is
prag. Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query based on leaving date 1
update with another query or with a join 2
Combining a Select query and an Update query 1
Problem with Update query 6
Update Query with IIF 1
Update Query from table with wildcard values 3
Help with Update query 1
Help with update query, using result of Max query 2

Top