Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Where = Where & how to approach results I want

    I have an invoices sub-query that I use in querydef

    There is a field BAL in the query which returns:
    a.) blank if nothing's been paid on the invoice
    b.) dollar amount that is balance due (in currency format)
    c.) $0.00 if it's paid in full (in currency format)

    The expression is done in the previous query as:
    BAL: [QryPPD.INVAMT]-[PD]

    I am trying to write a where clause for my querydef to return the blanks or the dollar amounts which in sql is
    WHERE ((([QryPPD.INVAMT]-[PD]) Is Null)) OR ((([QryPPD.INVAMT]-[PD])>0));

    I have tried this but the query is returning all results.....not sure why
    Code:
    where = where & " AND IsNull([BAL])"
    My questions are - should I write the expression in VBA like it shows in sql?
    Is currency considered text?
    Why wouldn't the IsNull work?

    Any responses are greatly appreciated!!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off separate presentation from the data
    don't push string representations of numeric data around, disdpaly them using froamt where required

    the logic [qiote]a.) blank if nothing's been paid on the invoice
    b.) dollar amount that is balance due (in currency format)
    c.) $0.00 if it's paid in full (in currency format)[/quote] seems flaky to me
    either their is a balance on the account or there isn't so option 1 doesn't make sense to me.
    the third option is usually returning a balance if its outstanding. ie should have received payment by now. and one way of doing that is to say change the display. if you have access to colour printers then they are a good way to go. however a surefire technique is to say change the background (ive often used a dark background with pale letters to indicate this sort of issue and its printer independant). however if you are sendingthe document to a custoemr by pdf or other electronic means then colour considerations change.

    and what is the value of where after you have built this clause
    again its impossible to tell what is going on without seeing the actual value of the SQL being passed to the SQL engine
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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