Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Wrong info on reports (2000)

    Hi,

    I have a report that I need to have open to only show records that have a zero balance. The Query behind the report has a control called [balance] which is made up of a formula using various other controls in the query. The query works fine, and the data on the report is fine. The report is opened from another form where the user selects the account from a list and enters the date range for the report. Again, this works fine. What I am attempting to do is to open the report to only show records that have a balance greater than zero. The code I am presently using to open the report is as follows:

    Dim strWhere As String

    strWhere = "[account Name] = '" & Me.List2.Column(1) & "'" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)"

    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , strWhere

    I've tried a variety of different things to get it to open with a zero balance all that happens is that report opens showing all accounts vs being limited to the one from the list. To make matters worse, it does not show all the records with a balance > 0.

    The SQL for the query is as follows:

    SELECT tblAccountDemographics.[Account Name], tblTimeSheet.Date, tblTimeSheet.[Activity Time], tblTimeSheet.[Total Billed], tblTimeSheet.Discount, tblPaymentAllocations.AmountAllocated, tblClientDemographics.[Client Name], nz(DSum("AmountAllocated","tblPaymentAllocations", "PayId=" & tbltimesheet.autonumber),0) AS AmtAlloc, [total billed]-[AmtAlloc] AS Balance, tblPaymentAllocations.PayID, tblPayments.[Date Paid]
    FROM (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.AccountID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) INNER JOIN (tblPayments INNER JOIN tblPaymentAllocations ON tblPayments.PaymentID = tblPaymentAllocations.PaymentLink) ON tblTimeSheet.AutoNumber = tblPaymentAllocations.PayID;

    I've tried putting in =>0 in the query under the [balance] field, but I end up with a parameter question for [amtalloc].

    I'm so stuck!

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wrong info on reports (2000)

    I am confused about whether you want the zero balances or the blances >=0. You seem to say each of these twice!

    Sometimes Access doesn't like adding criteria against calculated fields, (I am not sure just when this happens) but you can get around this by creating a second query based on the first and putting the criteria in the second one.

    The second line of your where condition is
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)"

    It is not a good idea to use Date as a field name, because it is the name of an Access function. It would be better to use ActivityDate.

    Have you tried adding the condition as a third part to the where clause

    " And ([Balance] >=0)"

    Or does this just give the same error as putting it in the query?
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong info on reports (2000)

    Hi,

    I am trying to get the report to open to only show records with a balance > 0. When I tried the code like this I got a type mismatch error.

    Dim strWhere As String

    strWhere = "[account Name] = '" & Me.List2.Column(1) & "'" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)"

    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , "([Balance] > 0)" And strWhere

    I'm sure I wrote it wrong but wanted to give it an attempt before posting back.

    Thanks,
    Leesha

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wrong info on reports (2000)

    Try

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)" & _
    " And ([Balance]>0)"

    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , strWhere
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong info on reports (2000)

    Hi!

    Well we're getting there! That got the report to open to records with balance >0. There is only one problem. Now the report opens to all accounts with records with a balance >0, not just the account selected from the list.

    Leesha

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wrong info on reports (2000)

    I am puzzled about why you use :

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" &

    Do you realise that Column(1) is actually the second column. The first column is column(0).

    It is not uncommon to have a hidden key in column(0) and a display name in column(1). Are you doing this?

    Also try putting in

    msgbox(strwhere)

    before the docmd.openreport line. This lets you check that strwhere gets assembled exactly the way you expect.
    Regards
    John



  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong info on reports (2000)

    The code that is presently being used was derived from this forum last summer. Han's helped me a great deal and kept me from having to reinvent the wheel. The database is one that was built by someone else and I've been muddling through adding to it as they need.

    Leesha

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wrong info on reports (2000)

    Before you added the third condition to strWhere did the report open with the correct data?
    i.e. with this does it show just the right account and date range?

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)"

    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , strWhere

    Is this is ok?

    Are you sure that when you added the third condition you didn't make any typing mistakes?

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)" & _
    " And ([Balance]>0)"

    For instance if the third line read:
    strWhere =" And ([Balance]>0)"

    The first two lines would be ignored.

    Did you put in msgbox(StrWhere) like I suggested? What happened?
    Regards
    John



  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong info on reports (2000)

    Hi,

    Much as it pained me to I went back to the drawing board. I wasn't exactly sure what to do with the msgbox statement you told me to do. What I ended up doing was identifying the list in the query using [forms]![frmAccountInvoices]![list] column of the query. That took care of the issue. The code to open the report no longer includes the the statement re AccountID or dates. So far so good.

    Thanks for your time and help!

    Leesha

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Wrong info on reports (2000)

    I am glad you seem to have it sorted out.

    My suggestion regarding a message was that you change

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)" & _
    " And ([Balance]>0)"

    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , strWhere


    to

    strWhere = "([account Name] = '" & Me.List2.Column(1) & "')" & _
    " And ([Date] Between #" & Me.STARTDATE & "# And #" & Me.ENDDATE & "#)" & _
    " And ([Balance]>0)"
    msgbox(strWhere)
    DoCmd.OpenReport "rptAccountpaymenthx", acViewPreview, , strWhere

    This can be used in lots of situations. It gets Access to display the contents of a variable to you before it tries to do anything with it. It is useful for finding typos, or missing quote marks etc. Another way to achieve a similar result is to write debug.print strwhere. Then you will find the contents in the immediate window.
    Regards
    John



  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong info on reports (2000)

    Thanks for the info and the explanation! I will save it with the rest of my tidbits for future use!

    Have a great day/night,
    Leesha

Posting Permissions

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