QueryDef Help (Re-posted)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NoodNutt - 20 Apr 2008 06:44 GMT

Hi all

I originally posted this in the wrong NG..............DOH!

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.

Reply to this Message

Douglas J. Steele - 20 Apr 2008 13:19 GMT

It's not clear to me what you're trying to do. If all you're trying to do is
limit what appears on your report, you don't need to change the query it
uses: you can simply pass a Where condition when you open the report:

DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview, , _
DateOut =" & Format(Forms![frmLinehaul]![SendDate], "\#yyyy\-mm\-dd\#")

(Note that I've included the # delimiter in the format. What I have is far
more reliable than what you had, since yours will not work for users who
have their Short Date format set to dd/mm/yyyy)

If what you're trying to do is create a new query that's identical to
qryLHVol except that it has a Where clause that qryLHVol doesn't have, try

Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String

Set dbMyDB = CurrentDb
strQName = "qryLHFull"
strQSQL = dbMyDB.QueryDefs("qryLHVol").SQL & _
" WHERE DateOut = " & _
Format(Forms![frmLinehaul]![SendDate], "\#yyyy\-mm\-dd\#")
Set qdMyQuery = dbMyDb.CreateQueryDef(strQName, strQSQL)
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

End Sub

(No need for the Append to the QueryDefs collection: the CreateQueryDef
method does that for you)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi all
>
[quoted text clipped - 56 lines]
> Many thanks
> Mark.

Reply to this Message

NoodNutt - 20 Apr 2008 23:13 GMT

Thx for replying Doug

As I stated in my post I don't want to use the qryLHVol, although it works
fine and it already has all the relative joins in it.

I am trying to get away from using queries that just sit there doing nothing
until a report is required.

For some reason I am getting DB Recalcing already and I only have about 100
records (at present it is only a 1-2 second re-calc, I shudder to think what
it would be like when there is upto 10K). I figure if I eliminate nested
queries & only create/run them on the fly directly from the table for when
reports are needed hopefully it will prevent the DB from slowing down too
much. Keeping in mind that this DB is still on a stand-alone system and has
yet to be split or converted to a MDE and installed on a network.

I hope that sort of makes sence.

Regards
Mark.

Reply to this Message

NoodNutt - 24 Apr 2008 15:55 GMT

Hi Douglas

In response:

[It's not clear to me what you're trying to do]

Create a query containing all the relavent joins. (Not permanently nested).
The IT people (who are being pains as they have a major disliking for
Access) want me to minimize the dependancy on queries as much as possible
(in their case, use no queries).

