multiple prompting for parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi -

I use [From:] and [To:] as parameters in the criteria area for a column of a
query. I also have [From:] and [To:] columns. For the most part, when the
query is invoked, I am only asked once for each date. But sometimes, I am
asked twice!

I tried changing the order of the columns (putting the [From:] and [To:]
columns before the column where they are used as criteria), and this seemed
to lessen the incidence, but it did just happen again. Could it have
something to do with leaving off the century when I entered the date value?

Just curious if there are any special rules that govern when the user is
prompted for a parameter, and why it might intermittendly prompt a second
time?

Thanks,

Phil Freihofner
 
Phil

Are you saying that the name you gave a couple columns in your tables (e.g.,
[From] and [To] are identical to the parameter prompts you added to a query?

If so, how will Access know which one(s) you are referring to?

Try this -- modify the parameter prompt to [From date] and [To date] and see
if you still have problems.

Good luck

Jeff Boyce
<Access MVP>
 
Sorry I wasn't more clear.

The "columns" I refer to in the query for "[From:]" and "[To:]" are not
table columns. I just meant a column in the query.

Here is more precisely the form of what I have:

StDt: [From:]

....where StDt is the label (the AS portion in SQL) and [From:] is the
parameter, not a table value.

On another field, [From:] is used in the criteria part.

Thanks,
Phil

Jeff Boyce said:
Phil

Are you saying that the name you gave a couple columns in your tables (e.g.,
[From] and [To] are identical to the parameter prompts you added to a query?

If so, how will Access know which one(s) you are referring to?

Try this -- modify the parameter prompt to [From date] and [To date] and see
if you still have problems.

Good luck

Jeff Boyce
<Access MVP>
Phil F said:
Hi -

I use [From:] and [To:] as parameters in the criteria area for a column of
a
query. I also have [From:] and [To:] columns. For the most part, when the
query is invoked, I am only asked once for each date. But sometimes, I am
asked twice!

I tried changing the order of the columns (putting the [From:] and [To:]
columns before the column where they are used as criteria), and this
seemed
to lessen the incidence, but it did just happen again. Could it have
something to do with leaving off the century when I entered the date
value?

Just curious if there are any special rules that govern when the user is
prompted for a parameter, and why it might intermittendly prompt a second
time?

Thanks,

Phil Freihofner
Click to expand...
Click to expand...
 
It's a little involved. (Hence my attempt to simplify and ask in more general
terms.)

SELECT tblPatient.UPN, tblPatient.PtLastName, tblPatient.PtFirstName,
tblTransplant.TypeofInfusion, tblTransplant.BMTDate,
ageinyears([BirthDate],[BMTDate]) AS AgeAtTransplant, tblPatient.Gender,
GetDiagnosis([TransplantID]) AS Diagnosis, tblTransplant.Source,
GetAntigenMismatch([transplantID]) AS AntigenMismatch, tblDonor.DRelation,
tblDonor.DonorCategory, tblTransplant.AdmitDate, tblTransplant.DischDate,
URN_LengthOfStay([AdmitDate],[DischDate]) AS LenOfStay_days,
tblTransplant.DateANC, tblProtocol.ProtocolDescription,
GetAGVHDStatus([tblPatient].[PtID]) AS AGVHDGrade,
GetCGVHDStatus([tblPatient].[PtID]) AS CGVHDGrade,
GetPtOutcome([tblPatient].[PtID]) AS Outcome, DeathDate([tblPatient].[PtID])
AS DateOfDeath, POSurvival([tblPatient].[PtID],[BMTDate],"d") AS
PostOpSurvival_Days, POSurvival([tblPatient].[PtID],[BMTDate],"m") AS
PostOpSurvival_Months, qstillalive([BMTDate],[tblPatient].[PtID],100,"d") AS
AliveAt100Days, qstillalive([BMTDate],[tblPatient].[PtID],1,"YYYY") AS
AliveAt1Year, qcauseofdeathrelapse([BMTDate],[tblPatient].[PtID],100,"d") AS
CauseRelapseWithin100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],100,"d") AS LosttoFUAt100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],1,"YYYY") AS LosttoFUAt1Year,
GetProcessingType([TransplantID]) AS Processing, GetCD3([TransplantID]) AS
CD3, GetCD34([TransplantID]) AS CD34, [StartDate] AS StDt, [EndDate] AS EndDt
FROM ((tblDonor RIGHT JOIN tblTransplant ON tblDonor.DonorID =
tblTransplant.DonorID) INNER JOIN tblPatient ON tblTransplant.PtID =
tblPatient.PtID) LEFT JOIN tblProtocol ON tblTransplant.CondProtocolID =
tblProtocol.ProtocolID
WHERE (((tblTransplant.BMTDate)>=[StartDate] And
(tblTransplant.BMTDate)<=[EndDate]))
ORDER BY tblTransplant.BMTDate;


I put the StDt and EndDt as the first columns, and the query is usually
fine, but at least once it has again prompted for each date twice.
Unfortunately it is intermittent and hard to reproduce.

Van T. Dinh said:
Post the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)


Phil F said:
Sorry I wasn't more clear.

The "columns" I refer to in the query for "[From:]" and "[To:]" are not
table columns. I just meant a column in the query.

Here is more precisely the form of what I have:

StDt: [From:]

...where StDt is the label (the AS portion in SQL) and [From:] is the
parameter, not a table value.

On another field, [From:] is used in the criteria part.

Thanks,
Phil
Click to expand...
Click to expand...
 
I tested in A2002 running a similar SQL 4 times and I could not repeat the
behaviour you observed. However, my sample data set may be too small to
create the same behaviour.

When the problem occurred, did you check that the spelling is exactly the
same? Check also the Menu Query / Parameters ... in case there are
Parameters left over that have similar spelling to your current Parameters.

A work-around is to create a "frmParameter" that has 2 TextBoxes for
StartDate and EndDate and use these TextBoxes as the Parameters in your
Query. You can also use the values in these TextBoxes whereever you need
and therefore no need to include the Parameter values in the selection list.

--
HTH
Van T. Dinh
MVP (Access)




Phil F said:
It's a little involved. (Hence my attempt to simplify and ask in more general
terms.)

SELECT tblPatient.UPN, tblPatient.PtLastName, tblPatient.PtFirstName,
tblTransplant.TypeofInfusion, tblTransplant.BMTDate,
ageinyears([BirthDate],[BMTDate]) AS AgeAtTransplant, tblPatient.Gender,
GetDiagnosis([TransplantID]) AS Diagnosis, tblTransplant.Source,
GetAntigenMismatch([transplantID]) AS AntigenMismatch, tblDonor.DRelation,
tblDonor.DonorCategory, tblTransplant.AdmitDate, tblTransplant.DischDate,
URN_LengthOfStay([AdmitDate],[DischDate]) AS LenOfStay_days,
tblTransplant.DateANC, tblProtocol.ProtocolDescription,
GetAGVHDStatus([tblPatient].[PtID]) AS AGVHDGrade,
GetCGVHDStatus([tblPatient].[PtID]) AS CGVHDGrade,
GetPtOutcome([tblPatient].[PtID]) AS Outcome, DeathDate([tblPatient].[PtID])
AS DateOfDeath, POSurvival([tblPatient].[PtID],[BMTDate],"d") AS
PostOpSurvival_Days, POSurvival([tblPatient].[PtID],[BMTDate],"m") AS
PostOpSurvival_Months, qstillalive([BMTDate],[tblPatient].[PtID],100,"d") AS
AliveAt100Days, qstillalive([BMTDate],[tblPatient].[PtID],1,"YYYY") AS
AliveAt1Year, qcauseofdeathrelapse([BMTDate],[tblPatient].[PtID],100,"d") AS
CauseRelapseWithin100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],100,"d") AS LosttoFUAt100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],1,"YYYY") AS LosttoFUAt1Year,
GetProcessingType([TransplantID]) AS Processing, GetCD3([TransplantID]) AS
CD3, GetCD34([TransplantID]) AS CD34, [StartDate] AS StDt, [EndDate] AS EndDt
FROM ((tblDonor RIGHT JOIN tblTransplant ON tblDonor.DonorID =
tblTransplant.DonorID) INNER JOIN tblPatient ON tblTransplant.PtID =
tblPatient.PtID) LEFT JOIN tblProtocol ON tblTransplant.CondProtocolID =
tblProtocol.ProtocolID
WHERE (((tblTransplant.BMTDate)>=[StartDate] And
(tblTransplant.BMTDate)<=[EndDate]))
ORDER BY tblTransplant.BMTDate;


