Name string and query criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sin - 18 Oct 2007 01:57 GMT

I have a form with a list box listing values "table 1", "table 2" .... and 2
combo box for year and month selection. The selected values (year and month)
in the combo are set as criteria to run a query.

While the query runs from the same data table, the selection in the list box
(table 1, table 2 ...) actually indicated a criteria for different column
depending on the selection. For e.g. I have a table of data with columns
name, state and poscode, "table 1" could represent a query for postcode
2000-2400 only, while "table 2" represents a query for state NSW only and
"table 3" represents all data etc. each string in the list box represent a
set query (as per name of the report indicated), and the user can simply
chose which report they want for the chosen period without going through the
selection list to select each critierias. Is it possible to set a
relationship between the string in the list box and the query value so that
the query can recognise the selected string as a criteria?

Hope this is clear.

Thanks in advance.

Reply to this Message

Pieter Wijnen - 18 Oct 2007 10:06 GMT

Not as such, but..

Create a Query MyQueryBase with the "fixed" criteria in place
Note: I Assume the listbox "criteria" to be in Column(1)

Then You Can Use

Dim Db As DAO.Database
Dim Qdf As DAO.QueryDef
Dim Pos As Long
Dim thS As String
Dim thW As String
Set Db = Access.CurrentDb
Set Qdef = Db.QueryDefs("MyQueryBase")
Pos = VBA.Instr(Qdef.SQL,"WHERE") + 5
thS = VBA.Left(Qdef.SQL,Pos)
thW = VBA.Mid(QDef.SQL, Pos)
Qdef.Close
Set Qdef = Db.QueryDefs("MyQuery")
thW = Me.ListBox1.Column(1) & thW ' Or a series of if's and/or a Select
Case
Qdef.SQL = thS & thW
Qdef.Close : Set Qdef = Nothing
Set Db = Nothing

HtH

Pieter

>I have a form with a list box listing values "table 1", "table 2" .... and
>2
[quoted text clipped - 21 lines]
>
> Thanks in advance.

Reply to this Message