Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format in Union Query (2003 all SPs)

    I have a union query (below)that is giving me some odd results and I think it may be that the date on transactions is not being recognized properly (eg sorting by date is unpredicatable, seems to be treating it as text)

    SELECT "EncounterSalesExported" AS Operation,MYOBSellAcct AS Acct,Day,EncounterSales as Total,AccountName from EncounterSalesbyAcctNo
    UNION Select
    "MYOBImportedSales" AS Operation,AccountNumber AS Acct,[Date by Day] As Day,TotalMYOBSale AS Total,AccountName from MYOBSalesbyAcctNo
    UNION select
    "MYOBReceipts" As Operation,AccountNumber As Acct, Date as Day,TotalReceived as Total, AccountName from MYOBReceiptsbyAcctSummary;

    Is there a way I can format the date field (Day in this query) as say short date to ensure that is not the problem
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Date format in Union Query (2003 all SPs)

    Are Day, Date by Day and Date all date/time fields?
    You can try enclosing Day and Date in square brackets, since they are also the names of built-in functions, i.e. [Day] and [Date] (I always avoid using field names that coincide with built-in names).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Union Query (2003 all SPs)

    Yes - all date fields
    Adding brackets didnt help
    Note the final result looks OK eg 1/10/04 but it is being treated as text when sorted so that netx item in sort is 1/11/04 not 2/10/04
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Date format in Union Query (2003 all SPs)

    Sorry, I can't explain this - date fields should be treated as such in a union query, not as text. If you like, you can post a stripped down copy of your database, so that Loungers can inverstigate the problem directly. See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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