Date Parameters Irritation

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));
 
D

Dale Fye

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.
 
J

Jeff Boyce

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...
 
J

John Spencer

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
 
K

knowshowrosegrows

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...
 
D

Dale Fye

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL problem... 4
MAX Function 1
Date Query 2
Criteria/If situation? 3
Sum of a count field 1
Querying for the Last Records 1
Query not pulling data anymore, do you have any suggestions? 3
query giving odd results 2

Top