• Date format in Union Query (2003 all SPs)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Date format in Union Query (2003 all SPs)

    Author
    Topic
    #417759

    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

    Viewing 0 reply threads
    Author
    Replies
    • #938215

      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).

      • #938411

        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

        • #938418

          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 for instructions.

    Viewing 0 reply threads
    Reply To: Date format in Union Query (2003 all SPs)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: