asked on

Access Query And Forms

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;

peter57r

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?

aplona

ASKER

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

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aplona

ASKER

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.

Helen Feddema

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

peter57r

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];

aplona

ASKER

I got it, there was a misspelling. The second query pulls from the first, how can i get date ranges in that one?

peter57r

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.