Date Parameters Irritation

  • Thread starter Thread starter knowshowrosegrows
  • Start date Start date
K

knowshowrosegrows

I have a query with the below SQL. Can someone explain why I get two sets of
"Choose Beginning Census Date" and Choose Ending Census Date" message boxes
when I run this?

SELECT tblProgram.Prm_Code, tblProgram.Location,
tblRunningCapacity.CapEndDate, tblRunningCapacity.Cap,
tblLOCID.OrderOfIntensity, tblAgency.Agency, tblAgency.Reg,
tblLOCID.CareMode, tblCensusEvent.Admiss, tblCensusEvent.[D/C],
tblCensusEvent.Census, tblCensusEvent.CensusDate, tblCensusEvent.Census_ID,
tblLOCOld.LOC_Alias, tblCensusEvent.Census/tblRunningCapacity.CAP AS
Utilization,
(tblRunningCapacity.CAP-tblCensusEvent.Census)/tblRunningCapacity.CAP AS
VacancyRate, tblProgram.Inactive
FROM tblAgency INNER JOIN (tblLOCID INNER JOIN (((tblLOCOld INNER JOIN
tblProgram ON tblLOCOld.Prm_Code = tblProgram.Prm_Code) INNER JOIN
tblCensusEvent ON tblProgram.Prm_Code = tblCensusEvent.Prm_Code) LEFT JOIN
tblRunningCapacity ON tblProgram.Prm_Code = tblRunningCapacity.Prm_Code) ON
tblLOCID.LOC_ID = tblProgram.LOC_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate) Between [Choose Beginning Census Date]
And [Choose Ending Census Date]) AND ((tblProgram.Inactive)<>-1));
 
Have you tried declaring these values as parameters?

If not, add the following at the beginning of your SELECT statement

PARAMETERS [Choose Beginning Census Date] DateTime,
[Choose Ending Census Date] DateTime;
SELECT ...

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
If the field you are asking the SQL to check is a Date/Time field, the
value(s) you check need to be delimited as date/time values (i.e., using a
"#"). For example:
WHERE (((tblCensusEvent.CensusDate) Between #12/21/2008# And
Click to expand...

Regards

Jeff Boyce
Microsoft Office/Access MVP



knowshowrosegrows said:
I have a query with the below SQL. Can someone explain why I get two sets
of
"Choose Beginning Census Date" and Choose Ending Census Date" message
boxes
when I run this?

SELECT tblProgram.Prm_Code, tblProgram.Location,
tblRunningCapacity.CapEndDate, tblRunningCapacity.Cap,
tblLOCID.OrderOfIntensity, tblAgency.Agency, tblAgency.Reg,
tblLOCID.CareMode, tblCensusEvent.Admiss, tblCensusEvent.[D/C],
tblCensusEvent.Census, tblCensusEvent.CensusDate,
tblCensusEvent.Census_ID,
tblLOCOld.LOC_Alias, tblCensusEvent.Census/tblRunningCapacity.CAP AS
Utilization,
(tblRunningCapacity.CAP-tblCensusEvent.Census)/tblRunningCapacity.CAP AS
VacancyRate, tblProgram.Inactive
FROM tblAgency INNER JOIN (tblLOCID INNER JOIN (((tblLOCOld INNER JOIN
tblProgram ON tblLOCOld.Prm_Code = tblProgram.Prm_Code) INNER JOIN
tblCensusEvent ON tblProgram.Prm_Code = tblCensusEvent.Prm_Code) LEFT JOIN
tblRunningCapacity ON tblProgram.Prm_Code = tblRunningCapacity.Prm_Code)
ON
tblLOCID.LOC_ID = tblProgram.LOC_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate) Between [Choose Beginning Census Date]
And [Choose Ending Census Date]) AND ((tblProgram.Inactive)<>-1));
Click to expand...
 
One possibility is that you have the order by property set for the query and
are viewing the query directly in datasheet view. Try opening the query in
datasheet view and selecting Remove Filter/Sort from the Records menu and then
pressing Control + S to save the queries display setup. S

