Solved

How to create a if condition on Ms access querry

Posted on 2006-10-20
2
2,795 Views
Last Modified: 2008-01-09
Hi There,
I'm creating a query in access which I use for my Vb .net program.
One of the field in the query is a sum of the Weight field. I would like to create another field on the run called
Carrier - which is based on the sum of the weight field.
if the weight in > 3000  then I want it to be truckload else fed ex.

I also got in to the sql view from the access query and tried the Case statement, but gives me a systex error.

I have attached the Sql view of the query

SELECT DISTINCTROW one2.cus_no, one2.cus_alt_adr_cd, one2.loc, one2.[FINAL DATE], one2.[REQ DATE], one2.hold_fg, one2.oe_po_no, one2.[WO #], one2.status, one2.ORDATE, one2.cus_name, one2.FRT, one2.note_1, one2.note_2, one2.note_3, one2.note_4, one2.note_5, one2.ship_instruction_1, one2.ship_instruction_2, one2.[DEAD DATE], Sum(one2.qty_bkord) AS [Sum Of qty_bkord], Sum(one2.qty_ordered) AS [Sum Of qty_ordered], Sum(one2.qty_to_ship) AS [Sum Of qty_to_ship], Max(one2.A4GLIdentity) AS [Max Of A4GLIdentity], Sum(one2.tot_qty_ordered) AS [Sum Of tot_qty_ordered], Sum(one2.tot_qty_shipped) AS [Sum Of tot_qty_shipped], Sum(one2.qty_allocated) AS [Sum Of qty_allocated], Sum(one2.WT) AS SumOfWT,

Case Sum (one2.WT)
When >= 3000 THEN 'Truck Load'
Else
'Fed ex'
End As [Carrier Info]


FROM one2
GROUP BY one2.cus_no, one2.cus_alt_adr_cd, one2.loc, one2.[FINAL DATE], one2.[REQ DATE], one2.hold_fg, one2.oe_po_no, one2.[WO #], one2.status, one2.ORDATE, one2.cus_name, one2.FRT, one2.note_1, one2.note_2, one2.note_3, one2.note_4, one2.note_5, one2.ship_instruction_1, one2.ship_instruction_2, one2.[DEAD DATE];


I will appreciate any help.
Thanks
0
Comment
Question by:Sivasan
2 Comments
 

Author Comment

by:Sivasan
ID: 17778021
Urgent help needed
0
 
LVL 34

Accepted Solution

by:
flavo earned 500 total points
ID: 17778061
Try:

Jet doesn't support the CASE WHEN THEN ELSE syntax, use IIF instead:

IIF(Sum (one2.WT) >= 3000, "Truck Load", "Fed ex") AS [Carrier Info]

Dave

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question