Got queries. Now have to make 'em dynamic 2

[petelyons]

Hi,

I've got my queries designed just the way they need to be. Now I have to make 'em dynamic based on user selections from a critieria selection form. I have some simple examples books, like for filtering, but it's not enough information.

I understand that I will have to embed my SQL into the VBE coding environment using strSql's etc.

Can anyone demonstrate or provide a sample for this, especially how to code dynamic WHERE statements. The queries are used to power my reports.

Thanks in advance!

 

[Aivars]

Hi!

You can write SQL clause and then run it or make query based on this SQL text.

Example:

dim strSQL as string
dim strCriteria as string

strSQL="Select * From MyTable "
strCriteria="NumField =" & me!CriteriaNumericItem & _
" And TextField = '" & me!CriteriaTextItem & "' " & _
" And DateField > #" & me!CriteriaTextItem & "#"
strCriteria = "Where " & strCriteria
strSQL=strSQL & strCriteria & ";"

me.recordsource=strSQL 'Or
docmd.runsql strSQL

'Also you can create new query based on strSQL and take later in other operations.

call NewQuery(strSQL,"MyNewQueryName&quot

Public Function NewQuery(strSQL As String, strQueryName As String)
Dim dbs As Database, myQueryDefine As QueryDef, myQueryName As String, a

myQueryName = strQueryName

' Return reference to current database.
Set dbs = CurrentDb

' Delete old query
On Error GoTo Err_NewQuery
dbs.QueryDefs.Delete myQueryName

' Create new query.
Set myQueryDefine = dbs.CreateQueryDef(myQueryName, strSQL)
Set dbs = Nothing
Exit Function

Err_NewQuery:
Set myQueryDefine = dbs.CreateQueryDef(myQueryName, strSQL)
Set dbs = Nothing
End Function

Aivars

 

[petelyons]

Aivars,

Many thanks! I will try this stuff out and see how I do. Thanks again!
-Pete

 

[Rohdem]

Are you using the Access query design grid to make your queries, or are you using straight SQL? I'm not sure you need to use VB here. If you are using the the design grid, you can reference text controls on a form using the syntax:

forms!formname!controlname

For example, if you had a control on your form called StartDate and a control called EndDate and you wanted your query to pull all records between those dates, you would type the following into the criteria box for the date: (in this example your form name would be 'ReportForm')

between Forms!ReportForm!StartDate and Forms!ReportForm!EndDate

This seems much easier to me, but I might me missing the point (it's been known to happen ;-)) Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[petelyons]

Thanks Mike, I will give this a shot too ;-)
-Pete