query coding

[hotrod1952 via AccessMonster.com]

I have a combo on a form which is a value list. 0,3, 4 are the options
available from the list. I have a query based on a table and the row of the
table that I need to query against has contains values 0,1,2,3 as data. What
I am trying to do is have the query return results when combo = 3 return all
data from table that is 3, When combo =0 return all data that is 0, And When
combo = 4 (I can change this in the list to something else) return all data
that is 3 and 0. I have the Combo and query set up and will work with 3 or 0
but not both and need an idea how to code the query to query for both 0 and 3.
Thanks

 

[Klatuu]

In the After Update event of the combo, build the Where condition of the query:

strSQL = "SELECT some fields FROM sometable WHERE "
Select Case Me.MyCombo
Case 0
strSQL = strSQL & "search field = 0;"
Case 3
strSQL = strSQL & "search field = 3;"
Case 4
strSQL = strSQL & "search field IN (0, 3);"
End Select

 

[hotrod1952 via AccessMonster.com]

Thanks for your help! Next question. How can I use the strSQL result in my
query? By having the form make a query? Now I have the formula in my query
for TABLE.ROW as =[FORMS]![FORM].[COMBO]

In the After Update event of the combo, build the Where condition of the query:

strSQL = "SELECT some fields FROM sometable WHERE "
Select Case Me.MyCombo
Case 0
strSQL = strSQL & "search field = 0;"
Case 3
strSQL = strSQL & "search field = 3;"
Case 4
strSQL = strSQL & "search field IN (0, 3);"
End Select

I have a combo on a form which is a value list. 0,3, 4 are the options
available from the list. I have a query based on a table and the row of the

[quoted text clipped - 5 lines]

but not both and need an idea how to code the query to query for both 0 and 3.
Thanks

 

[Klatuu]

That will not work. The value of the combo will be 0, 3, or 4.
Can you provide more detail, please.
How are you contstructing the rest of the query?
What do you want to do with it?

--
Dave Hargis, Microsoft Access MVP

Thanks for your help! Next question. How can I use the strSQL result in my
query? By having the form make a query? Now I have the formula in my query
for TABLE.ROW as =[FORMS]![FORM].[COMBO]

In the After Update event of the combo, build the Where condition of the query:

strSQL = "SELECT some fields FROM sometable WHERE "
Select Case Me.MyCombo
Case 0
strSQL = strSQL & "search field = 0;"
Case 3
strSQL = strSQL & "search field = 3;"
Case 4
strSQL = strSQL & "search field IN (0, 3);"
End Select

I have a combo on a form which is a value list. 0,3, 4 are the options
available from the list. I have a query based on a table and the row of the

[quoted text clipped - 5 lines]

but not both and need an idea how to code the query to query for both 0 and 3.
Thanks

 

[hotrod1952 via AccessMonster.com]

My current SQL for the query is :

SELECT MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, Last(MAINTPMLabGrtDue.WOLaborDate) AS LastOfWOLaborDate,
MAINTPMLabGrtDue.DateRequired
FROM MAINTPMLabGrtDue
GROUP BY MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, MAINTPMLabGrtDue.DateRequired
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=[forms]![MAINTPMCompPercent].[combo4]));

MAINTPMLabGrtDue.WOTypeNo is the data field I am trying to query against.

That will not work. The value of the combo will be 0, 3, or 4.
Can you provide more detail, please.
How are you contstructing the rest of the query?
What do you want to do with it?

Thanks for your help! Next question. How can I use the strSQL result in my
query? By having the form make a query? Now I have the formula in my query

[quoted text clipped - 16 lines]

 

[hotrod1952 via AccessMonster.com]

I have an idea on how to fix this. I am going to change my query to :
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=3 or 0));
and make a query from this query and set the WOTypeNo = Forms!.............

My current SQL for the query is :

SELECT MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, Last(MAINTPMLabGrtDue.WOLaborDate) AS LastOfWOLaborDate,
MAINTPMLabGrtDue.DateRequired
FROM MAINTPMLabGrtDue
GROUP BY MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, MAINTPMLabGrtDue.DateRequired
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=[forms]![MAINTPMCompPercent].[combo4]));

MAINTPMLabGrtDue.WOTypeNo is the data field I am trying to query against.

That will not work. The value of the combo will be 0, 3, or 4.
Can you provide more detail, please.

[quoted text clipped - 6 lines]

 

[Klatuu]

I don't know if that will work.

Is the query a stored query, or are you building the query dynamically?
--
Dave Hargis, Microsoft Access MVP

I have an idea on how to fix this. I am going to change my query to :
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=3 or 0));
and make a query from this query and set the WOTypeNo = Forms!.............

My current SQL for the query is :

SELECT MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, Last(MAINTPMLabGrtDue.WOLaborDate) AS LastOfWOLaborDate,
MAINTPMLabGrtDue.DateRequired
FROM MAINTPMLabGrtDue
GROUP BY MAINTPMLabGrtDue.WOTypeNo, MAINTPMLabGrtDue.WOID, MAINTPMLabGrtDue.
DateCreated, MAINTPMLabGrtDue.DateRequired
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=[forms]![MAINTPMCompPercent].[combo4]));

MAINTPMLabGrtDue.WOTypeNo is the data field I am trying to query against.

That will not work. The value of the combo will be 0, 3, or 4.
Can you provide more detail, please.

[quoted text clipped - 6 lines]

but not both and need an idea how to code the query to query for both 0 and 3.
Thanks

 

[hotrod1952 via AccessMonster.com]

Stored query.

I don't know if that will work.

Is the query a stored query, or are you building the query dynamically?

I have an idea on how to fix this. I am going to change my query to :
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=3 or 0));

[quoted text clipped - 17 lines]

 

[Klatuu]

What is the query for?
With a stored query, it is a bit of work to modify a stored query on the
fly. Not hard, just a bit of code to get it right.

Now, if this is for a report, there is a better way. That would be to leave
the Having off the stored query and do the filtering using the Where argument
of the OpenReport method.
--
Dave Hargis, Microsoft Access MVP

Stored query.

I don't know if that will work.

Is the query a stored query, or are you building the query dynamically?

I have an idea on how to fix this. I am going to change my query to :
HAVING (((MAINTPMLabGrtDue.WOTypeNo)=3 or 0));

[quoted text clipped - 17 lines]

but not both and need an idea how to code the query to query for both 0 and 3.
Thanks

 

[hotrod1952 via AccessMonster.com]

I use this query to populate data for 2 (and possibly another yet to be
developed) forms. My query of the query idea did get me what I needed to make
this work. Thanks for your insight.

What is the query for?
With a stored query, it is a bit of work to modify a stored query on the
fly. Not hard, just a bit of code to get it right.

Now, if this is for a report, there is a better way. That would be to leave
the Having off the stored query and do the filtering using the Where argument
of the OpenReport method.

Stored query.

[quoted text clipped - 6 lines]

 

[Klatuu]

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

I use this query to populate data for 2 (and possibly another yet to be
developed) forms. My query of the query idea did get me what I needed to make
this work. Thanks for your insight.

What is the query for?
With a stored query, it is a bit of work to modify a stored query on the
fly. Not hard, just a bit of code to get it right.

Now, if this is for a report, there is a better way. That would be to leave
the Having off the stored query and do the filtering using the Where argument
of the OpenReport method.

Stored query.

[quoted text clipped - 6 lines]

but not both and need an idea how to code the query to query for both 0 and 3.
Thanks