IF that is the case, it may be that the query is running twice and therefore
is asking you for the values twice. Try opening the query in datasheet view
and selecting Remove Filter/Sort from the Records menu and then pressing
Control + S to save the queries display setup. Then try running the query again.

Is there a delay between asking the first and second time or do you get asked
twice before anything happens?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
So, I opened it in datasheet and removed the filter/sort and saved it and it
opened perfectly. Then I opened another query that uses the one you just
fixed (SQL below) and got the error message "Microsoft Jet doesnt recognize
[Choose Beginning Census Date] as a valid field name or expression."

TRANSFORM First(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblAgency.Reg, tblAgency.Agency, tblProgram.Location,
qryVacancyRateTrendFirst.Cap AS [Current Cap], tblLOCID.LOC_Alias,
tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of
Vacancy Rate]
FROM tblAgency RIGHT JOIN (tblLOCID RIGHT JOIN (qryVacancyRateTrendFirst
RIGHT JOIN tblProgram ON qryVacancyRateTrendFirst.Prm_Code =
tblProgram.Prm_Code) ON tblLOCID.LOC_ID = tblProgram.LOC_ID) ON
tblAgency.Agency_ID = tblProgram.Agency_ID
WHERE (((qryVacancyRateTrendFirst.CensusDate) Between
DateAdd("yyyy",-1,Date()) And Date()) AND ((tblProgram.Inactive)<>-1)) OR
(((tblProgram.Inactive)<>-1) AND ((tblProgram.Prm_Code) Is Not Null))
GROUP BY tblLOCID.OrderOfIntensity, tblAgency.Reg, tblAgency.Agency,
tblProgram.Location, qryVacancyRateTrendFirst.Cap, tblLOCID.LOC_Alias,
tblProgram.Prm_Code, tblProgram.Inactive
PIVOT Format([CensusDate],"mm-dd");


--
Thanks

You all are teaching me so much


John Spencer said:
One possibility is that you have the order by property set for the query and
are viewing the query directly in datasheet view. Try opening the query in
datasheet view and selecting Remove Filter/Sort from the Records menu and then
pressing Control + S to save the queries display setup. S

IF that is the case, it may be that the query is running twice and therefore
is asking you for the values twice. Try opening the query in datasheet view
and selecting Remove Filter/Sort from the Records menu and then pressing
Control + S to save the queries display setup. Then try running the query again.

Is there a delay between asking the first and second time or do you get asked
twice before anything happens?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query with the below SQL. Can someone explain why I get two sets of
"Choose Beginning Census Date" and Choose Ending Census Date" message boxes
when I run this?

SELECT tblProgram.Prm_Code, tblProgram.Location,
tblRunningCapacity.CapEndDate, tblRunningCapacity.Cap,
tblLOCID.OrderOfIntensity, tblAgency.Agency, tblAgency.Reg,
tblLOCID.CareMode, tblCensusEvent.Admiss, tblCensusEvent.[D/C],
tblCensusEvent.Census, tblCensusEvent.CensusDate, tblCensusEvent.Census_ID,
tblLOCOld.LOC_Alias, tblCensusEvent.Census/tblRunningCapacity.CAP AS
Utilization,
(tblRunningCapacity.CAP-tblCensusEvent.Census)/tblRunningCapacity.CAP AS
VacancyRate, tblProgram.Inactive
FROM tblAgency INNER JOIN (tblLOCID INNER JOIN (((tblLOCOld INNER JOIN
tblProgram ON tblLOCOld.Prm_Code = tblProgram.Prm_Code) INNER JOIN
tblCensusEvent ON tblProgram.Prm_Code = tblCensusEvent.Prm_Code) LEFT JOIN
tblRunningCapacity ON tblProgram.Prm_Code = tblRunningCapacity.Prm_Code) ON
tblLOCID.LOC_ID = tblProgram.LOC_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate) Between [Choose Beginning Census Date]
And [Choose Ending Census Date]) AND ((tblProgram.Inactive)<>-1));
Click to expand...
Click to expand...
 
If you are going to use that query as the source for another query, you are
going to have to define the parameters, as I indicated in my earlier post.

