Where Condition

[mithani]

Hi everyone,

Can anyone please tell me why where condition is not fetching any record?

SELECT DIE_NUMBER, DIE_DESCRIPTION, SECTIONTYPE FROM EXTRUSION WHERE ((Extrusion.SECTIONTYPE)=(FORMS!Reference!SectionType)) ORDER BY DIE_DESCRIPTION;

Thanks for your help.

Mithani

 

[mithani]

Hi Everyone,

To make it more clarify. Both text boxes "SectionType" & "Die_no" are in subform. So first I select type of section from "SectionType" and than select my die number from Die_number. The above select statement written on Die_no. Since both text boxes are in sub form so I changed my select statement as:

SELECT DIE_NUMBER, DIE_DESCRIPTION, SECTIONTYPE FROM EXTRUSION WHERE extrusion.SectionType=txtSectionType ORDER BY DIE_DESCRIPTION;

It is fetching the correct data only first time but if I go back and select another section type, data wont be xhanged. That will be same that what I selected first time.

Any idea? Thanks

Mithani

 

[zaqyu]

Hi,

Is txtsection is of text data type? if it is then the SQL should be like this:

"SELECT DIE_NUMBER, DIE_DESCRIPTION, SECTIONTYPE FROM EXTRUSION WHERE extrusion.SectionType='" & [txtSectionType] & "' ORDER BY DIE_DESCRIPTION; "

 

[Steve R.]

I am not an SQL expert, but you may want to try:

SELECT DIE_NUMBER, DIE_DESCRIPTION, SECTIONTYPE FROM EXTRUSION WHERE [SECTIONTYPE] = FORMS!Reference.SectionType ORDER BY DIE_DESCRIPTION;

I assume that EXTRUSION is a table or query. I also assume that SectionType is a control on the form REFERENCE. When referring to a control you use the ".", not the "!" symbol. The "[]" may be optional when referring to a fieldname without spaces.

I cross posted with Zaqyu. He is correct if you are using your SQL statement in a variable. My example is for the SQL being a stand alone statement.

 

[mithani]

Deat Zaqyu & Ortaisa,

Thanks a lot.

Zaqyu,

You are right, sectionTyp is text but your example not fetching any record.

Ortaisa,
As i mentioned above, both text boxes are on Reference Subform, so If i give form reference than, does not fetch any record but if I give without any form reference than works but only first time.

Please see the attached JPGs. When I select SectionType as "Sub Head", you can see Die No shows "Sub Head". But If I go back and change to "Transom", but still die No shows Sub head( as selected first time).

Any Idea?

mithani

 

[mithani]

Hi,

Please see attached my DB. Please check and see where I am wrong.

Thanks

mithani

 

[pbaldy]

For starters, the textbox is on the subform, not the form, so the syntax to refer to it is different. Good reference here:

http://www.mvps.org/access/forms/frm0031.htm

Second, if you want the second combo to change its selections based on the item selected in the first, you'd have to requery it. Search here for cascading combos, which is what you looking for.

 

[mithani]

Dear Pbaldy,

I did the requery but still same problem.

Both combo are on subform so I don't think I have to give any form reference. Am I right?

I spent hours to rectify this problem but no solution.

did you see my attached DB?

Thanks

Mithani

 

[zaqyu]

Here's the corrected version that addresses your need

 

[mithani]

Dear Zaqyu

You are great!

thanks

mithani