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

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    6th April 2020 - 10:42 AM