Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ Update One Field Based On Multiple Criteria In One Query

Posted by: mmadani Nov 11 2019, 03:44 PM

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




Posted by: RJD Nov 11 2019, 04:57 PM

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 ...


Posted by: Doug Steele Nov 11 2019, 05:00 PM

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?

Posted by: mmadani Nov 11 2019, 05:25 PM

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

Posted by: mmadani Nov 11 2019, 05:28 PM

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

Let me know

Posted by: mmadani Nov 11 2019, 05:30 PM

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

My typo was the issue


Posted by: RJD Nov 11 2019, 06:01 PM

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.