Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wierd characters in union query results (2000)

    I am getting two little squares returned in the cells of a union query where I would expect to see currency amounts. The two little squares look like the characters that are sometimes displayed when Access comes accross ASCII codes that it doesn't know how to print.

    The union query combines two queries, one showing invoice details and one showing payment allocation details, and each works perfectly when run on its own. The idea is to create something like a bank statement, showing money in and money out on the same report.

    The currency amounts that are showing as little squares come from the payment allocations query and are displayed correctly in that query. The invoice query also populates currency cells and these display as expected in both the original query and in the union query. All the other fields from both queries are also output as expected in the union query. I am mystified.

    Thanks in advance for any help.

    The union query looks like this:

    SELECT CustID, TxDate, AppointmentID, ID, Type, OrderAmt, PaymentRem, AllocationAmt, Status
    FROM qry_Statement_Orders
    UNION SELECT CustID, TxDate, AppointmentID, ID, Type, OrderAmt, PaymentRem, AllocationAmt, Status
    FROM qry_Statement_Allocations
    ORDER BY CustID, TxDate;

    and the two source queries look like this:

    SELECT tbl_SalesOrderHeader.CustID, tbl_PaymentAllocations.AllocationDate AS TxDate, tbl_SalesOrderHeader.AppointmentDate, tbl_PaymentAllocations.AllocationID AS ID, "Alloc" AS Type, Null AS OrderAmt, Null AS PaymentRem, tbl_PaymentAllocations.Amount AS AllocationAmt, Null AS Status
    FROM tbl_PaymentAllocations LEFT JOIN tbl_SalesOrderHeader ON tbl_PaymentAllocations.OrderID = tbl_SalesOrderHeader.OrderID;

    SELECT tbl_SalesOrderHeader.CustID, tbl_SalesOrderHeader.OrderDate AS TxDate, tbl_SalesOrderHeader.AppointmentDate, tbl_SalesOrderHeader.OrderID AS ID, "Order" AS Type, tbl_SalesOrderHeader.OrderTotal AS OrderAmt, Null AS PaymentRem, Null AS AllocationAmt, tbl_SalesOrderHeader.Status
    FROM tbl_SalesOrderHeader;

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wierd characters in union query results (2000)

    Check and make sure you don't have any fields with carriage returns in them. That can turn up as two little squares in status bar text and places like union queries.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wierd characters in union query results (2000)

    Yes, that's where I've seen these characters before. But the columns in question are of type currency (which I assume means that they cannot contain CRs) and the data in them is displaying correctly in the query that forms one half of the union query. It's only in the union query that I get the problem.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wierd characters in union query results (2000)

    Try using zero instead of Null for the empty currency values in your SQL and see if that makes a difference
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wierd characters in union query results (2000)

    Yes, that worked. Thanks.

Posting Permissions

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