[If all you're trying to do is limit what appears on your report]

The only limit in the query is the date
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
With the above in mind, I tried using your structure and modified it to
include the table information and not the query.SQL, but it halted on

strQSQL = dbMyDB.QueryDefs(strMySQL).SQL & _

Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String
Dim strMySQL As String

Set dbMyDB = CurrentDb
strQName = "qryLHFull"

strMySQL = "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;)"

strQSQL = dbMyDB.QueryDefs(strMySQL).SQL & _
" WHERE DateOut = " & _
Format(Forms![frmLinehaul]![SendDate], "\#dd\-mmm\-yy\#")
Set qdMyQuery = dbMyDB.CreateQueryDef(strQName, strQSQL)
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

I sincerely appreciate any assistance

Regards
Mark

Reply to this Message

NoodNutt - 25 Apr 2008 16:49 GMT

Hi again there Douglas

Let's assume I don't have any queires, and I want to create one via code,
which you have kindly done for me using qryLHVol.

Though in this instance qryLHVol doesn't exist, can you re-structure what
you have already done for me to run like this:

Private Sub CmdBtnFull_Click()
Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String
Set dbMyDB = CurrentDb
strQName = "qryLHFull"
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 tblPupDetails.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

I tried this version but it halted on:

DBEngine(0)(0).QueryDefs.Append qdMyQuery

Many Many Many Thx (does this smack of desperation, Overkill)

TIA
Regards
Mark.

Reply to this Message

Douglas J. Steele - 26 Apr 2008 22:17 GMT

As I said previously, you do not need to use the Append method to get the
QueryDef into the QueryDefs collection.

(Sorry for taking so long to reply: I've been on the road for the past two
weeks)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi again there Douglas
>
[quoted text clipped - 37 lines]
> Regards
> Mark.

Reply to this Message

NoodNutt - 27 Apr 2008 09:07 GMT

Hi Douglas

I certainly understand "On the road", just came off Interstate driving
myself.

Can we use your original response:

------------------
Snippet -------------------------------------------------

(Note that I've included the # delimiter in the format. What I have is far
more reliable than what you had, since yours will not work for users who
have their Short Date format set to dd/mm/yyyy)

If what you're trying to do is create a new query that's identical to
qryLHVol except that it has a Where clause that qryLHVol doesn't have, try

Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String

Set dbMyDB = CurrentDb
strQName = "qryLHFull"
strQSQL = dbMyDB.QueryDefs("qryLHVol").SQL & _
" WHERE DateOut = " & _
Format(Forms![frmLinehaul]![SendDate], "\#yyyy\-mm\-dd\#")
Set qdMyQuery = dbMyDb.CreateQueryDef(strQName, strQSQL)
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

End Sub

(No need for the Append to the QueryDefs collection: the CreateQueryDef
method does that for you)

--------------------------------------------------------------------------------------

then reconstruct it without the reference to "qryLHVOL" (as in this scenario
qryLHVol doesn't exist), so it grabs all info directly from tables
(including the joins).

As always

Much appreciated
Mark.

Reply to this Message

Douglas J. Steele - 27 Apr 2008 12:35 GMT

You had it already: you just need to remove the Append statement (and you
should refer to your instantiation of the database, dbMyDB, rather than to
DBEngine(0)(0))

Private Sub CmdBtnFull_Click()
Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String
Set dbMyDB = CurrentDb
strQName = "qryLHFull"
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 tblPupDetails.DateOut = #" & Forms![frmLinehaul]![SendDate]
& "#"""

Set qdMyQuery = dbMyDB.CreateQueryDef()
qdMyQuery.Name = strQName
qdMyQuery.SQL = strQSQL
qdMyQuery.Close
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

Of course, you can simplify the end bit by passing the name of the query and
its SQL to the CreateQueryDef method:

Set qdMyQuery = dbMyDb.CreateQueryDef(strQName, strQSQL)
qdMyQuery.Close
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

(in actual fact, the line with .Close isn't necessary either, but at least
it won't cause an error.)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Douglas
>
[quoted text clipped - 39 lines]
> Much appreciated
> Mark.

Reply to this Message

NoodNutt - 28 Apr 2008 00:06 GMT

Hi Douglas

Sorry to drag this out

It halted on this

Set qdMyQuery = dbMyDB.CreateQueryDef(strQName, strQSQL)

But I managed to get it to step through like this:

Dim dbMyDB As Database, qdMyQuery As QueryDef
Dim strQSQL As String, strQName As String
Set dbMyDB = CurrentDb
strQName = "qryLHFull"

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 tblPupDetails.DateOut = #" & Forms![frmLinehaul]![SendDate]
& "#"""

Set qdMyQuery = dbMyDB.CreateQueryDef()
qdMyQuery.Name = strQName
qdMyQuery.SQL = strQSQL
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

Although, when it tried to open the report there was nothing, as the report
halted after it triggered a number filter code when it was looking for the
control that wasn't there.

What I noticed was that the query doesn't show it the collection in the left
pane where all the others are. Which it was, when I used the original code
with the qryLHVol in it.

Any thoughts.

Mark.

Reply to this Message

Douglas J. Steele - 28 Apr 2008 00:50 GMT

The SQL you're assigning to strQSQL is invalid: you've got incorrect quotes
at the end. The last line should just be

"WHERE tblPupDetails.DateOut = #" & Forms![frmLinehaul]![SendDate] & "#"

or, perhaps better,

"WHERE tblPupDetails.DateOut = " & _
Format(Forms![frmLinehaul]![SendDate], "\#yyyy\-mm\-dd\#")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Douglas
>
[quoted text clipped - 40 lines]
>
> Mark.

Reply to this Message

NoodNutt - 29 Apr 2008 02:51 GMT

I think where almost there Douglas.

The Code steps through now, upto, but not including the final stage of
opening the report, it halts there.

I think may have something to do with when creating the Query in the
queryDef Collection, it doesn't appear in the Object Collection Pane, as
such the report has no RecordSource to bind to.

Unless I have to set the reports RecordSource to = something else other than
"qryLHFull", or do I need something in front to tell the report where to
look for the RecordSource.

Thx for your patience Douglas
Regards
Mark.

Reply to this Message

Douglas J. Steele - 29 Apr 2008 22:11 GMT

See whether this makes a difference:

Set qdMyQuery = dbMyDB.CreateQueryDef(strQName, strQSQL)
qdMyQuery.Close
DoCmd.OpenReport "rptLinehaulVolumes", acViewPreview

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I think where almost there Douglas.
>
[quoted text clipped - 12 lines]
> Regards
> Mark.

Reply to this Message

NoodNutt - 30 Apr 2008 01:51 GMT

Sorry Douglas

That one didn't work the first time you suggested it.....LOL.

Ah well! Back to the drawing board.

Appreciate all your time efforts.

Regards
Mark.

Reply to this Message