Access 2007 query export to excel template

Posted on 2011-04-20
Last Modified: 2013-11-27
Hi. I have an access query that is broken down by week 1 through 4 for a specific period. I have done access exports to multiple worksheets but what i would like to do with this is export the query to certain cells on the same worksheet depending on the date and week (1-4). The period doesnt follow the regular month. Is there a way to take one query with vba and export it based on a parameter? Or is the easiest way to create 4 queries and export to each cell? Any help is appreciated.
Question by:pgmtkl
    LVL 11

    Expert Comment

    Please upload (a cutdown version) of the database and workbook.

    The reference the tables/sheets and explain what you want to acheieve.

    Author Comment

    Attached is a copy of the query and template that i would like report to look like. 2 tables are being used.

    SELECT FY12.ID, FY12.Date, FY12.Period, FY12.Week, FY12.Day, tbl_milestones.PJectID, tbl_milestones.PlannedDate, tbl_milestones.Status, IIf([Tbl_FY12.Day]="Sat","1",IIf([Tbl_FY12.Day]="Sun","2",IIf([Tbl_FY12.Day]="Mon","3",IIf([Tbl_FY12.Day]="Tue","4",IIf([Tbl_FY12.Day]="Wed","5",IIf([Tbl_FY12.Day]="Thu","6",IIf([Tbl_FY12.Day]="Fri","7","99"))))))) AS WkdaysortFROM tbl_milestones RIGHT JOIN FY12 ON tbl_milestones.[PlannedDate] = FY12.[Date];      
    LVL 44

    Accepted Solution

    You might start by describing table FY12, particularly why the Week and Day fields.  BTW Date, Day, Week, Month, Year, etc. are Access reserved words and should not be used as a field name.   I suggest FYDate as a name.

    Here are a couple of built-in functions you may be interested in:

    Using today's date

    ? WeekDay(Date(),vbSaturday)
    ? WeekDayName(WeekDay(Date(),vbSaturday),True,vbSaturday)
    ? DatePart("ww",Date(),vbSaturday,vbFirstFullWeek)

    Press Alt+F11 to get to the VB Editor Window and type these function names in the Help window and select the Function return
    LVL 44

    Expert Comment

    BTW you can test these functions as I did above by using the Immediate Pane - press Ctl+G
    LVL 44

    Expert Comment

    Thanks, glad to help.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now