T
Tracey
I've run into a problem doing a parameter query I received help from this
forum in developing. I've used the structure of this parameter query in
several circumstances, but this time it won't work. I have a subform called
"fHDHP", which has a subform called "fSalaryBand". I have an update query
which identifies the salary bands without an "end date" for each HDHP based
on a parameter, and then updates the end date to the date I enter in response
to the parameter. What is unique this time is that each HDHPID can be
associated with more than one salary band. The query only identifies, and
thus updates, the first instance of the salary band associated with each
HDHP. Here is the SQL:
PARAMETERS [What is end date for current salary band?] DateTime,
[Forms]![fClientFile]![fBenefitDesignSelect]![fBenefitDesignView]![fHDHPView]![fSalaryBand].Form![SalaryBandID] Long;
UPDATE [Salary Band] SET EndDate = [What is end date for current salary band?]
WHERE
SalaryBandID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!fSalaryBand.Form!SalaryBandID;
When I remove the WHERE statement, the query identifies the correct number
of salary bands to be updated, so I know that is where something is wrong.
I've tried adding:
AND
HDHPID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!.Form!HDHPID
to the WHERE statement, but that doesn't change anything. The 2 forms are
linked on HDHPID. Any suggestions on what I'm doing wrong would be greatly
appreciated. Thanks.
forum in developing. I've used the structure of this parameter query in
several circumstances, but this time it won't work. I have a subform called
"fHDHP", which has a subform called "fSalaryBand". I have an update query
which identifies the salary bands without an "end date" for each HDHP based
on a parameter, and then updates the end date to the date I enter in response
to the parameter. What is unique this time is that each HDHPID can be
associated with more than one salary band. The query only identifies, and
thus updates, the first instance of the salary band associated with each
HDHP. Here is the SQL:
PARAMETERS [What is end date for current salary band?] DateTime,
[Forms]![fClientFile]![fBenefitDesignSelect]![fBenefitDesignView]![fHDHPView]![fSalaryBand].Form![SalaryBandID] Long;
UPDATE [Salary Band] SET EndDate = [What is end date for current salary band?]
WHERE
SalaryBandID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!fSalaryBand.Form!SalaryBandID;
When I remove the WHERE statement, the query identifies the correct number
of salary bands to be updated, so I know that is where something is wrong.
I've tried adding:
AND
HDHPID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!.Form!HDHPID
to the WHERE statement, but that doesn't change anything. The 2 forms are
linked on HDHPID. Any suggestions on what I'm doing wrong would be greatly
appreciated. Thanks.