how to use SQL case statement in acess query?

[Chris]

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

 

[Ken Snell \(MVP\)]

Use an IIf function:

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

 

[Granny Spitz via AccessMonster.com]

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;

 

[Chris]

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:

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[Granny Spitz via AccessMonster.com]

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

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.

 

[Ken Snell \(MVP\)]

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

--

Ken Snell
<MS ACCESS MVP>

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:

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[Chris]

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

 

[Chris]

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:

--

Ken Snell
<MS ACCESS MVP>

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:

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[Chris]

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:

--

Ken Snell
<MS ACCESS MVP>

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:

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[Granny Spitz via AccessMonster.com]

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.