Place Weekday in Query Field and Export to Excel  | Member | | Join Date: May 2007
Posts: 81
| |
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
| | Member | | Join Date: Mar 2008
Posts: 69
| | | re: Place Weekday in Query Field and Export to Excel
Use the "w" parameter in the DatePart function. This will give you a number between 1 & 7 which you can decode to a day name if necessary. Your code should include something like "Datepart('w', DateFieldName) as DayNo". DayNo will then appear in the output and you can export to Excel.
I find the quickest way to export is to use the SQL "SELECT ..... INTO" to make a temporary table and then use the DAO TransferSpreadsheet method. You can get details fporm the VBA Help File.
| | Member | | Join Date: May 2007
Posts: 81
| | | re: Place Weekday in Query Field and Export to Excel
Quote:
Originally Posted by youmike Use the "w" parameter in the DatePart function. This will give you a number between 1 & 7 which you can decode to a day name if necessary. Your code should include something like "Datepart('w', DateFieldName) as DayNo". DayNo will then appear in the output and you can export to Excel.
I find the quickest way to export is to use the SQL "SELECT ..... INTO" to make a temporary table and then use the DAO TransferSpreadsheet method. You can get details fporm the VBA Help File. Ok I found what I need to export to excel, and I also changed the query to include a column for the weekday, but as a numerical value. How do I change it to the respective weekday name? Here is my query with a user input date: - 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.Driver AS Expr1, Dnt.TruckNumber, Dnt.[Donor Time]
-
FROM tblFoodDonor AS Dnr INNER JOIN tblFoodDonations AS Dnt ON Dnr.DonorID = Dnt.DonorID
-
WHERE (((Dnt.Date)=#7/1/2008#));
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Place Weekday in Query Field and Export to Excel
Hi, Cintury.
You may use Format() function to get weekday name. -
Format(dteInputDate, "dddd")
-
Regards,
Fish
| | Member | | Join Date: May 2007
Posts: 81
| | | re: Place Weekday in Query Field and Export to Excel
Quote:
Originally Posted by FishVal Hi, Cintury.
You may use Format() function to get weekday name.
-
Format(dteInputDate, "dddd")
-
Regards,
Fish Is this something I can readily put in my code or design view under a column?
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Place Weekday in Query Field and Export to Excel
Quote:
Originally Posted by Cintury Is this something I can readily put in my code or design view under a column? You may use it instead of WeekDay() function in query as well as all other VBA functions built-in, library or written in code module.
Regards,
Fish
| | Member | | Join Date: Mar 2008
Posts: 69
| | | re: Place Weekday in Query Field and Export to Excel
Fish's advice is spot on.
As a general observation, it's often useful to have small tables with Day & Month linked to numbers and then use them in queries. This is particularly so if you happen to deal with non-mainstream languages. In my own case, Xhosa is such a language which I've has to incorporate into applications in the past..
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,358 network members.
|
