?
Solved

Access 2007 query export to excel template

Posted on 2011-04-20
5
Medium Priority
?
610 Views
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.
0
Comment
Question by:pgmtkl
  • 3
5 Comments
 
LVL 11

Expert Comment

by:thydzik
ID: 35438405
Please upload (a cutdown version) of the database and workbook.

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

Author Comment

by:pgmtkl
ID: 35447753
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];      
qrycal.xlsx
Project-Calendar-template.xlsx
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 35501535
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)
 2
? WeekDayName(WeekDay(Date(),vbSaturday),True,vbSaturday)
Sun
? DatePart("ww",Date(),vbSaturday,vbFirstFullWeek)
 18

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

Expert Comment

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

Expert Comment

by:GRayL
ID: 35709754
Thanks, glad to help.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

839 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