?
Solved

Using the same variable in several queries

Posted on 2014-10-02
12
Medium Priority
?
128 Views
Last Modified: 2014-10-03
I have a report (rpt_EventsLog) that has 2 sub reports (subrpt_on duty today_jr) and (subrpt_on duty today_sr).

I want to enter the date one time, and use it for the queries on each of those reports, but right now it asks the question ([Date?]) three times?
0
Comment
Question by:TIgerV
[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
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40357160
Add the criteria fields to a form and run the report from that form.  Change the query to reference the form fields rather than to prompt.

Where SomeField = Forms!MyForm!SomeField
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40357176
and define Forms!MyForm!SomeField as a Parameter, otherwise you may have problems down the line when queries call on that query (especially Crossqueries).
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40357393
Another option, which I use frequently is to Create a TempVar for your new variable.  So in the AfterUpdate event of your form control, you would include:
Private Sub txt_DateControlName_AfterUpdate

    Tempvars!ReportDate = me.txt_DateControlName.Value 

End Sub

Open in new window

Then, in your queries, you could use:

SELECT * FROM yourTable where [DateField] = [Tempvars]![ReportDate]

and would not need to declare that in your query.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 40357630
"but right now it asks the question ([Date?]) three times?"

You need to have the *exact* same Prompt in each of the three (?) RecordSources involved ... the main report, and both sub reports, in which case it should ... only ask once.

mx
0
 

Author Comment

by:TIgerV
ID: 40358151
It is the same (jr. and sr are the same query, just copied and one option changed)  Sub reports don't pass the answer
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40358195
So have you tried the form or tempvar solution yet?
0
 

Author Comment

by:TIgerV
ID: 40358206
I have not, I will be back at it first thing in the morning.   After reviewing it, I'm sure the Tempvar solution will be the working one.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40358218
They BOTH work.  The form field reference doesn't require any code but the tempvar solution does.  That is the difference.
0
 
LVL 75
ID: 40358228
Well ... and the solution I posted doesn't require anything additional beyond what the OP currently has. Just change the prompts to match each other :-)
I posted that for an immediate quick fix.

The ultimate solution is of course either of the other two :-)

mx
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40358242
Joe,
Access prompts multiple times for arguments for reports.  So, even if the query of the subreport uses the same argument as the query of the main report, two prompts happen because Access runs two queries.  The only time Access only prompts once is if the queries are nested or the argument is used multiple times in the same query.

In most cases, the subreport should be relying in the master/child links rather than arguments though to avoid this issue.   That's what I do and probably what you do.  But in any event, if you open a report and enter a value at the prompt and then decide to print the report, you get prompted again.

All in all , it is much less annoying to use forms to provide values.  That also allows you to validate them before using them.
0
 

Author Closing Comment

by:TIgerV
ID: 40359981
Thank you!
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40360032
You could also save the Date value to a custom database property, and then retrieve it in a query or elsewhere using the GetProperty function.  Here is some for creating and retrieving custom db properties:

[sample code fragment using the procedure]

   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   
   strRecordSource = "tblInventoryItemsComponents"
   strQuery = "qryTemp"
   Set dbs = CurrentDb

   'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      'Use this line if you need a recordset
      Set rst = CurrentDb.OpenRecordset(strQuery)
   End If

   'Use SQL string as the record source of a form
   strFormName = "fpriLoadSoldPackingSlip"
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acDesign
   Set frm = Forms(strFormName)
   frm.RecordSource = strSQL
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acNormal
   
   'Use SQL string as the record source of a report
   strReport = "rptLoadSold"
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewDesign, _
      windowmode:=acHidden
   Set rpt = Reports(strReport)
   rpt.RecordSource = strSQL
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewNormal, _
      windowmode:=acWindowNormal

   'The report has the filtered query as its record source

'Create a sorted rather than filtered query
Private Sub fraChoice_AfterUpdate()

   Dim intChoice As Integer
   Dim strSort As String
   Dim strRecordSource As String
   Dim strSQL As String
   Dim strQuery As String
   Dim lngcount As Long
   
   intChoice = Nz(Me![fraChoice], 1)
   strSort = Switch(intChoice = 1, "CategoryName", _
      intChoice = 2, "Location", _
      intChoice = 3, "Room")
   
   'Create SQL and save sorted query
   strRecordSource = "tblCompanyInfo"
   strQuery = "qrySortedInfo"
   strSQL = "SELECT * FROM " & strRecordSource _
      & " ORDER BY [" & strSort & "];"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngcount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngcount
   
End Sub

=========================

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 11-May-2013

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

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