[AccessD] It's About Time I Learned This

OK, here's a sample of the SQL that I create using this technique:

strSQL = "SELECT tblUSRHeader.fldLicenseeID,
tblUSRHeader.fldUSRHeaderReportingYear, " _
    & "tblUSRHeader.fldUSRHeaderReportingQuarter,
tblUSRHeader.fldUSRHeaderAdjustment, " _
    & "tblUSRHeader.fldUSRHeaderShipmentQuarter,
tblUSRHeader.fldUSRHeaderInvoiceNumber, " _
    & "tblUSRHeader.fldUSRHeaderShipmentYear,
Sum(tblUSRDetail.fldUSRDetailQuantity) AS " _
    & "SumOffldUSRDetailQuantity, tblUSRHeader.fldUSRHeaderDeleteFlag, " _
    & "tblUSRDetail.fldUSRDetailDeleteFlag, tblUSRDetail.fldUSRHeaderID, " _
    & "tblUSRHeader.fldUSRHeaderInvoiceNumber FROM tblUSRHeader INNER JOIN
tblUSRDetail ON " _
    & "tblUSRHeader.fldUSRHeaderID = tblUSRDetail.fldUSRHeaderID GROUP BY "
_
    & "tblUSRHeader.fldLicenseeID, tblUSRHeader.fldUSRHeaderReportingYear, "
_
    & "tblUSRHeader.fldUSRHeaderReportingQuarter,
tblUSRHeader.fldUSRHeaderAdjustment, " _
    & "tblUSRHeader.fldUSRHeaderShipmentQuarter,
tblUSRHeader.fldUSRHeaderInvoiceNumber, " _
    & "tblUSRHeader.fldUSRHeaderShipmentYear,
tblUSRHeader.fldUSRHeaderDeleteFlag, " _
    & "tblUSRDetail.fldUSRDetailDeleteFlag, tblUSRDetail.fldUSRHeaderID, " _
    & "tblUSRHeader.fldUSRHeaderInvoiceNumber HAVING
(((tblUSRHeader.fldLicenseeID) = " _
    & Me.cboInvoiceReportLicensee.Column(0) & " ) And
((tblUSRHeader.fldUSRHeaderShipmentQuarter) " _
    & "<= " & Me.cboInvoiceReportQuarter & ") And
((tblUSRHeader.fldUSRHeaderShipmentYear) = " _
    & Me.cboInvoiceReportYear & ") And
((tblUSRHeader.fldUSRHeaderDeleteFlag) = False) And " _
    & "((tblUSRDetail.fldUSRDetailDeleteFlag) = False)) ORDER BY
tblUSRHeader.fldLicenseeID, " _
    & "tblUSRHeader.fldUSRHeaderShipmentQuarter,
tblUSRHeader.fldUSRHeaderAdjustment DESC;"

Set rsDetail = db.OpenRecordset(strSQL)

Works really well, but it's not real convenient to work with - especially
during development when the query needs tweaking.  I thought there was some
way to inject the parameteres right into a stored query. Maybe not.


Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, October 08, 2009 11:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] It's About Time I Learned This

>   strSQL = "Select Cast(chequenum as int) from tblCheques where PKID" 
> &
   PKID

Rocky, I think you are missing the point here.  The above line is where you
would take YOUR parameters from your combo boxes, list selections,  whatever
and build the above line to suit your needs.  The rest of the code
before/after is  what updates the query automatically  for you. No need to
cut-n-paste etc.

I do similar  things tons  of  times.

Ie, grab the current sql from the query using QueryDef.  Amend it based on
the  selection forms that my users are presented with (typically building a
filter for  viewing only the records they want to see), and then write it
back  to the query which the form is based on and requery the  form.

As a developer, this is all left to the user to change  the underlying query
from a simple  selection form.  Same for reports etc.

Max