?
Solved

MS ACCESS update query with multiple criteria

Posted on 2009-04-09
9
Medium Priority
?
1,288 Views
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,
Ralph
0
Comment
Question by:RalphHxyz
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 20

Expert Comment

by:clarkscott
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
0
 
LVL 77

Expert Comment

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

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

Expert Comment

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

Is my assumption correct?
S
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:RalphHxyz
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.
Ralph
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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])))
0
 

Author Comment

by:RalphHxyz
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.
Ralph

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24108893
RalphHxyz,

try my post at http:#a24108548
0
 

Author Comment

by:RalphHxyz
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]);
Ralph

5-Prompt2.png
6-Results.gif
0
 

Author Comment

by:RalphHxyz
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])));
Works!!
Thanks so much,
RAlph
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

594 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