how to use SQL case statement in acess query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Use an IIf function:

SELECT (IIf([Field1]="abc", [Field1], [Field2])) AS columnNew
FROM table1;
 
Chris said:
Does Access support the SQL Case statment?
No.

how I can get the similar
function in an Access query?
Click to expand...

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

SELECT IIF(column1 = "abc", column1, column2) AS columnNew
FROM table1;
 
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


Ken Snell (MVP) said:
Use an IIf function:

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

--

Ken Snell
<MS ACCESS MVP>

Chris said:
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
Click to expand...
Click to expand...
 
Chris said:
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
Click to expand...

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.
 
Post the actual SQL statement that you're trying to use:

--

Ken Snell
<MS ACCESS MVP>

Chris said:
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


Ken Snell (MVP) said:
Use an IIf function:

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

--

Ken Snell
<MS ACCESS MVP>

Chris said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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;


Ken Snell (MVP) said:
Post the actual SQL statement that you're trying to use:

--

Ken Snell
<MS ACCESS MVP>

Chris said:
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


Ken Snell (MVP) said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Ken Snell (MVP) said:
Post the actual SQL statement that you're trying to use:

--

Ken Snell
<MS ACCESS MVP>

Chris said:
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


Ken Snell (MVP) said:
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
Click to expand...
Click to expand...
Click to expand...
 
Chris said:
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.
Click to expand...

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.
 
Back
Top