I put the StDt and EndDt as the first columns, and the query is usually
fine, but at least once it has again prompted for each date twice.
Unfortunately it is intermittent and hard to reproduce.
Click to expand...
 
Hi Van -

Thanks for sticking with this one. I haven't had the problem come up again.
If it recurs, I'll try using txtboxes. The idea of leftover parameters is
interesting, didn't occur to me.

The data sample is not that large, there's not even 1000 patients, even
though there is a lot going on in terms of all the function calls. The time
it happened, I had left off the century, but that shouldn't have mattered.
Just one of those occasional weird, unexplainable things, I guess. (Though
usually they do end up having a real, knowable cause.) Maybe it was because
that time I had an audience...

Phil

Van T. Dinh said:
I tested in A2002 running a similar SQL 4 times and I could not repeat the
behaviour you observed. However, my sample data set may be too small to
create the same behaviour.

When the problem occurred, did you check that the spelling is exactly the
same? Check also the Menu Query / Parameters ... in case there are
Parameters left over that have similar spelling to your current Parameters.

A work-around is to create a "frmParameter" that has 2 TextBoxes for
StartDate and EndDate and use these TextBoxes as the Parameters in your
Query. You can also use the values in these TextBoxes whereever you need
and therefore no need to include the Parameter values in the selection list.

--
HTH
Van T. Dinh
MVP (Access)




Phil F said:
It's a little involved. (Hence my attempt to simplify and ask in more general
terms.)

SELECT tblPatient.UPN, tblPatient.PtLastName, tblPatient.PtFirstName,
tblTransplant.TypeofInfusion, tblTransplant.BMTDate,
ageinyears([BirthDate],[BMTDate]) AS AgeAtTransplant, tblPatient.Gender,
GetDiagnosis([TransplantID]) AS Diagnosis, tblTransplant.Source,
GetAntigenMismatch([transplantID]) AS AntigenMismatch, tblDonor.DRelation,
tblDonor.DonorCategory, tblTransplant.AdmitDate, tblTransplant.DischDate,
URN_LengthOfStay([AdmitDate],[DischDate]) AS LenOfStay_days,
tblTransplant.DateANC, tblProtocol.ProtocolDescription,
GetAGVHDStatus([tblPatient].[PtID]) AS AGVHDGrade,
GetCGVHDStatus([tblPatient].[PtID]) AS CGVHDGrade,
GetPtOutcome([tblPatient].[PtID]) AS Outcome, DeathDate([tblPatient].[PtID])
AS DateOfDeath, POSurvival([tblPatient].[PtID],[BMTDate],"d") AS
PostOpSurvival_Days, POSurvival([tblPatient].[PtID],[BMTDate],"m") AS
PostOpSurvival_Months, qstillalive([BMTDate],[tblPatient].[PtID],100,"d") AS
AliveAt100Days, qstillalive([BMTDate],[tblPatient].[PtID],1,"YYYY") AS
AliveAt1Year, qcauseofdeathrelapse([BMTDate],[tblPatient].[PtID],100,"d") AS
CauseRelapseWithin100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],100,"d") AS LosttoFUAt100Days,
qlosttofollowup([BMTDate],[tblPatient].[PtID],1,"YYYY") AS LosttoFUAt1Year,
GetProcessingType([TransplantID]) AS Processing, GetCD3([TransplantID]) AS
CD3, GetCD34([TransplantID]) AS CD34, [StartDate] AS StDt, [EndDate] AS EndDt
FROM ((tblDonor RIGHT JOIN tblTransplant ON tblDonor.DonorID =
tblTransplant.DonorID) INNER JOIN tblPatient ON tblTransplant.PtID =
tblPatient.PtID) LEFT JOIN tblProtocol ON tblTransplant.CondProtocolID =
tblProtocol.ProtocolID
WHERE (((tblTransplant.BMTDate)>=[StartDate] And
(tblTransplant.BMTDate)<=[EndDate]))
ORDER BY tblTransplant.BMTDate;


I put the StDt and EndDt as the first columns, and the query is usually
fine, but at least once it has again prompted for each date twice.
Unfortunately it is intermittent and hard to reproduce.
Click to expand...
Click to expand...
 
Back
Top