Query prompts for StartDt and EndDt?

[Pat Dools]

Hello I am running an append query as part of a macro, which gets kicked off
by a command button placed on a form (called, 'Switchboard_Reporting').
When I run the append query from the Database Window, the query runs and
does not prompt me for dates - it reads the dates from the
'Switchboard_Reporting' form and appends the records.

When I run the macro from the 'Switchboard_Reporting' form, I get prompted
to enter both 'StartDt' and 'EndDt', which I am getting from the
'Switchboard_Reporting' form. I want these values to be on the actual record
that gets appended, as I use these dates in a report downstream.

I can't seem to figure out how to edit the query to stop prompting me when I
call the macro from the ''Switchboard_Reporting' form. Any ideas would be
helpful and thanks! Here is the SQL query I'm running:

INSERT INTO tVisits_per_hlthctr ( HealthCtr_Name, Visit_Count,
Subject_Count, New_Kids_Count, StartDt_Range, EndDt_Range )
SELECT tHealthCtr_LU.HealthCtr_Name, Count(tAttendance_Tracking.Visit_Date)
AS Visit_Count, 0 AS Subject_Count, 0 AS New_Kids_Count,
Min(Forms!Switchboard_Reporting!StartDt) AS StartDt_Range,
Min(Forms!Switchboard_Reporting!EndDt) AS EndDt_Range
FROM (tHealthCtr_LU INNER JOIN tSubject_Info ON tHealthCtr_LU.HealthCtr_ID =
tSubject_Info.HealthCtr_ID) LEFT JOIN tAttendance_Tracking ON
tSubject_Info.Subject_ID = tAttendance_Tracking.Subject_ID
WHERE (((tAttendance_Tracking.Visit_Date) Between
[Forms]![Switchboard_Reporting]![StartDt] And
[Forms]![Switchboard_Reporting]![EndDt]))
GROUP BY tHealthCtr_LU.HealthCtr_Name, 0, 0;

 

[Ken Snell [MVP]]

What is the value of the ''Switchboard_Reporting" form's RecordSource
property? How do you run the macro from that form -- clicking a button on
the form? running it manually?

 

[NJBrit]

Your query is expecting the form to have StartDt and EndDt populated. If you
run the macro without those, Access will prompt you.
BTW you MIN on StartDt AND EndDt - is that what you want?

 

[Pat Dools]

Hi Ken,

The ''Switchboard_Reporting" is just a switchboard-type form that has no
official record source. The query I reference below merely references the
'Start Date' and 'End Date' fields on the ''Switchboard_Reporting" form. I
run the macro by clicking a button on the form. Let me know if you need any
other info.
Thanks.

What is the value of the ''Switchboard_Reporting" form's RecordSource
property? How do you run the macro from that form -- clicking a button on
the form? running it manually?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello I am running an append query as part of a macro, which gets kicked
off
by a command button placed on a form (called, 'Switchboard_Reporting').
When I run the append query from the Database Window, the query runs and
does not prompt me for dates - it reads the dates from the
'Switchboard_Reporting' form and appends the records.

When I run the macro from the 'Switchboard_Reporting' form, I get prompted
to enter both 'StartDt' and 'EndDt', which I am getting from the
'Switchboard_Reporting' form. I want these values to be on the actual
record
that gets appended, as I use these dates in a report downstream.

I can't seem to figure out how to edit the query to stop prompting me when
I
call the macro from the ''Switchboard_Reporting' form. Any ideas would be
helpful and thanks! Here is the SQL query I'm running:

INSERT INTO tVisits_per_hlthctr ( HealthCtr_Name, Visit_Count,
Subject_Count, New_Kids_Count, StartDt_Range, EndDt_Range )
SELECT tHealthCtr_LU.HealthCtr_Name,
Count(tAttendance_Tracking.Visit_Date)
AS Visit_Count, 0 AS Subject_Count, 0 AS New_Kids_Count,
Min(Forms!Switchboard_Reporting!StartDt) AS StartDt_Range,
Min(Forms!Switchboard_Reporting!EndDt) AS EndDt_Range
FROM (tHealthCtr_LU INNER JOIN tSubject_Info ON tHealthCtr_LU.HealthCtr_ID
=
tSubject_Info.HealthCtr_ID) LEFT JOIN tAttendance_Tracking ON
tSubject_Info.Subject_ID = tAttendance_Tracking.Subject_ID
WHERE (((tAttendance_Tracking.Visit_Date) Between
[Forms]![Switchboard_Reporting]![StartDt] And
[Forms]![Switchboard_Reporting]![EndDt]))
GROUP BY tHealthCtr_LU.HealthCtr_Name, 0, 0;

 

[Ken Snell [MVP]]

The only thing that comes to mind is that the macro being run by the form's
button is not running the same query that you're running from the database
window. Doublecheck the arguments of the OpenQuery action in the macro.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,

The ''Switchboard_Reporting" is just a switchboard-type form that has no
official record source. The query I reference below merely references the
'Start Date' and 'End Date' fields on the ''Switchboard_Reporting" form.
I
run the macro by clicking a button on the form. Let me know if you need
any
other info.
Thanks.

What is the value of the ''Switchboard_Reporting" form's RecordSource
property? How do you run the macro from that form -- clicking a button on
the form? running it manually?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello I am running an append query as part of a macro, which gets
kicked
off
by a command button placed on a form (called, 'Switchboard_Reporting').
When I run the append query from the Database Window, the query runs
and
does not prompt me for dates - it reads the dates from the
'Switchboard_Reporting' form and appends the records.

When I run the macro from the 'Switchboard_Reporting' form, I get
prompted
to enter both 'StartDt' and 'EndDt', which I am getting from the
'Switchboard_Reporting' form. I want these values to be on the actual
record
that gets appended, as I use these dates in a report downstream.

I can't seem to figure out how to edit the query to stop prompting me
when
I
call the macro from the ''Switchboard_Reporting' form. Any ideas would
be
helpful and thanks! Here is the SQL query I'm running:

INSERT INTO tVisits_per_hlthctr ( HealthCtr_Name, Visit_Count,
Subject_Count, New_Kids_Count, StartDt_Range, EndDt_Range )
SELECT tHealthCtr_LU.HealthCtr_Name,
Count(tAttendance_Tracking.Visit_Date)
AS Visit_Count, 0 AS Subject_Count, 0 AS New_Kids_Count,
Min(Forms!Switchboard_Reporting!StartDt) AS StartDt_Range,
Min(Forms!Switchboard_Reporting!EndDt) AS EndDt_Range
FROM (tHealthCtr_LU INNER JOIN tSubject_Info ON
tHealthCtr_LU.HealthCtr_ID
=
tSubject_Info.HealthCtr_ID) LEFT JOIN tAttendance_Tracking ON
tSubject_Info.Subject_ID = tAttendance_Tracking.Subject_ID
WHERE (((tAttendance_Tracking.Visit_Date) Between
[Forms]![Switchboard_Reporting]![StartDt] And
[Forms]![Switchboard_Reporting]![EndDt]))
GROUP BY tHealthCtr_LU.HealthCtr_Name, 0, 0;