MS ACCESS update query with multiple criteria

Posted on 2009-04-09
Last Modified: 2012-05-06
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,
Question by:RalphHxyz
  • 4
  • 2
  • 2
  • +1
LVL 20

Expert Comment

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

Expert Comment

ID: 24108338
Sorry- but you are looking at something incorrectly somewhere.

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

Expert Comment

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

Is my assumption correct?

Author Comment

ID: 24108478
"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.
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 24108548

update tableX
set [P_JOBTIME]=IIf([P_JOBTIME]="_","3:00 AM",
IIf([P_JOBTIME]=" am","3:00 AM",
IIf([P_JOBTIME]=" pm","3:00 AM",[P_JOBTIME])))

Author Comment

ID: 24108780
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.

LVL 119

Expert Comment

by:Rey Obrero
ID: 24108893

try my post at http:#a24108548

Author Comment

ID: 24108922
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]);


Author Comment

ID: 24109091
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])));
Thanks so much,

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot Table for a join with partition by and over clause 7 27
Library not Registered 16 49
Create Form using Wizard 14 33
Get Duration of last Status Update 4 31
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now