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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CASE Statement using different fields 8 35
SQL Server Error: 4060 8 33
SQL query 45 41
SQL multiple joins 2 19
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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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