Tools menu and date question

R

rutica

Registered User.
Local time
Today, 15:06
Joined
Jan 24, 2008
Messages
87
2 questions if I may:

1). In my Access 2003 database, under Tools, Start Up, Allow Full Menus is unchecked.

I have a form that opens a query. When that query opens, I would like to activate the Tools, Office Links menu. Then when the query closes, I would like the Tools menu goes away again. Can I do that?

2). Also, in another query, I have a date field called As-Of-Date which is always the last day of the month: ex: 12/31/08, 1/31/09, 2/28/09, etc.

Part of the criteria in my SQL is:

"...WHERE ((([table1].[As-of-Date])>Date()-28)..."

What I'm trying to do is always get the previous month's data. So in March, I want to see Feb's data. This works if we're in the beginning of the month, ex: March 1 will show me Feb's data. Great. But it doesn't work if we're towards the end of the month, ex: March 30 will not show me Feb's data. Not good.

How can I add the criteria to my query that says show me data for the date range of the previous month?

Thanks,
 
rutica said:
2 questions if I may:

1). In my Access 2003 database, under Tools, Start Up, Allow Full Menus is unchecked.

I have a form that opens a query. When that query opens, I would like to activate the Tools, Office Links menu. Then when the query closes, I would like the Tools menu goes away again. Can I do that?
Click to expand...
You would need to open the query IN a form and then you can create your own menu and have it as the form's menu. See here how to create your own menu (similar to the shortcut menu in this example):
http://www.btabdevelopment.com/main/LinkClick.aspx?link=62&tabid=55&mid=385
2). Also, in another query, I have a date field called As-Of-Date which is always the last day of the month: ex: 12/31/08, 1/31/09, 2/28/09, etc.

Part of the criteria in my SQL is:

"...WHERE ((([table1].[As-of-Date])>Date()-28)..."

What I'm trying to do is always get the previous month's data. So in March, I want to see Feb's data. This works if we're in the beginning of the month, ex: March 1 will show me Feb's data. Great. But it doesn't work if we're towards the end of the month, ex: March 30 will not show me Feb's data. Not good.

How can I add the criteria to my query that says show me data for the date range of the previous month?
Click to expand...
"...WHERE ((([table1].[As-of-Date])>DateSerial(Year(Date()), Month(Date()),0)..."
 
Thanks for writing. I followed the link's instructions and was able to create the menu toolbar. But instead of creating this custom toolbar, there's not just a way to make the Access toolbar I want appear when opening up the query from the form?

For DateSerial(Year(Date()), Month(Date()),0)... doesn't that just give me yesterday's date? I tried it today on 4/1/09 and it gave me 3/31/09. Tomorrow on 4/2/09, won't it just give me 4/1/09?

Thanks,
 
rutica said:
Thanks for writing. I followed the link's instructions and was able to create the menu toolbar. But instead of creating this custom toolbar, there's not just a way to make the Access toolbar I want appear when opening up the query from the form?

For DateSerial(Year(Date()), Month(Date()),0)... doesn't that just give me yesterday's date? I tried it today on 4/1/09 and it gave me 3/31/09. Tomorrow on 4/2/09, won't it just give me 4/1/09?

Thanks,
Click to expand...
The DateSerial function you quoted gives the last day of the previous month (regardless of which day you are on).

Tomorrow it would give you 3/31/09, and 3/31/09 the next day, and 3/31/09 the next day, etc.

You can't set toolbars to be visible for queries. You CAN for forms though, so if you create a form, based on the query and open the FORM instead of the query (you can make it look like it is the query that is opened by setting the form's property to be Datasheet) then you can have the toolbar/menus.
 
Great. Thanks. This is very helpful.

thanks again!!
 
Great. Thanks. This is very helpful.

thanks again!!
 
Instead of making a Toolbar, can I write code to automatically show my query data in a temporary Excel file? The code would be behind a button on my form.

The 'temporary' part is what i'm struggling with.

When I try:
DoCmd.OutputTo acOutputQuery, "qryTest", acFormatXLS, XL, True

it prompts me to name the file in Excel. But I don't want to save the data. Can I have the query data open in a unsaved Excel file (ex: Book1.xls).

Then if the user leaves Book1.xls open, and clicks the button on the form again, they get the data in Book 2.xls. If the user wants to save, that is up to them.

Thanks,
 
wow, impressive! i tried the code, but i have a query, not a form.

therefore, i tried to change: "frm As Form" into "qry as QueryDef". But then it didn't accept: "Set rst = qry.RecordsetClone"

Can this code be used for a query?

thanks!
 
rutica said:
wow, impressive! i tried the code, but i have a query, not a form.

therefore, i tried to change: "frm As Form" into "qry as QueryDef". But then it didn't accept: "Set rst = qry.RecordsetClone"

Can this code be used for a query?

thanks!
Click to expand...

For a query use

Set rst = CurrentDb.OpenRecordset("YourQueryNameHere")
 
I am getting 'Type Mismatch'.

This is what my code looks like:

Private Sub Command28_Click()
Send2Excel ("qry KPI Detail Report- Escalation Summary")
End Sub

'---------------------------------------------------------------------------------------
' Procedure : Send2Excel
' Author : Bob Larson
' Date : 5/25/2008
' Purpose : Send any single recordset form to Excel. This will not work with
' subforms.
' Use : You may freely use this code as long as the author information in
' this header remains intact
'---------------------------------------------------------------------------------------
'

Public Function Send2Excel(qry As QueryDef, Optional strSheetName As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to


Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler
Set rst = CurrentDb.OpenRecordset("qry KPI Detail Report- Escalation Summary")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If
xlWSh.Range("A1").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Loop
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function
 
Change this part:

Public Function Send2Excel(qry As QueryDef, Optional strSheetName As String)

to this:

Public Function Send2Excel(qry As String, Optional strSheetName As String)


and then this part:

Set rst = CurrentDb.OpenRecordset("qry KPI Detail Report- Escalation Summary")

To this

Set rst = CurrentDb.OpenRecordset(qry)

And then call it like you currently are.
 
Now I'm getting a message: "Loop without Do". It highlights:


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Loop
 
rutica said:
Now I'm getting a message: "Loop without Do". It highlights:


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Loop
Click to expand...

Yeah, I think I have a typo from old code. It should be NEXT and not LOOP.
 
Now I'm getting: 'Too few parameters. Expected 2'

This is what I have:


Private Sub Command28_Click()
Send2Excel ("Qry KPI Detail Report- Escalation Summary")
End Sub

'---------------------------------------------------------------------------------------
' Procedure : Send2Excel
' Author : Bob Larson
' Date : 5/25/2008
' Purpose : Send any single recordset form to Excel. This will not work with
' subforms.
' Use : You may freely use this code as long as the author information in
' this header remains intact
'---------------------------------------------------------------------------------------
Public Function Send2Excel(qry As String, Optional strSheetName As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to


Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler
Set rst = CurrentDb.OpenRecordset(qry)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If
xlWSh.Range("A1").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function
 
Your query appears to have some input parameters. What is the SQL for the query?
 
You're right, the query is a crosstab query that takes 2 values from two combo boxes on my form.

But, you know, I can live with keeping:
DoCmd.OutputTo acOutputQuery, "qryTest", acFormatXLS, XL, True

If you are interested, here is my query SQL:
PARAMETERS [Forms]![frmEscalationReports]![cboEscalation] Long, [Forms]![frmEscalationReports]![cboDate] DateTime;
TRANSFORM Count(IIf([CodeGrouping] Is Null,tblEscalationReportsDetail![Criteria Code],[CodeGrouping])) AS Expr2
SELECT tblEscalationReports.ReportName, [Project Status Survey].ADDomain, [Project Status Survey].[Project ID], [Project Status Survey].ProjectName, [Project Status Survey Budget].SizeClass, [Project Status Survey].[As-Of Date]
FROM (((([KPI Rating Criteria] LEFT JOIN tblEscalationReportsDetail ON [KPI Rating Criteria].[Criteria Code] = tblEscalationReportsDetail.[Criteria Code]) LEFT JOIN tblEscalationReports ON tblEscalationReportsDetail.ReportIDLink = tblEscalationReports.ReportID) RIGHT JOIN [KPI Rating Rationale] ON [KPI Rating Criteria].[Criteria Code] = [KPI Rating Rationale].[Criteria Code]) INNER JOIN [Project Status Survey] ON ([KPI Rating Rationale].[As-of-Date] = [Project Status Survey].[As-Of Date]) AND ([KPI Rating Rationale].[Project ID] = [Project Status Survey].[Project ID])) INNER JOIN [Project Status Survey Budget] ON ([Project Status Survey].[As-Of Date] = [Project Status Survey Budget].[As-Of Date]) AND ([Project Status Survey].[Project ID] = [Project Status Survey Budget].[Project ID])
WHERE (((tblEscalationReportsDetail.ReportIDLink)=[Forms]![frmEscalationReports]![cboEscalation]) AND (([Project Status Survey].[As-Of Date])=[Forms]![frmEscalationReports]![cboDate]))
GROUP BY tblEscalationReports.ReportName, [Project Status Survey].ADDomain, [Project Status Survey].[Project ID], [Project Status Survey].ProjectName, [Project Status Survey Budget].SizeClass, [Project Status Survey].[As-Of Date]
ORDER BY [Project Status Survey].ADDomain, [Project Status Survey].[Project ID], [Project Status Survey].ProjectName, IIf([CodeGrouping] Is Null,tblEscalationReportsDetail![Criteria Code],[CodeGrouping])
PIVOT IIf([CodeGrouping] Is Null,tblEscalationReportsDetail![Criteria Code],[CodeGrouping]);


I posted a different question regarding this crosstab query with a sample of my database here:
http://www.access-programmers.co.uk/forums/showthread.php?t=169398
 
Bob,
If you're still there...

Is it possible to use your code to export to Excel if the query I want to send to Excel is a crosstab query that has 2 parameters? (The parameters are 2 values from two combo boxes on my form).
One parameter is called [Forms]![frmEscalationReports]![cboEscalation] and is a long integer.
The other parameter is called [Forms]![frmEscalationReports]![cboDate] and is a date.

If so, I am trying to export to Excel with 2 special formatting needs in Excel:
--Data, Filter, AutoFilter is turned on for my columns in Row 1.
--My column names in Row 1 are turned vertically. In other words, the Orientation is 90 degrees. (Format, Cells, Alignment tab, Orientation section).

Is it possible to do this?

Thanks!!!
 
I got it!

I replaced this:
Set rst = CurrentDb.OpenRecordset(qry)

with this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs(qry)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

And for my formatting needs, I used:
ApXL.selection.AutoFilter
ApXL.selection.Orientation = 90

Thanks!
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Interesting QUERY problem
Replies
11
Views
773
mloucel
mloucel
J
Date format mm/yyyy
Replies
17
Views
1,424
JPR
J
A
Search button for more than one field search
Replies
3
Views
222
Ken Sheridan
Ken Sheridan
D
Solved Date is displaying odd behavior
Replies
14
Views
847
Ken Sheridan
Ken Sheridan
N
Solved MS Access Switch Board Menu
Replies
2
Views
960
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom