Access 2010 - Assign a variable to a query in a report / sub report

Posted on 2016-09-20
Last Modified: 2016-09-22
Wondering if someone can help or point me in the direction of the relevant help.

I have a union query which finds data from a table using the date as a criteria. The date is created using a variable 'MonthAhead'. The criteria in SQL :-

WHERE (((Year([EID1])*12+DatePart("m",[EID1]))=Year(Date())*12+DatePart("m",Date())+[MonthAhead]))

For example if I want to see all the data for the month in 2 months time I enter 2 when prompted for a value when running the query. I have created a report to use this query called 'MonthlyReport'.

I want to create a report to list the data for firstly one month ahead ('Month Ahead' = 1) then for the second month ahead ('MonthAhead' = 2) etc etc

I am trying to do this this by having a main report which uses a series of sub reports of 'MonthlyReport'. So in the main report the first sub-report of 'MonthlyReport' would use the variable  'Month Ahead' = 1, the second sub-report of 'MonthlyReport' would use the variable  'Month Ahead' = 2, and so on.

I can do this but cannot find away where the variable is automatically assigned. So as it stands when I run the report I get a dialogue box asking for the value of 'Month Ahead' when it compiles each sub report. It works but I would just like it to assign the 'Month Ahead' value without the manual input. Do I need to embed a value for 'Month Ahead' in the report or subreport somewhere?
Question by:Nige JK
LVL 31

Expert Comment

ID: 41807208
Wherever the variable is set, once it is set you could save it to a custom database property, and retrieve it from there in the subreports' record source queries.  This way, the calculation only needs to be done once, when the variable is set.  Here is some code for working with custom database properties:

Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties

Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 11-Oct-2014
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set prps = CurrentDb.Properties
   prps(strName) = varValue

   Exit Sub

    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = CurrentDb.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      CurrentDb.Properties.Append prp
      Resume Next
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 11-Oct-2014
'Called from various procedures

On Error GoTo ErrorHandler
   'Attempt to get the value of the specified property
   GetProperty = CurrentDb.Properties(strName).Value

   Exit Function

   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

   If IsDate(Me![txtStartDate].Value) = False Then
      strTitle = "Invalid date"
      strPrompt = "Please enter a valid start date"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
      GoTo ErrorHandlerExit
      dteStart = CDate(Me![txtStartDate].Value)
   End If
   strPropertyName = "PropName"
   lngDataType = dbDate
   Call SetProperty(strPropertyName, lngDataType, dteStart)

   GetStartDate = CDate(GetProperty("PropName", ""))

   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

   strPropertyName = "PropName"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)

   lngID = CLng(GetProperty("PropName", ""))

   strPropertyName = "PropName"
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, intMonth)

   intID = CInt(GetProperty("PropName", ""))

   strPropertyName = "PropName"
   lngDataType = dbBoolean
   Call SetProperty(strPropertyName, lngDataType, "True")

   blnValue = CBln(GetProperty("PropName", ""))

Saving to a custom property from a control's AfterUpdate event
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   Exit Sub

   MsgBox "Error No: " & Err.Number & "; Description: " & _
   Resume ErrorHandlerExit

End Sub

Open in new window

LVL 34

Accepted Solution

PatHartman earned 500 total points
ID: 41807356
Where Format([EID1], "yyyy/mm" Between Format(Date(), "yyyy/mm") and Format(DateAdd("m", [MonthAhead]), "yyyy/mm")

Then include a new column in your query:
Select ..., Format([EID1], "yyyy/mm") As MonthGroup

Then in your report add a new section and group by MonthGroup.  and set the page to break on that section.

NOTE: since we are formatting a date field here go get just year and month, it is important to have year first and month second so that the sorting and grouping will work correctly across year boundaries.

Author Comment

by:Nige JK
ID: 41808409
Many thanks for the comments. I have used the comments by Pat to achieve (more) than I wanted by taking this different approach. I have altered it so that a start date and finish date are inputted manually at the beginning  and the report then splits the months up in the date range I specified using the grouping for the MonthGroup in the report.

Thanks again

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL Server Stoplist 2 17
SQL server is using more virtual memory. 5 67
Format a Field AFTER UPDATE 5 27
recover sqlserver db 8 54
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

912 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