Discussions

Expand all | Collapse all

Use IF text lookup in a numeric formula

  • 1.  Use IF text lookup in a numeric formula

    Posted 01-29-2019 22:48
    Hi,

    I have a numeric formula that calculates the amount of hours that have passed if a date/time exceeds 24 hours.

    If([Delivery (hours)]>24,[Delivery (hours)]-24)

    I would like to add the condition that if the field [Priority] matches a certain text that it change the result. Something like this:

    If(
    [Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24),
    [Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)

    However because I'm now calling on a Text field, QB complains about it because this is a Numeric formula.

    If I convert the formula to Text, now I can't calculate the numbers.

    Can anyone help me with a way to do this?


  • 2.  RE: Use IF text lookup in a numeric formula

    Posted 01-29-2019 23:03
    I'm confused, What is wrong with the formula you have as a formula numeric field.  Why do you feel it needs to not be a numeric field type?



  • 3.  RE: Use IF text lookup in a numeric formula

    Posted 01-29-2019 23:19
    After the first AND in the second row QB says "expecting ," and that there are "extra characters beyond the end of the formula". I assume that means that it doesn't like the AND function and concluded it must be because [Priority] is Text in a Numeric formula.


  • 4.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 00:19
    no its miffed because the formula is not passing the syntax requirements due to mis-matched brackets

    I removed the trailing bracket on the line 2 below, as the IF does not end until the end of the formula.

    If(
    [Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24,
    [Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)


  • 5.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 15:48
    I noticed the trailing bracket too and removed it, I still get the same error. I copy/pasted your formula above just to be sure... I really don't know then why this formula won't work.


  • 6.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 16:01
    Please post your code and the error message


  • 7.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 17:07
    I figured it out. It's that "AND" is capitalized. I made them lowercase and now it lets me save the formula without errors.

    I had no idea that this could cause conflict. Good to know.


  • 8.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 17:46
    Both upper and lower case and AND will both work identically in a formula so that cannot have been the problem, but I'm glad it's now working for you.

    If you do not believe me, then change the AND to and, it will still work  


  • 9.  RE: Use IF text lookup in a numeric formula

    Posted 01-30-2019 18:51
    I've definitely noticed that the syntax highlighting stuff doesn't always indicate an actual error, and that you can still save and have a working formula. It can be pretty misleading.


  • 10.  RE: Use IF text lookup in a numeric formula

    Posted 01-31-2019 00:29
    Unfortunately it highlights functions with incorrect capitalization, such as isnull would be highlighted as it will be corrected to IsNull on save.  or dbid() will be highlighted.   So those syntax "errors" which will be corrected when the formula saves should not be flagged as warnings.