Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Formula in MSQuery, as opposed to in Excel (Excel 2002)

    Hi,

    Is it possible to have the formula below in the msquery instead of having it calculate in Excel AFTER the data is downloaded into Excel...

    For example, if G2 is equal to JUL08, then put the dollar amount in this new column, otherwise put 0. Below is the formula I have in Excel... I'd like to make the column in msquery instead. I have used formulas before in msquery like left(FILE NAME,4) and it works, but I can't get an If, Then type formula to work. Is it even possible?

    =IF($G2=$I$1,$F2,0)

    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula in MSQuery, as opposed to in Excel (Excel 2002)

    You can use the IIf function instead if IF in queries:
    <code>
    IIf(Month='JUL08',Amount,0)
    </code>
    where Month is the name of the field you want to test on (corresponding to column G) and Amount is the currency field (corresponding to column F).

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula in MSQuery, as opposed to in Excel (Excel 2002)

    It accepts the formula now, however when I refresh the query it gives me this message:

    SQL0104 - Token = was not valid. Valid tokens,.

    Here is the formula I typed in:

    IIF(BPGDNB='JAN08',BPBAVA,0)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula in MSQuery, as opposed to in Excel (Excel 2002)

    Do you have null values (blanks) in the BPGDNB field? If so, try
    <code>
    IIf(BPGDNB & ''='JAN08',BPBAVA,0)
    </code>
    (That shouldn't really be necessary, but it's worth a try)

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula in MSQuery, as opposed to in Excel (Excel 2002)

    Now it gives me this error message... the other error message said the = token is not valid, and now this one says the & token is not valid. Does this make sense? I would think we'd be able to use the = sign in a formula??

    SQL0104 - Token & was not valid. Valid tokens,.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula in MSQuery, as opposed to in Excel (Excel 2002)

    I have tested the expression using one of my databases and it worked correctly, so I can't help without seeing (stripped down copies of) the database and the workbook.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •