QueryDef helps please

[NoodNutt]

Hi all

Can anyone assist me with the following.

I would like to insert this:

SELECT tblPupDetails.DateOut, tblPupDetails.PickUpNo,
tblPupDetails.DestState, tblCustomers.CustName, tblPupDetails.QTY,
tblType.TypeDesc, tblPupDetails.Weight, tblPupDetails.DG,
tblPupDetails.DGClass, tblPupDetails.DGSubClass, tblDrivers.DriverName,
tblPupDetails.PupStatus
FROM tblDrivers RIGHT JOIN (tblType RIGHT JOIN (tblPupDetails INNER JOIN
tblCustomers ON tblPupDetails.CustID = tblCustomers.CustID) ON
tblType.TypeID = tblPupDetails.Type) ON tblDrivers.DriverID =
tblPupDetails.DriverAllocated;

Into this:

Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String
Set dbMyDB = CurrentDb
strQName = "qryLHFull"
strQSQL = "SELECT * FROM qryLHVol WHERE DateOut =#" &
Forms![frmLinehaul]![SendDate] & "#"
Set qdMyQuery = DBEngine(0)(0).CreateQueryDef()
qdMyQuery.Name = strQName
qdMyQuery.SQL = strQSQL
DBEngine(0)(0).QueryDefs.Append qdMyQuery
qdMyQuery.Close
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview
End Sub

I have been using qryLHVol to create the Def as it already has the relative
joins and it works fine, am hoping someone could polish it up for me so I
can drag everything from tables, and minimise nesting.

I would like the above
strQSQL = "SELECT * FROM qryLHVol WHERE DateOut =#" &
Forms![frmLinehaul]![SendDate] & "#"

to look like this
strQSQL = "SELECT tblPupDetails.DateOut, tblPupDetails.PickUpNo,
tblPupDetails.DestState, tblCustomers.CustName, tblPupDetails.QTY,
tblType.TypeDesc, tblPupDetails.Weight, tblPupDetails.DG,
tblPupDetails.DGClass, tblPupDetails.DGSubClass, tblDrivers.DriverName,
tblPupDetails.PupStatus
FROM tblDrivers RIGHT JOIN (tblType RIGHT JOIN (tblPupDetails INNER JOIN
tblCustomers ON tblPupDetails.CustID = tblCustomers.CustID) ON
tblType.TypeID = tblPupDetails.Type) ON tblDrivers.DriverID =
tblPupDetails.DriverAllocated;
WHERE DateOut =#" & Forms![frmLinehaul]![SendDate] & "#"

I though it would be just a case of copying the SELECT query code from
qryLHVol and paste it straight in, DOH!, wrong......

Many thanks
Mark.