If statement question

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have a field [UNIT_MEASURE] in a query that I have this in the
criteria

Like "W" Or "V" Or "P"

this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria

IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)

How do I get this field to lookup these criteria's. Do I do it this
way or

=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))

I would like this field [TOT_UOM] to show only the W's that are under
30000, the V's under 2000 and the P's under 40. Is this correct, if so
it's not pulling anything in the query.

Ryan
 
Change the WHERE clause in your SQL statement to look like:

WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )

When you look at this in the design view, it should make sense to you.

HTH
Dale
 
Hi Ryan,

Maybe this is what you want.

Open NotePad (or your favorite text editor) and paste in the following SQL:

SELECT TheTable.UNIT_MEASURE, IIf([UNIT_MEASURE]="p" And
[TOT_UOM]<40,[tot_uom],IIf([UNIT_MEASURE]="w" And
[TOT_UOM]<30000,[tot_uom],IIf([UNIT_MEASURE]="v" And
[TOT_UOM]<2000,[tot_uom],0))) AS TRY
FROM TheTable
WHERE (((TheTable.UNIT_MEASURE)="w" Or (TheTable.UNIT_MEASURE)="v" Or
(TheTable.UNIT_MEASURE)="p") AND ((IIf([UNIT_MEASURE]="p" And
[TOT_UOM]<40,[tot_uom],IIf([UNIT_MEASURE]="w" And
[TOT_UOM]<30000,[tot_uom],IIf([UNIT_MEASURE]="v" And
[TOT_UOM]<2000,[tot_uom],0))))>0));


Use the replace command and replace "TheTable" with the name of your table.
Then create a new query, switch to SQL view and paste in the SQL that you
just changed.

Execute the query.

HTH
 
Thanks, but I get a Call error, whatever that means.


Change the WHERE clause in your SQL statement to look like:

WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )

When you look at this in the design view, it should make sense to you.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[email protected] said:
I have a field [UNIT_MEASURE] in a query that I have this in the
criteria
Click to expand...
Like "W" Or "V" Or "P"
Click to expand...
this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria
IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)
Click to expand...

How do I get this field to lookup these criteria's. Do I do it this
way or
=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))
Click to expand...

I would like this field [TOT_UOM] to show only the W's that are under
30000, the V's under 2000 and the P's under 40. Is this correct, if so
it's not pulling anything in the query.
Click to expand...
Ryan
Click to expand...
Click to expand...
 
Can you post the entire SQL statement?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



[email protected] said:
Thanks, but I get a Call error, whatever that means.


Change the WHERE clause in your SQL statement to look like:

WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )

When you look at this in the design view, it should make sense to you.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[email protected] said:
I have a field [UNIT_MEASURE] in a query that I have this in the
criteria
Click to expand...
Like "W" Or "V" Or "P"
Click to expand...
this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria
IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)
Click to expand...

How do I get this field to lookup these criteria's. Do I do it this
way or
=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))
Click to expand...

I would like this field [TOT_UOM] to show only the W's that are under
30000, the V's under 2000 and the P's under 40. Is this correct, if so
it's not pulling anything in the query.
Click to expand...
Ryan
Click to expand...
Click to expand...
Click to expand...
 
Here is the SQL.

SELECT [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
FROM [Qry(3)POreceivingswithWIMS]
GROUP BY [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
HAVING ((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="W") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<30000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="V") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<2000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="P") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<40));












Can you post the entire SQL statement?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[email protected] said:
Thanks, but I get a Call error, whatever that means.
Click to expand...
Change the WHERE clause in your SQL statement to look like:
WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )
When you look at this in the design view, it should make sense to you.
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have a field [UNIT_MEASURE] in a query that I have this in the
criteria
Like "W" Or "V" Or "P"
this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria
IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)
How do I get this field to lookup these criteria's. Do I do it this
way or
=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))
I would like this field [TOT_UOM] to show only the W's that are under
30000, the V's under 2000 and the P's under 40. Is this correct, if so
it's not pulling anything in the query.
Ryan
Click to expand...
Click to expand...
Click to expand...
 
Hi Ryan

Does your query work without the HAVING clause? I suspect it might be
balking at "NAME" which is a reserved word.

Try enclosing it in brackets -
.[NAME]

Also, I may be missing something here, but I can't see why you are using a
GROUP BY query which groups by *every* field. Would it not serve your
purpose to remove the entire GROUP BY clause and change HAVING to WHERE?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is the SQL.

SELECT [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
FROM [Qry(3)POreceivingswithWIMS]
GROUP BY [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
HAVING ((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="W") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<30000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="V") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<2000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="P") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<40));












