Run-Time Query????

  • Thread starter Thread starter Dragon
  • Start date Start date
D

Dragon

Hi

I have an Access 2000 table on which I am trying to create a query. I
created a form with two Option Groups based on Field2 and Field4. Option1
(for Field2) allows you to select one of the three products or an option
button for All products. Similarly, Option2 (for Field4) has three
selections, Yes, No, All.

Initially I was thinking about creating a query for each choice combination.
But that mean I will be creating lots of queries which will have everything
same except the criteria. Is their an easier way to create a query to
simplify the database? May be at run time? Thank you.

Table1

Field1 Field2 Field3 Field4
xxxxxx Prod1 xxxxxx Yes
xxxxxx Prod2 xxxxxx Yes
xxxxxx Prod3 xxxxxx No
xxxxxx Prod3 xxxxxx Yes
xxxxxx Prod1 xxxxxx No
 
Try using a WHERE clause (criteria) where the value in the appropriate field
is referred to. I don't have any examples of where the value of the option
box of the open form is used so can't tell you have to use it.

Instead, because I tend to close the form and because the value is needed
elsewhere a global value is set in the AfterUpdate event. The query then
has the critera:
=fnGetField2Value()

The query first checks to see if any value was set. If not a default value
is used.

Good luck.
 
-----Original Message-----
Hi

I have an Access 2000 table on which I am trying to create a query. I
created a form with two Option Groups based on Field2 and Field4. Option1
(for Field2) allows you to select one of the three products or an option
button for All products. Similarly, Option2 (for Field4) has three
selections, Yes, No, All.

Initially I was thinking about creating a query for each choice combination.
But that mean I will be creating lots of queries which will have everything
same except the criteria. Is their an easier way to create a query to
simplify the database? May be at run time? Thank you.

Table1

Field1 Field2 Field3 Field4
xxxxxx Prod1 xxxxxx Yes
xxxxxx Prod2 xxxxxx Yes
xxxxxx Prod3 xxxxxx No
xxxxxx Prod3 xxxxxx Yes
xxxxxx Prod1 xxxxxx No


.
Click to expand...
I would create the three queries using the QBE grid then
copy the SQL to the code editor put them in a select case
statement or an if .... then statement and select the
proper one based on the criteria chosen by the user.
 
Thank you for your reply.

Unfortunately I do not think having only three queries will help as users
can select any number of options. For example following are a few
combinations.

Prod1, Prod2, Yes
Prod3, No
Prod2, Prod3, All
All, All

I know Select and/or If-Then-Else might work, but I am trying to reduce the
number of queries I have to create. It seem if I have 4 choices in Option
Group1 and 3 in Group2, then I may end up creating over 20 queries for
different combinations.

Thank you.
 
Hi,

Leaving the form open is not an issue for me as I don't need to close it
immidiately and I can even leave it up to the user to create additional data
sets or simply close the form. My main concern is to reduce the number of
queries.

Thank you.
 
Then your functions would return the proper value from the option.


fnName = Forms.Item("formname").Controls.Item("Option1").Value

1. You might not want to hardcode the form and item names depending on your
coding style
2. Your syntax style might shorten the statement above to as little as:
fnName = Forms("formname").Controls("Option1")
3. The value returned might need to be interpreted. Perhaps the value is
"Prod1" but the query needs 1.


Here is a query where fnGetReportDate() is a Public function.
SELECT tblA.Store, tblA.BeginDate, tblA.EndingDate, tblA.Manager
FROM tblA
WHERE (tblA.BeginDate <= fnGetReportDate()) AND (tblA.EndingDate >=
fnGetReportDate())
ORDER BY tblA.Store, tblA.BeginDate, tblA.EndingDate;
 
Back
Top