It's been a while since I nested queries like this, but as I recall, I think
you will actually need to define the parameters in both the original and the
secondary query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



knowshowrosegrows said:
So, I opened it in datasheet and removed the filter/sort and saved it and it
opened perfectly. Then I opened another query that uses the one you just
fixed (SQL below) and got the error message "Microsoft Jet doesnt recognize
[Choose Beginning Census Date] as a valid field name or expression."

TRANSFORM First(qryVacancyRateTrendFirst.VacancyRate) AS VacancyRate
SELECT tblAgency.Reg, tblAgency.Agency, tblProgram.Location,
qryVacancyRateTrendFirst.Cap AS [Current Cap], tblLOCID.LOC_Alias,
tblProgram.Prm_Code, Avg(qryVacancyRateTrendFirst.VacancyRate) AS [Average Of
Vacancy Rate]
FROM tblAgency RIGHT JOIN (tblLOCID RIGHT JOIN (qryVacancyRateTrendFirst
RIGHT JOIN tblProgram ON qryVacancyRateTrendFirst.Prm_Code =
tblProgram.Prm_Code) ON tblLOCID.LOC_ID = tblProgram.LOC_ID) ON
tblAgency.Agency_ID = tblProgram.Agency_ID
WHERE (((qryVacancyRateTrendFirst.CensusDate) Between
DateAdd("yyyy",-1,Date()) And Date()) AND ((tblProgram.Inactive)<>-1)) OR
(((tblProgram.Inactive)<>-1) AND ((tblProgram.Prm_Code) Is Not Null))
GROUP BY tblLOCID.OrderOfIntensity, tblAgency.Reg, tblAgency.Agency,
tblProgram.Location, qryVacancyRateTrendFirst.Cap, tblLOCID.LOC_Alias,
tblProgram.Prm_Code, tblProgram.Inactive
PIVOT Format([CensusDate],"mm-dd");


--
Thanks

You all are teaching me so much


John Spencer said:
One possibility is that you have the order by property set for the query and
are viewing the query directly in datasheet view. Try opening the query in
datasheet view and selecting Remove Filter/Sort from the Records menu and then
pressing Control + S to save the queries display setup. S

IF that is the case, it may be that the query is running twice and therefore
is asking you for the values twice. Try opening the query in datasheet view
and selecting Remove Filter/Sort from the Records menu and then pressing
Control + S to save the queries display setup. Then try running the query again.

Is there a delay between asking the first and second time or do you get asked
twice before anything happens?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query with the below SQL. Can someone explain why I get two sets of
"Choose Beginning Census Date" and Choose Ending Census Date" message boxes
when I run this?

SELECT tblProgram.Prm_Code, tblProgram.Location,
tblRunningCapacity.CapEndDate, tblRunningCapacity.Cap,
tblLOCID.OrderOfIntensity, tblAgency.Agency, tblAgency.Reg,
tblLOCID.CareMode, tblCensusEvent.Admiss, tblCensusEvent.[D/C],
tblCensusEvent.Census, tblCensusEvent.CensusDate, tblCensusEvent.Census_ID,
tblLOCOld.LOC_Alias, tblCensusEvent.Census/tblRunningCapacity.CAP AS
Utilization,
(tblRunningCapacity.CAP-tblCensusEvent.Census)/tblRunningCapacity.CAP AS
VacancyRate, tblProgram.Inactive
FROM tblAgency INNER JOIN (tblLOCID INNER JOIN (((tblLOCOld INNER JOIN
tblProgram ON tblLOCOld.Prm_Code = tblProgram.Prm_Code) INNER JOIN
tblCensusEvent ON tblProgram.Prm_Code = tblCensusEvent.Prm_Code) LEFT JOIN
tblRunningCapacity ON tblProgram.Prm_Code = tblRunningCapacity.Prm_Code) ON
tblLOCID.LOC_ID = tblProgram.LOC_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate) Between [Choose Beginning Census Date]
And [Choose Ending Census Date]) AND ((tblProgram.Inactive)<>-1));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top