Alias / Dialog Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dialog box to run a query. I'm having trouble with an expression in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
 
Flute does not exist until after the query is executed. So, if you want to
apply criteria to it, do it in a 2nd query.

The real problem exists from the fact that you have 3 fields where there
should be only one. If you normalize the data, you will not have to go to
these lengths to do such simple tasks.


--
Steve Clark, Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 
This should work as your criteria. It will return all records if cbFlute is
Null and only records that match the value if cbFlute if it has a value.

LIKE IIF([Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null,
"*", [Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute])

This code has no effect on Trombones.
 
Thanks. It does appear as if things aren't normalized but in fact, they are.

1Flute, 2Flute, 3Flute are relative to the flutes of a corrugated box. One
box may have up to 3 flutes. While it would appear that this is a call for a
one-to-many table relationship doing so would actually create many more
challenges. Without going into all of the details I found it much easier to
simply use the 3 fields listed.

--
www.Marzetti.com


S.Clark said:
Flute does not exist until after the query is executed. So, if you want to
apply criteria to it, do it in a 2nd query.

The real problem exists from the fact that you have 3 fields where there
should be only one. If you normalize the data, you will not have to go to
these lengths to do such simple tasks.


--
Steve Clark, Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

JohnLute said:
I have a dialog box to run a query. I'm having trouble with an expression
in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
Click to expand...
Click to expand...
 
Hi, Klatuu! Trombones! Too funny!

Actually these are in reference to the flutes of a corrugated box. Please
see my response to S.Clark in this thread.

I gave the criteria a whirl however it returns all records. It's not
filtering anything out. Other than that - it works fine!

Is it because perhaps what S.Clark said: that "Flute" doesn't exist until
after the query is executed...?

--
www.Marzetti.com


Klatuu said:
This should work as your criteria. It will return all records if cbFlute is
Null and only records that match the value if cbFlute if it has a value.

LIKE IIF([Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null,
"*", [Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute])

This code has no effect on Trombones.

JohnLute said:
I have a dialog box to run a query. I'm having trouble with an expression in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
Click to expand...
Click to expand...
 
WHOOOPS! My apologies! This DOES work. I made a slight error in transcribing.

THANKS!!!

--
www.Marzetti.com


JohnLute said:
Hi, Klatuu! Trombones! Too funny!

Actually these are in reference to the flutes of a corrugated box. Please
see my response to S.Clark in this thread.

I gave the criteria a whirl however it returns all records. It's not
filtering anything out. Other than that - it works fine!

Is it because perhaps what S.Clark said: that "Flute" doesn't exist until
after the query is executed...?

--
www.Marzetti.com


Klatuu said:
This should work as your criteria. It will return all records if cbFlute is
Null and only records that match the value if cbFlute if it has a value.

LIKE IIF([Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null,
"*", [Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute])

This code has no effect on Trombones.

JohnLute said:
I have a dialog box to run a query. I'm having trouble with an expression in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Hi,


The alias cannot be used in the WHERE clause, but you can cut and paste what
it aliased (which is what does the query designer if you use it correctly)
in the where clause:


SELECT a+b AS c ...
WHERE a+b > 0


but not


SELECT a+b AS c ...
WHERE c>0


Hoping it may help,
Vanderghast, Access MVP


JohnLute said:
Hi, Klatuu! Trombones! Too funny!

Actually these are in reference to the flutes of a corrugated box. Please
see my response to S.Clark in this thread.

I gave the criteria a whirl however it returns all records. It's not
filtering anything out. Other than that - it works fine!

Is it because perhaps what S.Clark said: that "Flute" doesn't exist until
after the query is executed...?

--
www.Marzetti.com


Klatuu said:
This should work as your criteria. It will return all records if cbFlute
is
Null and only records that match the value if cbFlute if it has a value.

LIKE IIF([Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is
Null,
"*", [Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute])

This code has no effect on Trombones.

JohnLute said:
I have a dialog box to run a query. I'm having trouble with an
expression in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Michel!

--
www.Marzetti.com


Michel Walsh said:
Hi,


The alias cannot be used in the WHERE clause, but you can cut and paste what
it aliased (which is what does the query designer if you use it correctly)
in the where clause:


SELECT a+b AS c ...
WHERE a+b > 0


but not


SELECT a+b AS c ...
WHERE c>0


Hoping it may help,
Vanderghast, Access MVP


JohnLute said:
Hi, Klatuu! Trombones! Too funny!

Actually these are in reference to the flutes of a corrugated box. Please
see my response to S.Clark in this thread.

I gave the criteria a whirl however it returns all records. It's not
filtering anything out. Other than that - it works fine!

Is it because perhaps what S.Clark said: that "Flute" doesn't exist until
after the query is executed...?

--
www.Marzetti.com


Klatuu said:
This should work as your criteria. It will return all records if cbFlute
is
Null and only records that match the value if cbFlute if it has a value.

LIKE IIF([Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is
Null,
"*", [Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute])

This code has no effect on Trombones.

:

I have a dialog box to run a query. I'm having trouble with an
expression in
the query. This is the expression:

Flute: [1FluteType] & "" & [2FluteType] & "" & [3FluteType]

In've tried this criteria:
([Flute])=[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Or
[Forms]![frmQueryPKCGPhysicalMaterialAttributes]![cbFlute] Is Null

This criteria isn't working.

How can I create a criteria for this expression? Is this possible?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top