Dynamic Querying

[Gaff]

Hi ,
Does anyone know if there are any good websites out there that deal with Dynamic Querying in Access. What i'm talking about is that a person can enter certain criteria and based on this a query is built and executed. I suppose a kind of SQL builder if you like. Along similar lines to the design view of queries in Access, except that i will be doing it through a VB front end. Thanks

 

[demoman]

I have not actually tried this from VB, but I expect it would work. Look at the help for the Access QueryDef object. It looks like you can change the SQL property through ODBC. This is basically just providing a different SQL statement to the query definition. I do this from within Access without any problem. You will need to add the Access object library to your VB project. The DAO library usually loads by default, but you may want to check it.

If you need help with the code for the Querydef, send me an e-mail and I will send you a sample.

Steve
SteveAndEdie1@attbi.com

 

[raskew]

You might try Steve Nyberg's site at

http://www.mile50.com/

His RFil and FFil applications may be close to what you're looking for.

 

[hsitz]

Steve Nyberg's utilities are general purpose, but may be too difficult for some end users.

I generally build custom query forms into my Access applications. The controls on these forms are used to dynamically build an SQL string that then serves as the recordset for a resulting form or report. Nothing about these forms is peculiar to Access; I assume they'd work equally well from VB. They're just not generaly purpose, they're fairly narrowly tailored to just the sorts of queries the customer needs to run.

It's a little complicated to explain here, but generally I use the method set forth around pp. 250-270 of Beginning Access 2000 VBA, a Wrox book by Smith and Sussman. I think their method involves setting the SQL property of an access query definition, but there's no reason it has to, and I sometimes just use the resulting SQL to get a recordset directly. If you don't need a general purpose query designer then this method would work pretty well from VB. -- Herb

 

[hsitz]

Here's a sample of the code I used in one recent query form. It gives the user the chance to query varying six or seven different criteria. This code builds the SQL string out of the settings on the form. It's helpful (should be obvious) to first experiment by constructing a number of representative actual queries. They'll give you a feel for what you need to incorporate into the query form. This code builds the SQL string out of the settings on the form:

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


Dim strWhere As String
Dim strHAVING As String
Dim strSQL2 As String

strSQL = "SELECT DISTINCT tblMTS.uniqueid, " & IIf(Me.cboCampaign > 0, "[Donor Contributions].FundrID,", "&quot _
& " sum([Donor Contributions].DonorAmount) as sumofdonoramount " _
& " FROM tblMTS LEFT JOIN [Donor Contributions] ON tblMTS.uniqueid = [Donor Contributions].PersonID "

If Not IsNull(Me.cboKind) Then
If Me.cboKind <> &quot;Non In Kind&quot; Then
strWhere = &quot; AND ([Donor Contributions].lngDonationtype = '&quot; & Me.cboKind & &quot;')&quot;
Else
strWhere = &quot; AND ([Donor Contributions].lngDonationtype <> 'In Kind')&quot;
End If
End If

If Me.cboCampaign > 0 Then
strHAVING = strHAVING & &quot; AND ([Donor Contributions].FundrID = &quot; & Me.cboCampaign & &quot&quot;
End If
If Not IsNull(Me.txtMoreThan) Then
strHAVING = strHAVING & &quot; AND (sum([Donor Contributions].DonorAmount) >= &quot; & Me.txtMoreThan & &quot&quot;
End If
If Not IsNull(Me.txtLessThan) Then
strHAVING = strHAVING & &quot; AND (sum([Donor Contributions].DonorAmount) <= &quot; & Me.txtLessThan & &quot&quot;
End If
If Not IsNull(Me.txtBeginDate) Then
strWhere = strWhere & &quot; AND ([Donor Contributions].DonorDate >= #&quot; & Me.txtBeginDate & &quot;#)&quot;
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & &quot; AND ([Donor Contributions].DonorDate <= #&quot; & Me.txtEndDate & &quot;#)&quot;
End If

If strWhere <> &quot;&quot; Then
strWhere = &quot; WHERE &quot; & Mid(strWhere, 6)
End If

If strHAVING <> &quot;&quot; Then
strHAVING = &quot; HAVING &quot; & Mid(strHAVING, 6)
End If

strSQL2 = &quot;GROUP BY tblMTS.uniqueid&quot; & IIf(Not IsNull(Me.cboCampaign), &quot;, [Donor Contributions].FundrID&quot;, &quot;&quot

strSQL = strSQL & strWhere & strSQL2 & strHAVING
-------------------------------------------------------