Can you post the entire SQL statement?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[email protected] said:
Thanks, but I get a Call error, whatever that means.
Click to expand...
Change the WHERE clause in your SQL statement to look like:
Click to expand...
WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )
Click to expand...
When you look at this in the design view, it should make sense to
you.
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
:
I have a field [UNIT_MEASURE] in a query that I have this in the
criteria
Click to expand...
Like "W" Or "V" Or "P"
Click to expand...
this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria
IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)
Click to expand...

How do I get this field to lookup these criteria's. Do I do it this
way or
=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))
Click to expand...

I would like this field [TOT_UOM] to show only the W's that are
under
30000, the V's under 2000 and the P's under 40. Is this correct, if
so
it's not pulling anything in the query.
Click to expand...
Ryan
Click to expand...
Click to expand...
Click to expand...
 
I put [NAME] in brackets and deleted the having W, V, P part and
rewrote it in there and it ran fine. So I don't know what happened, it
might be that my database that I was connecting to had a lot of users
on it or something. Thanks!!


Hi Ryan

Does your query work without the HAVING clause? I suspect it might be
balking at "NAME" which is a reserved word.

Try enclosing it in brackets -
.[NAME]

Also, I may be missing something here, but I can't see why you are using a
GROUP BY query which groups by *every* field. Would it not serve your
purpose to remove the entire GROUP BY clause and change HAVING to WHERE?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Here is the SQL.
Click to expand...
SELECT [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
FROM [Qry(3)POreceivingswithWIMS]
GROUP BY [Qry(3)POreceivingswithWIMS].CORP,
[Qry(3)POreceivingswithWIMS].DIVISION,
[Qry(3)POreceivingswithWIMS].FACILITY,
[Qry(3)POreceivingswithWIMS].DST_CNTR,
[Qry(3)POreceivingswithWIMS].PO_NUM,
[Qry(3)POreceivingswithWIMS].VEND_NUM,
[Qry(3)POreceivingswithWIMS].NAME,
[Qry(3)POreceivingswithWIMS].WIMS_SUB_VEND,
[Qry(3)POreceivingswithWIMS].SUB_VEND_DESC,
[Qry(3)POreceivingswithWIMS].ALLOC_VEND_NUM,
[Qry(3)POreceivingswithWIMS].ALLOC_WIMS_VEND,
[Qry(3)POreceivingswithWIMS].UNIT_MEASURE,
[Qry(3)POreceivingswithWIMS].MIN_ORD,
[Qry(3)POreceivingswithWIMS].MAX_ORD,
[Qry(3)POreceivingswithWIMS].WEIGHT_UOM,
[Qry(3)POreceivingswithWIMS].TOT_WGHT,
[Qry(3)POreceivingswithWIMS].TOT_CASES,
[Qry(3)POreceivingswithWIMS].TOT_UOM,
[Qry(3)POreceivingswithWIMS].LAST_FM_DATE,
[Qry(3)POreceivingswithWIMS].SHIPPING_POINT
HAVING ((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="W") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<30000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="V") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<2000)) OR
((([Qry(3)POreceivingswithWIMS].UNIT_MEASURE)="P") AND
(([Qry(3)POreceivingswithWIMS].TOT_UOM)<40));
Click to expand...
Can you post the entire SQL statement?
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
Thanks, but I get a Call error, whatever that means.
Change the WHERE clause in your SQL statement to look like:
WHERE ([Unit_Measure] = "P" AND [TOT_UOM] < 40 )
OR ([Unit_Measure] = "W" AND [TOT_UOM] < 30000 )
OR ([Unit_Measure] = "V" AND [TOT_UOM] < 2000 )
When you look at this in the design view, it should make sense to
you.
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have a field [UNIT_MEASURE] in a query that I have this in the
criteria
Like "W" Or "V" Or "P"
this pulls everything with W, V, P in it, I have another field
[TOT_UOM] next to it where I put this in the criteria
IIf([UNIT_MEASURE]="P",<40)
IIf([UNIT_MEASURE]="W",<30000)
IIf([UNIT_MEASURE]="V,<2000)
How do I get this field to lookup these criteria's. Do I do it this
way or
=IIf([UNIT_MEASURE]="W",<30000,IIf([UNIT_MEASURE]="P",<40,IIf([UNIT_MEASURE]="V,<2000)))
I would like this field [TOT_UOM] to show only the W's that are
under
30000, the V's under 2000 and the P's under 40. Is this correct, if
so
it's not pulling anything in the query.
Ryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top