UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update One Field Based On Multiple Criteria In One Query, Access 2007    
 
   
mmadani
post Nov 11 2019, 03:44 PM
Post#1



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

Thanks

Mike




Attached File(s)
Attached File  TipTable.JPG ( 16.03K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Nov 11 2019, 04:57 PM
Post#2


UtterAccess VIP
Posts: 10,157
Joined: 25-October 10
From: Gulf South USA


Hi: Your attachment graphic seems to suggest that [Transaction Amount] is being read as text rather than numeric. Hard to tell with limited information, but that might be possible (e.g. reading 11.73 as less than 5). Plus, the quotes around the logic part of the IIf statement is incorrect.

You can first try, in a field in the query ...

Type: IIf([Transaction Amount]<5,"Tip","Fare")

If this doesn't work, try forcing the [Transaction Amount] to be numeric ...

Type: IIf(CCur([Transaction Amount])<5,"Tip","Fare")

If you are doing this in a control on a form or report instead ...

=IIf(CDbl([Transaction Amount])<5,"Tip","Fare") ... or you might use CCur instead ...

Try some of these and see if you can get the results you want ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Doug Steele
post Nov 11 2019, 05:00 PM
Post#3


UtterAccess VIP
Posts: 22,228
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
 
mmadani
post Nov 11 2019, 05:25 PM
Post#4



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.

Solution:

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
 
mmadani
post Nov 11 2019, 05:28 PM
Post#5



Posts: 397
Joined: 27-June 08



Doug, I could post the database if you want me to.

Let me know
Thanks
Mike
Go to the top of the page
 
mmadani
post Nov 11 2019, 05:30 PM
Post#6



Posts: 397
Joined: 27-June 08



IIf([Transaction Amount]<5,"Tip","Fare") This one works fine too.

My typo was the issue

Mike
Go to the top of the page
 
RJD
post Nov 11 2019, 06:01 PM
Post#7


UtterAccess VIP
Posts: 10,157
Joined: 25-October 10
From: Gulf South USA


QUOTE
Should the desired updated to value be in double Quotes instead of a single quote ? I think That is my mistake.

Either single or double quotes is acceptable. Just no quotes around the entire logic part.

And there is no need at all to do the update. In fact, I would not do it, but choose the Fare/Tip result when you need it. The reason for this is in case you make a data entry error for the amount, update the Fare/Tip field, then correct the amount without running the update again. Or if the amount logic changes. Common practice is to just get the words with the IIF (or Switch, etc.) when you need them. There are cases when you would want to store the type, and maybe this is one, but that is fairly rare.

Continued success with your project...

And thank you and a Happy Veterans Day to you as well.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 12:58 PM