Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS ACCESS update query with multiple criteria

Posted on 2009-04-09
9
Medium Priority
?
1,284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

618 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