J
Jack Peyton
I use Access 2000. I have a form "FMonPayParkA" that has a subform
"FMonPayParkB". The subform is set to continuous view and has the
following textboxes:
MonYr
AmtPaid
DatePaid
GrpID
EmpNo
ParkLotNameID.
which are bound to query for the subform is "QMonPayParkB".
The subform has an additional textbox "AmtOwed". The record source for
textbox AmtOwed is
DLookUp("[PayRate]","QMonPayParkBAmtOweOriginal","[ExpEffDate]<=Forms!FMonPa
yParkA!FMonPayParkB.Form![MonYr]").
QMonPayParkBAmtOweOriginal is a query that finds the "PayRate" for
"AmtOwed" based on the date in "MonYr". The SQL for the query is:
SELECT TOP 1 Max(DateSerial(Year([EffDate]),Month([EffDate]),1)) AS
ExpEffDate, TPayPark.PayRate, TPayPark.ParkLotNameID
FROM TPayPark
GROUP BY TPayPark.PayRate, TPayPark.ParkLotNameID
HAVING
(((Max(DateSerial(Year([EffDate]),Month([EffDate]),1)))<=DateSerial(Year([Fo
rms]![FMonPayParkA]![FMonPayParkB].[Form]![MonYr]),Month([Forms]![FMonPayPar
kA]![FMonPayParkB].[Form]![MonYr]),1)) AND
((TPayPark.ParkLotNameID)=[Forms]![FMonPayParkA]![FMonPayParkB].[Form]![Park
LotNameID]))
ORDER BY Max(DateSerial(Year([EffDate]),Month([EffDate]),1)) DESC;
The sub form shows the "AmtOwed" charges for each month and allows entry for
"AmtPaid" and "DatePaid". Is there any way to move the above DLookUp to the
query for the subform?
Any Help will be appreciated?
Thanks,
Jack Peyton
"FMonPayParkB". The subform is set to continuous view and has the
following textboxes:
MonYr
AmtPaid
DatePaid
GrpID
EmpNo
ParkLotNameID.
which are bound to query for the subform is "QMonPayParkB".
The subform has an additional textbox "AmtOwed". The record source for
textbox AmtOwed is
DLookUp("[PayRate]","QMonPayParkBAmtOweOriginal","[ExpEffDate]<=Forms!FMonPa
yParkA!FMonPayParkB.Form![MonYr]").
QMonPayParkBAmtOweOriginal is a query that finds the "PayRate" for
"AmtOwed" based on the date in "MonYr". The SQL for the query is:
SELECT TOP 1 Max(DateSerial(Year([EffDate]),Month([EffDate]),1)) AS
ExpEffDate, TPayPark.PayRate, TPayPark.ParkLotNameID
FROM TPayPark
GROUP BY TPayPark.PayRate, TPayPark.ParkLotNameID
HAVING
(((Max(DateSerial(Year([EffDate]),Month([EffDate]),1)))<=DateSerial(Year([Fo
rms]![FMonPayParkA]![FMonPayParkB].[Form]![MonYr]),Month([Forms]![FMonPayPar
kA]![FMonPayParkB].[Form]![MonYr]),1)) AND
((TPayPark.ParkLotNameID)=[Forms]![FMonPayParkA]![FMonPayParkB].[Form]![Park
LotNameID]))
ORDER BY Max(DateSerial(Year([EffDate]),Month([EffDate]),1)) DESC;
The sub form shows the "AmtOwed" charges for each month and allows entry for
"AmtPaid" and "DatePaid". Is there any way to move the above DLookUp to the
query for the subform?
Any Help will be appreciated?
Thanks,
Jack Peyton