Parameter query (1 Viewer)

[capogna1]

Hi everyone!
So my query looks up between dates, I enter "Start date" and "End date" and this works.
I would like those 2 dates to come up on my report that look at this query.
I might be real simple to do but been trying and can't get it.

Thanks in advance!!!

 

[XPS35]

Make form to enter the dates. Also add a button to open the report.
Both in your query as in your report you can refer to the controls on the form.

 

[sonic8]

I would like those 2 dates to come up on my report that look at this query.

Include the parameters not only as criteria but also as output columns of your query. Then you can easily bind them to controls on your report.
E.g.

SELECT *, [StartDate], [EndDate]
FROM YourTable
WHERE SomeDate BETWEEN [StartDate] AND [EndDate]

 

[Gasman]

I thought you might be able to refer to the form dates directly, but I just get a blank field?
I used the builder to get the form control reference

SELECT Transactions.Date, Transactions.Description, [Forms]![frmEmails]![EmailDate] AS Expr1
FROM Transactions;

 

[capogna1]

Include the parameters not only as criteria but also as output columns of your query. Then you can easily bind them to controls on your report.
E.g.

SELECT *, [StartDate], [EndDate]
FROM YourTable
WHERE SomeDate BETWEEN [StartDate] AND [EndDate]

How do I include output column to my query?
I'm trying to have the Between Dates from "Q_Completed_By_Date1" added to my report "R_CompletedByDate" so the report shows the dates entered in criteria parameter.

 

[MajP]

How about stripping down that database to the relevant forms tables and queries and explain where to look. Most people do not want to waste their time blindly wading through that. Get rid of all password forms.

 

[capogna1]

How about stripping down that database to the relevant forms tables and queries and explain where to look. Most people do not want to waste their time blindly wading through that. Get rid of all password forms.

 

[MajP]

I did exactly as @sonic8 instructed

SELECT
  T_Encounter.Type_Of_Encounter AS [Type Of Encounter],
  T_Encounter.Attending_Name AS [Attending Name],
  T_Encounter.Admit_Date AS [Admission Date],
  T_Encounter.Discharge_Date AS [Discharge Date],
  T_Encounter.Treating_SpecialityID AS [Treating Speciality ID],
  T_Encounter.DateReviewed AS [Date Reviewed],
  T_Encounter.Reviewer,
  DateDiff(
    "d", [Discharge_Date], [ReviewCompletedDate]
  ) AS [Days Old],
  T_Encounter.ReviewCompletedDate AS [Review Completed Date],
  [Enter your start date for report] AS StartDate,
  [Enter your End date for report] AS EndDate
FROM
  T_Encounter
  INNER JOIN Q_DaysOld ON T_Encounter.ID_Enc = Q_DaysOld.ID_Enc
WHERE
  (
    (
      (
        T_Encounter.ReviewCompletedDate
      ) Is Not Null
      And (
        T_Encounter.ReviewCompletedDate
      ) Between [Enter your start date for report]
      And [Enter your end date for report]
    )
  );

I just added the calculated fields
StartDate: [Enter your start date for report]
EndDate: [Enter your End date for report]

FYI the text in the Parameters has to be EXACT. One spelling error and this does not work.
I would make a form to do this so that you can validate the dates to ensure start is before end date and both are valid dates. Then you could also give a nice date picker to make entering dates easier and less error prone.

 

[capogna1]

I did exactly as @sonic8 instructed

SELECT
  T_Encounter.Type_Of_Encounter AS [Type Of Encounter],
  T_Encounter.Attending_Name AS [Attending Name],
  T_Encounter.Admit_Date AS [Admission Date],
  T_Encounter.Discharge_Date AS [Discharge Date],
  T_Encounter.Treating_SpecialityID AS [Treating Speciality ID],
  T_Encounter.DateReviewed AS [Date Reviewed],
  T_Encounter.Reviewer,
  DateDiff(
    "d", [Discharge_Date], [ReviewCompletedDate]
  ) AS [Days Old],
  T_Encounter.ReviewCompletedDate AS [Review Completed Date],
  [Enter your start date for report] AS StartDate,
  [Enter your End date for report] AS EndDate
FROM
  T_Encounter
  INNER JOIN Q_DaysOld ON T_Encounter.ID_Enc = Q_DaysOld.ID_Enc
WHERE
  (
    (
      (
        T_Encounter.ReviewCompletedDate
      ) Is Not Null
      And (
        T_Encounter.ReviewCompletedDate
      ) Between [Enter your start date for report]
      And [Enter your end date for report]
    )
  );

View attachment 103990
I just added the calculated fields
StartDate: [Enter your start date for report]
EndDate: [Enter your End date for report]

FYI the text in the Parameters has to be EXACT. One spelling error and this does not work.
I would make a form to do this so that you can validate the dates to ensure start is before end date and both are valid dates. Then you could also give a nice date picker to make entering dates easier and less error prone.T

I did exactly as @sonic8 instructed

SELECT
  T_Encounter.Type_Of_Encounter AS [Type Of Encounter],
  T_Encounter.Attending_Name AS [Attending Name],
  T_Encounter.Admit_Date AS [Admission Date],
  T_Encounter.Discharge_Date AS [Discharge Date],
  T_Encounter.Treating_SpecialityID AS [Treating Speciality ID],
  T_Encounter.DateReviewed AS [Date Reviewed],
  T_Encounter.Reviewer,
  DateDiff(
    "d", [Discharge_Date], [ReviewCompletedDate]
  ) AS [Days Old],
  T_Encounter.ReviewCompletedDate AS [Review Completed Date],
  [Enter your start date for report] AS StartDate,
  [Enter your End date for report] AS EndDate
FROM
  T_Encounter
  INNER JOIN Q_DaysOld ON T_Encounter.ID_Enc = Q_DaysOld.ID_Enc
WHERE
  (
    (
      (
        T_Encounter.ReviewCompletedDate
      ) Is Not Null
      And (
        T_Encounter.ReviewCompletedDate
      ) Between [Enter your start date for report]
      And [Enter your end date for report]
    )
  );

View attachment 103990
I just added the calculated fields
StartDate: [Enter your start date for report]
EndDate: [Enter your End date for report]

FYI the text in the Parameters has to be EXACT. One spelling error and this does not work.
I would make a form to do this so that you can validate the dates to ensure start is before end date and both are valid dates. Then you could also give a nice date picker to make entering dates easier and less error prone.

Thanks Much!!!