X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Update One Field Based On Multiple Criteria In One Query, Access 2007    
post Nov 11 2019, 03:44 PM

Posts: 397
Joined: 27-June 08

I got it working in Excel and never had trouble with this until now. Something I am not seeing

I have a field (text) has a total charge and the Tip of that charge show up on another row. Same field though.

I wrote an IIF statement as such (IIF("[transaction amount] <5",'TIP','FARE')) . I did not think of needing to put this in an update query, or a case statement. This worked well in Excel !!

See attached it is assigning label incorrectly i.e. : Fare is labeled as TIP and vice versa

I would like to see a few options to do this .I would appreciate some code examples also



Attached File(s)
Attached File  TipTable.JPG ( 16.03K )Number of downloads: 0
Go to the top of the page
Start new topic
Doug Steele
post Nov 11 2019, 05:00 PM

UtterAccess VIP
Posts: 22,263
Joined: 8-January 07
From: St. Catharines, ON (Canada)

It should work the same in Access.

What's the exact scenario: have you set the ControlSource of a form to the IIf statement you mentioned? What's the RecordSource of the form?

Any chance you can create a sample database with only the form, demonstrating the problem, and post it here?

Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Nov 11 2019, 05:25 PM

Posts: 397
Joined: 27-June 08

Joe and Doug Happy veterans day.

Should the desired updated to value be in double Quotes instead of a single quote ? I think That is my mistake. at any rate it was a table with transactions from UBER but the transaction type is not listed for an employee to be able to expense.
Had to distinguish between the tip and the Fare. All text field as they come from Uber.


I used the update query with a switch function like this : Switch([Fare in USD (excl# Taxes)] <=5,"TIP",[Fare in USD (excl# Taxes)] >5,"FARE") and updated the table and it worked like a charm.

[Fare in USD (excl# Taxes)] is the actual field name . Thank you guys for Jumping in I appreciate it. I would like to try both of your methods to add to the Knowledge.

Many thanks

Mike notworthy.gif
This post has been edited by mmadani: Nov 11 2019, 05:27 PM
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    30th March 2020 - 05:52 PM