Results 1 to 8 of 8

Thread: help plz

  1. #1
    Join Date
    Aug 2005
    Posts
    18

    Unanswered: help plz

    i have 2 queries totaldues and totalPayments
    totaldues has 2 fileds(custID (Group By)
    amountDue(sum of all invoices dues)
    and totalPayments consist of CustID(Group By) and Amount_Paid(sum of
    all payment)


    thses queries calculate total dues and total payments customer made.
    then i have another query on the base of these 2 queries which is
    called custGrandBalance and it consist of customerID,amountDue(which is
    sum of all dues about this customer), Amount_Paid(sum of all payment),
    TotalDues: ([SumOfamountDue]-[SumOfAmount_Paid])


    this query brings me total dues for the customer. Now problem starts
    here that if i have amountDue > 0 but amount_Paid is nothing that means
    customer never made any payment then this query will show me blank
    record. for some reason. but if i put any payment in payment table then
    i see the result. can any one help why is that. If there is no record
    in query totalPayemnts then it should be equal to 0 which is not
    happening

    is there any way if amount_paid has no value, when we run the query,
    then we could put it equal to 0
    Last edited by khanji; 11-28-05 at 13:03.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Zero is different from null. Null combined with anything = Null.

    Try using the nz() function to replace null values with a numeric value:

    TotalDues: (nz([SumOfamountDue], 0)-nz([SumOfAmount_Paid], 0))


    Side Note: Please be more discriptive with your thread titles. The fact that you are posting a thread implies you need help. Therefore a thread title stating "HLP PLZ" is redundant. A lot of people ignore those kind of titles...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2005
    Posts
    18
    great help thanks man, i have solved my problem, god bless you. can you answer me one more question. How to take automatic backup of my database at certain time. I want to save only my data in the table on a backup drive or some other drive can you help plz

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There is quite a variety of ways to do this. One of my personal favorites is another db with an autoexec macro (well, I use a little vb6 executable, same difference) that opens your target db and dumps all of it's tables to an xml or excel file. I then use windows scheduling to launch my "backup" application. How do you envision your desired process working, in general?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2005
    Posts
    18
    man any thing easy, which is reliable. I want back up on zip drive and thats it. I dont care which way. In any way.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In that case, I've already given you a fairly easy and reliable method...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Teddy
    .....One of my personal favorites
    .... that opens your target db and dumps all of it's tables to an xml or excel file.
    would it not just be easier to take a copy to the file(s), using a batch file rather than a VB process / macro?

    agree with using the windows scheduler to do the dirty deed though.....

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    He specifically mentioned wanting data only. I used this method in a scenario where a seperate process would look at the file I was dumping too and cull differential backups to a data warehouse facility. There aren't many reasons to pull ONLY data from access when backing it up, but they exist.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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