asked on

MS ACCESS update query with multiple criteria

This "allmost" works:
Ocassionally incorrect data will be uploaded (_, am, pm).
Most of the time correctly formated data will be uploaded.

Update To:
IIf([P_JOBTIME]="_","3:00 AM",
IIf([P_JOBTIME]=" am","3:00 AM",
IIf([P_JOBTIME]=" pm","3:00 AM",)))

This finds the criteria and updates it correctly BUT it deletes any record not meeting the criteria.
Meaning ligitimate data that does not need be touched is deleted.
How do I maintain the legitimate data?

Thanks for the help,
Ralph

clarkscott

I think if you delete the last comma (the last nested ELSE) it might work???
IIf([P_JOBTIME]=" pm","3:00 AM",)))
---------------------------------------|--------------
Scott C

peter57r

Sorry- but you are looking at something incorrectly somewhere.

There is no possibility of Access deleting any records in an update query.

clarkscott

I assumed the Ralph meant that the data was being 'blanked out' - not entire record deleted.

Is my assumption correct?
S

RalphHxyz

ASKER

"There is no possibility of Access deleting any records in an update query"
If so why am I asking the question?
That's about the stupidest "Expert" reply I have ever seen.
Ralph

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RalphHxyz

ASKER

Is blanked out different from deleted?
I have closed and reopened the table but the "good" records are not there!
Some thing about my Update Query that I have found out so far.
IIF() is supposedly limited to two criteria. That should be noted.
For more than two criteria one should use a SWITCH() function.
SWITCH([P_JOBTIME]="_","3:00 AM",[P_JOBTIME]=" am","3:00 AM",[P_JOBTIME]=" pm","3:00 AM")
But I am still deleting good data (with a relly puzzelling modification see the pictures attached the 5:30 am record remained all of the other records have been deleted except those that met the criteria)
I have been using ACCESS for 15 years and I agree a UPDATE query does not "normally" delete records.
Ralph

1-Table-Start.png
2-Query.png
3-Prompt.png
4-Results.png

Rey Obrero (Capricorn1)

RalphHxyz,

try my post at http:#a24108548

RalphHxyz

ASKER

Adding [P_JOBTIME]) deletes all of the records even those that meet the criteria.
UPDATE qryDetail_Crosstab
SET qryDetail_Crosstab.P_JOBTIME = Switch([P_JOBTIME]="_","3:00 AM",
[P_JOBTIME]=" am","3:00 AM",
[P_JOBTIME]=" pm","3:00 AM",[P_JOBTIME]);
Ralph

5-Prompt2.png
6-Results.gif

RalphHxyz

ASKER

capricorn, you da man!!
UPDATE qryDetail_Crosstab
SET qryDetail_Crosstab.P_JOBTIME = IIf([P_JOBTIME]="_","3:00 AM",
IIf([P_JOBTIME]=" am","3:00 AM",
IIf([P_JOBTIME]=" pm","3:00 AM",[P_JOBTIME])));
Works!!
Thanks so much,
RAlph