Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2008
    Location
    Detroit, MI
    Posts
    4

    Unanswered: Place Weekday in Query Field and Export to Excel

    Using Access 2003 (Access 2000 file format) on Windows XP

    I have a query that pulls from 2 tables that includes a date as well as other data. What I need is code to put in the SQL view, if this is not possible in the regular design view, to extract a weekday from that date (I still do not know why my boss needs the date and weekday). Other than that I have everything I need in the query.

    I know how to add this to the main form of my database as a button that pulls up a date entry box from a user and even print, but I do not know how to "automate" a save as or export to an excel file on the desktop. Here is the current SQL view (with 7/1/2008 as the user inputted date) of the query:

    SELECT tblFoodDonations.Date, tblFoodDonations.FoodDonationsID, tblFoodDonor.[Donor Company], tblFoodDonations.Bakery, tblFoodDonations.Dairy, tblFoodDonations.Meat, tblFoodDonations.Fruit, tblFoodDonations.Vegetable, tblFoodDonations.Prepared, tblFoodDonations.Juice, tblFoodDonations.[Non-Perishable], tblFoodDonations.[Non-Food], tblFoodDonations.Driver, tblFoodDonations.TruckNumber, tblFoodDonations.[Donor Time]
    FROM tblFoodDonor INNER JOIN tblFoodDonations ON tblFoodDonor.DonorID = tblFoodDonations.DonorID
    WHERE (((tblFoodDonations.Date)=#7/1/2008#));
    Also I would like to add that I have been using access for months now, but am in no way an expert with the SQL view or VB coding. thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Dnt.Date
         , WEEKDAY(Dnt.Date) AS Date_weekday
         , Dnt.FoodDonationsID
         , Dnr.[Donor Company]
         , Dnt.Bakery
         , Dnt.Dairy
         , Dnt.Meat
         , Dnt.Fruit
         , Dnt.Vegetable
         , Dnt.Prepared
         , Dnt.Juice
         , Dnt.[Non-Perishable]
         , Dnt.[Non-Food]
         , Dnt.Dnriver
         , Dnt.TruckNumber
         , Dnt.[Donor Time]
      FROM tblFoodDonations AS Dnt   
    INNER 
      JOIN tblFoodDonor AS Dnr
        ON Dnr.DonorID = Dnt.DonorID
     WHERE Dnt.Date = #2008-07-01#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2008
    Location
    Detroit, MI
    Posts
    4
    Do I put this in the same query? Or replace what I have with this code?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    replace what you have

    after you save it and open it again, access will have butchered the formatting and clarity, but as long as it's producing what you want...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I hate how Access turns nicely structured SQL into (((a pile) of) puke).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    agreed, wholeheartedly

    but i can also see it from the point of view of whoever built the access user interface -- presumably they must have figured that the query design view, with the grid and the drag-and-drop tables and columns, would be the main way people would build or maintain queries

    my strategy is to keep all queries in source code in an external sql library
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Maybe it should be called AQL rather than SQL
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jul 2008
    Location
    Detroit, MI
    Posts
    4
    When I run this I get "Enter Parameter Value: Dnt. Driver" as a text box. What exactly is Dnt.xxx? The SQL that I posted worked, I just needed an extra column that took the date and generated the day of the week. (Not replacing the date, but adding the weekday)

    I also found some code I can copy and paste to automate to excel.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Cintury
    What exactly is Dnt.xxx?
    a column in the Dnt table

    Dnt is a table alias assigned in the SQL

    table aliases are used for convenience, to make the SQL shorter, more manageable, but above all, easier to read

    of course, to the numpties who decided mangling the SQL code every time you save it is an okay idea, having SQL that is easy to read is a totally obscure benefit, innit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^

    Yes, very obscure... and a fairly pointless exercise in Access tbh... since it's gonna end up as a pile of puke when you next look at it anyway.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which is why i always maintain my access queries in external sql text files

    another shortcoming (the list is endless) is that there are no comments allowed in access sql

    but i can have comments in the text file, as long as i keep the comments separate, and only copy/paste the actual sql into the sql view window

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, having comments in SQL would be nice in Access... and keeping them in text files isn't really going to help much since you'd have to have the comments completely away from the SQL itself, which defeats the purpose of having comments.

    I just use the builder as much as possible in Access.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whaddya mean, "completely away from"???

    i put the comments right into the same sql text file as the query

    Code:
    --------------------------------------------
    -- the following query does such-and-such --
    -- and is run once a day after job #24    --
    --------------------------------------------
    
    SELECT ...
      FROM ...
    LEFT OUTER
      JOIN ...
        ON ...
     WHERE ...
    GROUP
        BY ...
    
    -- notice that it's a left outer join.
    -- this is important because there are occasional
    -- XX rows without matching YY rows, and this
    -- is the only query which will show them
    this still allows me very easy copy/paste into the sql view window, and it allows me to fully document the query right there with the sql

    as a matter of fact, my text editor (ultraedit) has column mode, so i sometimes do put comments on the same line(s) as the sql, just off to the right where they won't be picked up by my Ctrl-C (using column mode)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I meant comments within the SQL... just as you have alluded to with your last paragraph.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Jul 2008
    Location
    Detroit, MI
    Posts
    4
    Ok I got it to work. (Dnt.Driver was mispelled) Now this brings me to 2 more problems:

    1) I'm receiving a weird time when I convert to excel: 1/0/1900 10:44:06 AM
    is an example. Although in access I don't see these extra numbers "1/0/1900" and it appears in every donor time.

    2) I see that there is a numerical value for the weekday. Would it be better to have the code to change this in access or excel? At the moment I was exporting this to a blank excel spreadsheet and this seems like it may require it to be exported to an existing excel template.

Posting Permissions

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