Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-09-20
3
Medium Priority
?
97 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
[X]
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
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 39

Accepted Solution

by:
PatHartman earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

670 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