Solved

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

Posted on 2016-09-20
3
49 Views
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?
0
Comment
Question by:Nige JK
3 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
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

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    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
   Else
   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

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      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

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

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

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

   strDocsPath = GetProperty("PropName", "")

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

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

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

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

Boolean
=======
   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
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

Open in new window

0
 
LVL 34

Accepted Solution

by:
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.
0
 

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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

759 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

19 Experts available now in Live!

Get 1:1 Help Now