 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / April 2008If statement question
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  SignatureDon''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 [quoted text clipped - 18 lines] > > Ryan Thanks, but I get a Call error, whatever that means. > Change the WHERE clause in your SQL statement to look like: > [quoted text clipped - 36 lines] > > > Ryan Can you post the entire SQL statement? SignatureDon''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. > [quoted text clipped - 38 lines] > > > > > Ryan 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? > [quoted text clipped - 47 lines] > > > > > Ryan 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 - [table].[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? SignatureGood Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand > Here is the SQL. > [quoted text clipped - 100 lines] >> >> > > > Ryan 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!! On Apr 29, 3:23 pm, "Graham Mandeno" <Graham.Mand...@nomail.please> wrote: > Hi Ryan > [quoted text clipped - 120 lines] > > >> > > > Ryan 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 SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > I have a field [UNIT_MEASURE] in a query that I have this in the > criteria [quoted text clipped - 18 lines] > > Ryan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.