Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access Query And Forms

Posted on 2012-08-27
8
Medium Priority
?
605 Views
Last Modified: 2012-09-04
Hello Experts,
     I have two queries that i would like to use a form to enter the dates.  The first one gives me counts and the second one gives me percentages  I need to base the date range on referral.referral dt.  The form is called totals.  Here are the two queries:

SELECT Count(Referral.StudyNo) AS CountOfStudyNo, Sum(IIf([Enrolled],1,0)) AS [Count of Enrolled], Sum(IIf(IsNull([Referral Dt]),0,1)) AS [Count Of Referred], Sum(IIf([Enrolled] And Not IsNull([HomeDate]),1,0)) AS [Count Of Home Visited], Sum(IIf([Declined],1,0)) AS [Count of Declined], Sum(IIf(Not [Eligible],1,0)) AS [Count of Inelegible], Sum(IIf([Skilled_Nursing_Facility],1,0)) AS [count of Transferred], Sum(IIf([Rehospitalized],1,0)) AS [count of Readmitted], Sum(IIf([Enrolled] And Not IsNull([HomeDate]),IIf(DateDiff("h",[HomeDate],[Hosp_Discharge_Date])<=72,1,0),0)) AS [Count of Home Visited 72], Sum(IIf([tblphonepillars (1).ph2date],1,0)) AS [count of Phone call 1], Sum(IIf([tblphonepillars (2).ph7date],1,0)) AS [count of Phone call 2], Sum(IIf([tblphonepillars (3).ph14date],1,0)) AS [count of Phone call 3]
FROM (((Referral INNER JOIN [tblPhonePillars (1)] ON Referral.StudyNo = [tblPhonePillars (1)].StudyNo) INNER JOIN tblHomeVisits ON Referral.StudyNo = tblHomeVisits.StudyNo) INNER JOIN [tblPhonePillars (2)] ON (Referral.StudyNo = [tblPhonePillars (2)].StudyNo) AND (tblHomeVisits.StudyNo = [tblPhonePillars (2)].StudyNo)) INNER JOIN [tblPhonePillars (3)] ON tblHomeVisits.StudyNo = [tblPhonePillars (3)].StudyNo;


And:

SELECT [Totals_Percentages]![Count Of Home Visited]/[Totals_Percentages]![Count of Enrolled] AS PercHomeVisited, [Totals_Percentages]![Count Of phone call 1]/[Totals_Percentages]![Count of Enrolled] AS Perccall1, [Totals_Percentages]![Count Of phone call 2]/[Totals_Percentages]![Count of Enrolled] AS Perccall2, [Totals_Percentages]![Count Of phone call 3]/[Totals_Percentages]![Count of Enrolled] AS Perccall3, [Totals_Percentages]![Count Of enrolled]/[Totals_Percentages]![Count of referred] AS Percreferred, [Totals_Percentages]![Count Of phone call 3]/[Totals_Percentages]![Count of referred] AS Perccompleted, [Totals_Percentages]![Count Of readmitted]/[Totals_Percentages]![Count of enrolled] AS Percenrolledreadmitted, [Totals_Percentages]![Count Of declined]/[Totals_Percentages]![Count of referred] AS Percreferredineligible
FROM Totals_Percentages;
0
Comment
Question by:aplona
  • 4
  • 3
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38336609
Use a form to enter what dates?
Do you mean you want use dates in a form as selection criteria in your queries?

If so do you mean a 'Date From' and an a 'Date To' ?

And which date(s) in the tables are you testing against?
0
 
LVL 3

Author Comment

by:aplona
ID: 38336630
Yes, i need to use the form "Totals" to enter the date range.  the table and field for the date range is referral.referral DT
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1200 total points
ID: 38336709
In the sql view of the first query, add to the end of the query (before the ; )...

where referral.[referralDT] between forms!Totals!DateFrom and forms!Totals!DateTo

Referral  is not part of the second query, so I don't see where it fits in.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 3

Author Comment

by:aplona
ID: 38336749
Ok, i went ahead and added that,  i open my form enter the two dates and click the comman button that is set to open that query but it returns no results.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 38336765
Another option is to store the dates entered on a form to custom database properties.  Then the query can use them even if the form isn't open, which is handy during development.  Here is some sample code for working with database properties:

Option Compare Database
Option Explicit

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


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

On Error GoTo ErrorHandler

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

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.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 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.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"
      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", ""))

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 77

Expert Comment

by:peter57r
ID: 38336800
If you are getting no results then the data needs to be checked.  You have a lot of inner joins operating which is clearly going to impact on the results that are returned.

But for example- i can run this query here and get what I expect ...

SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Order Date]
FROM Orders
WHERE Orders.[Order Date] Between [forms]![ordersrequest]![Order Date] And [forms]![Ordersrequest]![Shipped Date];
0
 
LVL 3

Author Comment

by:aplona
ID: 38336808
I got it, there was a misspelling.  The second query pulls from the first,  how can i get date ranges in that one?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38336837
If the second query gets its data from the first query there is no need to use the dates in the second query.  They will be picked up by the first query.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
I came across an unsolved Outlook issue and here is my solution.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

580 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