how to use SQL case statement in acess query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 30 Sep 2006 01:55 GMT

is access support the SQL Case statment? if not how I can get the similar
function in access query?
something like
Select CASE column1 WHEN "abc" THEN column1 ELSE column2 END as columnNew
from table1

Thanks
Chris

Reply to this Message

Ken Snell (MVP) - 30 Sep 2006 02:02 GMT

Use an IIf function:

SELECT (IIf([Field1]="abc", [Field1], [Field2])) AS columnNew
FROM table1;

Signature

Ken Snell
<MS ACCESS MVP>

> is access support the SQL Case statment? if not how I can get the similar
> function in access query?
[quoted text clipped - 4 lines]
> Thanks
> Chris

Reply to this Message

Chris - 30 Sep 2006 02:22 GMT

Ken:
Thank you. when I use IIf as you suggested I got the following problem don't
know the reason. both field 1 and field2 are datetime datatype. if one of
them are empty it works fine the columnNew will be the field which is not
empty however when both field1 and field2 not empty it put a #Error in
columnNew
any idea what I have missed?
Thanks
Chris

> Use an IIf function:
>
[quoted text clipped - 9 lines]
> > Thanks
> > Chris

Reply to this Message

Ken Snell (MVP) - 30 Sep 2006 02:43 GMT

Post the actual SQL statement that you're trying to use:

Signature

Ken Snell
<MS ACCESS MVP>

> Ken:
> Thank you. when I use IIf as you suggested I got the following problem
[quoted text clipped - 22 lines]
>> > Thanks
>> > Chris

Reply to this Message

Chris - 30 Sep 2006 02:52 GMT

Ken:
here is the sql statement I am using the problem is the shipdate in the end.
Thanks
Chris
SELECT OrderData.OrderID, OrderData.OrderDatePlaced, OrderData.BillFName,
OrderData.BillLName, OrderData.ExpectedShipDate, OrderData.ShippingType,
OrderData.OrderPlaced, OrderData.OrderStatus, OrderData.OrderTotal,
BasketItems.EstimatedShippingDate, BasketItems.ProductName,
BasketItems.ProductQty, BasketItems.ProductPrice,
BasketItems.ProductSalePrice, OrderData.BillCompany, OrderData.BillAddress1,
OrderData.BillAddress2, OrderData.BillCity, OrderData.BillState,
OrderData.BillZip, OrderData.BillCountry, OrderData.BillPhone,
OrderData.BillFax, OrderData.BillEmail, OrderData.BillType,
OrderData.ShipLName, OrderData.ShipFName, OrderData.DeliveryInstructions,
OrderData.ProductionNote, OrderData.AdditionalNote, OrderData.POnumber,
OrderData.ShipCompany, OrderData.ShipAddress1, OrderData.ShipAddress2,
OrderData.ShipCity, OrderData.ShipState, OrderData.ShipZip,
OrderData.ShipCountry, OrderData.ShipPhone, OrderData.ShipFax,
OrderData.ShipEmail, IIf((Not IsNull([OrderData.ExpectedShipDate]<>'')),
[OrderData.ExpectedShipDate], [BasketItems.EstimatedShippingDate]) AS shipdate
FROM BasketItems INNER JOIN OrderData ON BasketItems.BasketID =
OrderData.BasketID;

> Post the actual SQL statement that you're trying to use:
>
[quoted text clipped - 24 lines]
> >> > Thanks
> >> > Chris

Reply to this Message

Chris - 30 Sep 2006 02:56 GMT

Ken:
Never mind the problem seems go away after I removed the extra <>"" after
the IsNull function. it should not be here.
Thanks for your help.
Chris

> Post the actual SQL statement that you're trying to use:
>
[quoted text clipped - 24 lines]
> >> > Thanks
> >> > Chris

Reply to this Message

Granny Spitz - 30 Sep 2006 02:07 GMT

> Does Access support the SQL Case statment?

No.

> how I can get the similar
> function in an Access query?

Use the equivalent of a *conditional,* an IIF statement.

SELECT IIF(column1 = "abc", column1, column2) AS columnNew
FROM table1;

Reply to this Message

Granny Spitz - 30 Sep 2006 02:37 GMT

>is access support the SQL Case statment? if not how I can get the similar
>function in access query?
[quoted text clipped - 4 lines]
>Thanks
>Chris

If you don't mind my asking, Chris (I suppose that's not your real name as
it's so uncommon in your country), have the people at your company finally
moved from programming Excel spreadsheets to doing the daily work in Access?
I ask because our friend Aaron K* used to work where you work and he
complains bitterly about the tons of repetitive Excel spreadsheets that
people in your company are burdened with producing to track operations.
Access 2007 would seem to be the perfect tool for people familiar with Excel
to migrate those spreadsheets to a relational database, as Access 2007 is
designed for Excel users.

Reply to this Message

Chris - 30 Sep 2006 02:48 GMT

Granny:
This is my first post in this group. My comany currently don't have a lot of
use of excel. we are just a family business. very small have not hired
anybody yet.
Thanks
Chris

> >is access support the SQL Case statment? if not how I can get the similar
> >function in access query?
[quoted text clipped - 14 lines]
> to migrate those spreadsheets to a relational database, as Access 2007 is
> designed for Excel users.

Reply to this Message

Granny Spitz - 30 Sep 2006 03:12 GMT

>Granny:
>This is my first post in this group. My comany currently don't have a lot of
>use of excel. we are just a family business. very small have not hired
>anybody yet.

Oh, I see. You're a contractor. The building you're posting from belongs to
a company that does a lot of work in Excel (or used to, at least). I'm
hoping they'll move that work to Access, but it seems that there aren't
enough people familiar with Access. After seeing your post, I was hoping you
were one of their people migrating to Access. Sorry.

Reply to this Message