[AccessD] It's About Time I Learned This

Rocky,
you could adapt from this. This is from a vb project that has queries in a
Access database.

Public Sub LoadCancelList(ByVal pBadgeNbr As Long, ByVal pVacationYear As
Long, pCutoffDateSingleDays As Date, pCutOffDateWeeks)
    Dim mConn As ADODB.Connection
    Dim mCmd As ADODB.Command
    Set mCmd = New ADODB.Command
    With mCmd
        .CommandText = "vbEmployeeVacCancelList"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@BadgeNbr", adInteger,
adParamInput, , pBadgeNbr)
        .Parameters.Append .CreateParameter("@Vacationyear", adInteger,
adParamInput, , pVacationYear)
        .Parameters.Append .CreateParameter("@CutOffDateSingleDays", adDate,
adParamInput, , pCutoffDateSingleDays)
        .Parameters.Append .CreateParameter("@CutOffDateWeeks", adDate,
adParamInput, , pCutOffDateWeeks)
        SetConnection mConn, True, True, mpdbLocal
        Set .ActiveConnection = mConn
    End With
    ResetRST mrstCancelList
    With mrstCancelList
        .Open mCmd, , adOpenStatic, adLockReadOnly
        Set .ActiveConnection = Nothing

    End With
    mConn.Close
    Set mConn = Nothing
    Set mCmd = Nothing

End Sub

This is what the query looks like in Access:

PARAMETERS [@BadgeNbr] Long, [@VacationYear] Long, [@CutoffDateSingleDays]
DateTime, [@CutoffDateWeeks] DateTime;
SELECT vacLog.VRID, vacLog.BadgeNbr, vacLog.VRType, vacLog.VRDate,
vacLog.VROccCode, vacLog.VRCrew, vacLog.VRStatus, vacLog.VRComments,
vacLog.VRDays, vacLog.vrYear, vacLog.vrModUserID, vacLog.vrModTimestamp,
vacLog.RelatedVRID, vacLog_1.VRType AS [Related Type], vacLog_1.VRDate AS
[Related Date]
FROM LKUP_VRTypes INNER JOIN (LKUP_VRStatus INNER JOIN (vacLog LEFT JOIN
vacLog AS vacLog_1 ON vacLog.RelatedVRID=vacLog_1.VRID) ON
LKUP_VRStatus.VRStatus=vacLog.VRStatus) ON LKUP_VRTypes.VRType=vacLog.VRType
WHERE (((vacLog.BadgeNbr)=[@BadgeNbr]) AND
((vacLog.VRDate)>[@CutOffDateSingleDays]) AND
((vacLog.vrYear)=[@VacationYear]) AND ((LKUP_VRStatus.VRAllowCancel)=True)
AND ((LKUP_VRTypes.VRIndividualType)=True)) OR
(((vacLog.BadgeNbr)=[@BadgeNbr]) AND ((vacLog.VRDate)>[@CutOffDateWeeks])
AND ((vacLog.vrYear)=[@VacationYear]) AND
((LKUP_VRStatus.VRAllowCancel)=True) AND
((LKUP_VRTypes.VRIndividualType)=False))
ORDER BY vacLog.VRDate;


Douglas Barnes

Starn Technical Services
P.O. Box 1172
1057 French Street
Meadville, PA  16335

P: 814.724.1045
F: 814.337.3460