SelectCase statement

I'm trying to calculate the business week by looking at logdate. Below is the 
entire syntax for the select query with the case statement indented for 
easier reading:
SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, 
tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, 
tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, 
tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, 
tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, 
tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusType, tblINQ.StatusDate, 
tblINQ.StatusTime, tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, 
tblINQ.CustDivCd, tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter, 
tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig, 
tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName, 
tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass, tblINQ.LocationDesc, 
tblINQ.ReqLastName, tblINQ.ReqFirstName, tblINQ.ResolutionCd, 
tblINQ.ResolutionDesc, tblINQ.Region, tblINQ.TypeCd, tblINQ.AreaCd, 
tblINQ.ProblemCd, tblINQ.SymptomCd, tblINQ.TypeDesc, tblINQ.AreaDesc, 
tblINQ.ProblemDesc, tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket, 
tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage, tblREQ.MaintDTM AS 
tblREQ_MaintDTM, tblIDT.ActualProblem, tblIDT.ActualArea, tblIDT.AssetCode, 
tblIDT.AssetName, tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc, 
tblIDT.RootCauseCode, tblIDT.RootCauseDesc,

CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN INFOP.VINQUIRY.INQ_STATUS_DATE 
WHEN WEEKDAY(tblINQ.LOGDATE) = 2 THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN 
WEEKDAY(tblINQ.LOGDATE) = 3 THEN (tblINQ.LOGDATE) - 2 DAYS WHEN 
WEEKDAY(tblINQ.LOGDATE) = 4 THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN 
WEEKDAY(tblINQ.LOGDATE) = 5 THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN 
WEEKDAY(tblINQ.LOGDATE) = 6 THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN 
WEEKDAY(tblINQ.LOGDATE) = 7 THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS 
WEEK , 

INTO tblData
FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON 
tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
WHERE (((tblINQ.LOGDATE)>Date()-90) AND ((tblINQ.LoggedByDivCd)="45") AND 
((tblINQ.LoggedByDeptCd)="820") AND ((tblREQ.ReqSeqNbr)=1));

I get a syntax error MissingOperator. Please Help!
0
Utf
1/4/2010 6:08:01 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
1005 Views

Similar Articles

[PageSpeed] 50

There is no CASE statement in MS Access SQL.
Look in Access help for the IIF statement.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"dtoney" wrote:

> I'm trying to calculate the business week by looking at logdate. Below is the 
> entire syntax for the select query with the case statement indented for 
> easier reading:
> SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, 
> tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, 
> tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, 
> tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, 
> tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, 
> tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusType, tblINQ.StatusDate, 
> tblINQ.StatusTime, tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, 
> tblINQ.CustDivCd, tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter, 
> tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig, 
> tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName, 
> tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass, tblINQ.LocationDesc, 
> tblINQ.ReqLastName, tblINQ.ReqFirstName, tblINQ.ResolutionCd, 
> tblINQ.ResolutionDesc, tblINQ.Region, tblINQ.TypeCd, tblINQ.AreaCd, 
> tblINQ.ProblemCd, tblINQ.SymptomCd, tblINQ.TypeDesc, tblINQ.AreaDesc, 
> tblINQ.ProblemDesc, tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket, 
> tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage, tblREQ.MaintDTM AS 
> tblREQ_MaintDTM, tblIDT.ActualProblem, tblIDT.ActualArea, tblIDT.AssetCode, 
> tblIDT.AssetName, tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc, 
> tblIDT.RootCauseCode, tblIDT.RootCauseDesc,
> 
> CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN INFOP.VINQUIRY.INQ_STATUS_DATE 
> WHEN WEEKDAY(tblINQ.LOGDATE) = 2 THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN 
> WEEKDAY(tblINQ.LOGDATE) = 3 THEN (tblINQ.LOGDATE) - 2 DAYS WHEN 
> WEEKDAY(tblINQ.LOGDATE) = 4 THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN 
> WEEKDAY(tblINQ.LOGDATE) = 5 THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN 
> WEEKDAY(tblINQ.LOGDATE) = 6 THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN 
> WEEKDAY(tblINQ.LOGDATE) = 7 THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS 
> WEEK , 
> 
> INTO tblData
> FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON 
> tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
> WHERE (((tblINQ.LOGDATE)>Date()-90) AND ((tblINQ.LoggedByDivCd)="45") AND 
> ((tblINQ.LoggedByDeptCd)="820") AND ((tblREQ.ReqSeqNbr)=1));
> 
> I get a syntax error MissingOperator. Please Help!
0
Utf
1/4/2010 6:33:01 PM
Hi,

     Try the simpler:

tblINQ.LOGDATE - WEEKDAY(tblINQ.LOGDATE) + 1 AS WEEK

     You can also narrow down the problem by copying the query and trying
simpler versions until you get it to work.  So, does it work as a plain
select statement?  If not, correct the problem first then change it back to
an append statement.

         Clifford Bass

dtoney wrote:
>I'm trying to calculate the business week by looking at logdate. Below is the 
>entire syntax for the select query with the case statement indented for 
>easier reading:
>SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, 
>tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, 
>tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, 
>tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, 
>tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, 
>tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusType, tblINQ.StatusDate, 
>tblINQ.StatusTime, tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, 
>tblINQ.CustDivCd, tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter, 
>tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig, 
>tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName, 
>tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass, tblINQ.LocationDesc, 
>tblINQ.ReqLastName, tblINQ.ReqFirstName, tblINQ.ResolutionCd, 
>tblINQ.ResolutionDesc, tblINQ.Region, tblINQ.TypeCd, tblINQ.AreaCd, 
>tblINQ.ProblemCd, tblINQ.SymptomCd, tblINQ.TypeDesc, tblINQ.AreaDesc, 
>tblINQ.ProblemDesc, tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket, 
>tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage, tblREQ.MaintDTM AS 
>tblREQ_MaintDTM, tblIDT.ActualProblem, tblIDT.ActualArea, tblIDT.AssetCode, 
>tblIDT.AssetName, tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc, 
>tblIDT.RootCauseCode, tblIDT.RootCauseDesc,
>
>CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN INFOP.VINQUIRY.INQ_STATUS_DATE 
>WHEN WEEKDAY(tblINQ.LOGDATE) = 2 THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN 
>WEEKDAY(tblINQ.LOGDATE) = 3 THEN (tblINQ.LOGDATE) - 2 DAYS WHEN 
>WEEKDAY(tblINQ.LOGDATE) = 4 THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN 
>WEEKDAY(tblINQ.LOGDATE) = 5 THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN 
>WEEKDAY(tblINQ.LOGDATE) = 6 THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN 
>WEEKDAY(tblINQ.LOGDATE) = 7 THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS 
>WEEK , 
>
>INTO tblData
>FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON 
>tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
>WHERE (((tblINQ.LOGDATE)>Date()-90) AND ((tblINQ.LoggedByDivCd)="45") AND 
>((tblINQ.LoggedByDeptCd)="820") AND ((tblREQ.ReqSeqNbr)=1));
>
>I get a syntax error MissingOperator. Please Help!

-- 
Message posted via http://www.accessmonster.com

0
Clifford
1/4/2010 6:38:15 PM
I have several text books that illustrate the use of the CASE statement. It 
appears it may be in VBA.... I keep getting an error in SQL and need to know 
how to write it in VBA.. we use CASE statements in our SQL passthrough 
queries every day. 

"Dorian" wrote:

> There is no CASE statement in MS Access SQL.
> Look in Access help for the IIF statement.
> -- Dorian
> "Give someone a fish and they eat for a day; teach someone to fish and they 
> eat for a lifetime".
> 
> 
> "dtoney" wrote:
> 
> > I'm trying to calculate the business week by looking at logdate. Below is the 
> > entire syntax for the select query with the case statement indented for 
> > easier reading:
> > SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, 
> > tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, 
> > tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, 
> > tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, 
> > tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, 
> > tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusType, tblINQ.StatusDate, 
> > tblINQ.StatusTime, tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, 
> > tblINQ.CustDivCd, tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter, 
> > tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig, 
> > tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName, 
> > tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass, tblINQ.LocationDesc, 
> > tblINQ.ReqLastName, tblINQ.ReqFirstName, tblINQ.ResolutionCd, 
> > tblINQ.ResolutionDesc, tblINQ.Region, tblINQ.TypeCd, tblINQ.AreaCd, 
> > tblINQ.ProblemCd, tblINQ.SymptomCd, tblINQ.TypeDesc, tblINQ.AreaDesc, 
> > tblINQ.ProblemDesc, tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket, 
> > tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage, tblREQ.MaintDTM AS 
> > tblREQ_MaintDTM, tblIDT.ActualProblem, tblIDT.ActualArea, tblIDT.AssetCode, 
> > tblIDT.AssetName, tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc, 
> > tblIDT.RootCauseCode, tblIDT.RootCauseDesc,
> > 
> > CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN INFOP.VINQUIRY.INQ_STATUS_DATE 
> > WHEN WEEKDAY(tblINQ.LOGDATE) = 2 THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN 
> > WEEKDAY(tblINQ.LOGDATE) = 3 THEN (tblINQ.LOGDATE) - 2 DAYS WHEN 
> > WEEKDAY(tblINQ.LOGDATE) = 4 THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN 
> > WEEKDAY(tblINQ.LOGDATE) = 5 THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN 
> > WEEKDAY(tblINQ.LOGDATE) = 6 THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN 
> > WEEKDAY(tblINQ.LOGDATE) = 7 THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS 
> > WEEK , 
> > 
> > INTO tblData
> > FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON 
> > tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
> > WHERE (((tblINQ.LOGDATE)>Date()-90) AND ((tblINQ.LoggedByDivCd)="45") AND 
> > ((tblINQ.LoggedByDeptCd)="820") AND ((tblREQ.ReqSeqNbr)=1));
> > 
> > I get a syntax error MissingOperator. Please Help!
0
Utf
1/5/2010 3:51:01 AM
so is this in effect taking the logdate and subtracting it from the day of 
the week then adding back 1 to show the Sunday date for the week of the log 
date?

"Clifford Bass via AccessMonster.com" wrote:

> Hi,
> 
>      Try the simpler:
> 
> tblINQ.LOGDATE - WEEKDAY(tblINQ.LOGDATE) + 1 AS WEEK
> 
>      You can also narrow down the problem by copying the query and trying
> simpler versions until you get it to work.  So, does it work as a plain
> select statement?  If not, correct the problem first then change it back to
> an append statement.
> 
>          Clifford Bass
> 
> dtoney wrote:
> >I'm trying to calculate the business week by looking at logdate. Below is the 
> >entire syntax for the select query with the case statement indented for 
> >easier reading:
> >SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM, tblINQ.AltKeyID, 
> >tblINQ.OrigParentChild, tblINQ.SevCd, tblINQ.EDIType, tblINQ.QueOwnerDesc, 
> >tblINQ.Origination, tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType, 
> >tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID, 
> >tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID, tblINQ.RespTech, 
> >tblINQ.SupportArea, tblINQ.StatusCd, tblINQ.StatusType, tblINQ.StatusDate, 
> >tblINQ.StatusTime, tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, 
> >tblINQ.CustDivCd, tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter, 
> >tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig, 
> >tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName, 
> >tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass, tblINQ.LocationDesc, 
> >tblINQ.ReqLastName, tblINQ.ReqFirstName, tblINQ.ResolutionCd, 
> >tblINQ.ResolutionDesc, tblINQ.Region, tblINQ.TypeCd, tblINQ.AreaCd, 
> >tblINQ.ProblemCd, tblINQ.SymptomCd, tblINQ.TypeDesc, tblINQ.AreaDesc, 
> >tblINQ.ProblemDesc, tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket, 
> >tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage, tblREQ.MaintDTM AS 
> >tblREQ_MaintDTM, tblIDT.ActualProblem, tblIDT.ActualArea, tblIDT.AssetCode, 
> >tblIDT.AssetName, tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc, 
> >tblIDT.RootCauseCode, tblIDT.RootCauseDesc,
> >
> >CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN INFOP.VINQUIRY.INQ_STATUS_DATE 
> >WHEN WEEKDAY(tblINQ.LOGDATE) = 2 THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN 
> >WEEKDAY(tblINQ.LOGDATE) = 3 THEN (tblINQ.LOGDATE) - 2 DAYS WHEN 
> >WEEKDAY(tblINQ.LOGDATE) = 4 THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN 
> >WEEKDAY(tblINQ.LOGDATE) = 5 THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN 
> >WEEKDAY(tblINQ.LOGDATE) = 6 THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN 
> >WEEKDAY(tblINQ.LOGDATE) = 7 THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS 
> >WEEK , 
> >
> >INTO tblData
> >FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON 
> >tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
> >WHERE (((tblINQ.LOGDATE)>Date()-90) AND ((tblINQ.LoggedByDivCd)="45") AND 
> >((tblINQ.LoggedByDeptCd)="820") AND ((tblREQ.ReqSeqNbr)=1));
> >
> >I get a syntax error MissingOperator. Please Help!
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
1/5/2010 3:54:02 AM
On Mon, 4 Jan 2010 19:51:01 -0800, dtoney <dtoney@discussions.microsoft.com>
wrote:

>I keep getting an error in SQL and need to know 
>how to write it in VBA.. we use CASE statements in our SQL passthrough 
>queries every day. 

SQL/Server and Access/JET are different dialects of SQL.

CASE is supported in SQL/Server.

CASE is not available in Access/JET.

They are different programs, and use different syntax.

Sorry, but the answer is You Can't Do It That Way.

You can use the VBA functions IIF() or Switch() instead of CASE.
-- 

             John W. Vinson [MVP]
0
John
1/5/2010 4:14:36 AM
There is a Select ... Case statement in VBA. It is used for program flow and 
is not related to sql.

Transact-SQL (SQL Server's SQL dialect) supports a CASE expression
JetSQL (used by Jet databases in Access) does not support CASE. It uses 
either of two alternatives instead:
- an Immediate If function -  Iif()
  It is limited compared to SQL Server's CASE expression since it only 
allows a single boolean comparison but you can nest the iif expressions

- a Switch() function which allows multiple comparisons and is a little 
closer to the CASE expression

So, you have now mentioned "passthrough": is this a SQL passthrough query 
you are constructing? Intended to be executed on SQL Server? Or is this a 
Jet query intended to be internally executed against a local Jet database?

As for writing a query in VBA ... your original message contained nothing to 
indicate that you were doing this in VBA, and frankly, it does not even 
matter. The intent when creating a query in VBA is to construct a sql 
statement that will run as-is iwhen executed by the database's query engine. 
In other words, the result needs to look exactly as it looks when you build 
it in the SQL View of the Access Query Builder.

So, if you are dynamically concatenating strings together in VBA to 
construct a sql statement, you have to look at the result of the 
concatenation to debug the resulting sql statement. That appears to be what 
you have below: the result of building a sql statement in VBA code ... 
correct?

So, assuming this is intended to be used as the SQL property of a 
passthrough querydef, the way to debug it is to create a passthrough query 
pointed at your SQL database, paste the statement below into the SQL View 
and try to execute it. If it raises an error, remove bits of it 
one-at-a-time until it runs with no error. You have now isolated the bit 
that is causing the problem. If that does not reveal the problem to you, 
show it to us and tell us what error it raises.

Something just caught my eye:
 (tblINQ.LOGDATE)  - 1 DAYS

This is not proper syntax for either Jet or SQL Server databases. Is this 
passthrough query intended to run against a Db2 database? If so, DB2 also 
supports CASE expressions.

Perhaps you should provide further details if you wish to get further 
assistance.

dtoney wrote:
> I have several text books that illustrate the use of the CASE
> statement. It appears it may be in VBA.... I keep getting an error in
> SQL and need to know how to write it in VBA.. we use CASE statements
> in our SQL passthrough
> queries every day.
>
> "Dorian" wrote:
>
>> There is no CASE statement in MS Access SQL.
>> Look in Access help for the IIF statement.
>> -- Dorian
>> "Give someone a fish and they eat for a day; teach someone to fish
>> and they eat for a lifetime".
>>
>>
>> "dtoney" wrote:
>>
>>> I'm trying to calculate the business week by looking at logdate.
>>> Below is the entire syntax for the select query with the case
>>> statement indented for easier reading:
>>> SELECT tblINQ.LOGDATE, tblINQ.Ticket, tblINQ.TicketDTM,
>>> tblINQ.AltKeyID, tblINQ.OrigParentChild, tblINQ.SevCd,
>>> tblINQ.EDIType, tblINQ.QueOwnerDesc, tblINQ.Origination,
>>> tblINQ.ServiceType, tblINQ.CategoryType, tblINQ.TSCType,
>>> tblINQ.LoggedByDivCd, tblINQ.LoggedByDeptCd, tblINQ.LoggedByID,
>>> tblINQ.OwnerDivNbr, tblINQ.OwnerDeptCd, tblINQ.OwnerID,
>>> tblINQ.RespTech, tblINQ.SupportArea, tblINQ.StatusCd,
>>> tblINQ.StatusType, tblINQ.StatusDate, tblINQ.StatusTime,
>>> tblINQ.ActualCloseDt, tblINQ.OwnerAtClose, tblINQ.CustDivCd,
>>> tblINQ.Organization, tblINQ.CustID, tblINQ.CustCostCenter,
>>> tblINQ.CustLOB, tblINQ.BusinessArea, tblINQ.BusinessAreaOrig,
>>> tblINQ.CustEmpNo, tblINQ.CustLastName, tblINQ.CustFirstName,
>>> tblINQ.CustLocCd, tblINQ.CustLocName, tblINQ.CustClass,
>>> tblINQ.LocationDesc, tblINQ.ReqLastName, tblINQ.ReqFirstName,
>>> tblINQ.ResolutionCd, tblINQ.ResolutionDesc, tblINQ.Region,
>>> tblINQ.TypeCd, tblINQ.AreaCd, tblINQ.ProblemCd, tblINQ.SymptomCd,
>>> tblINQ.TypeDesc, tblINQ.AreaDesc, tblINQ.ProblemDesc,
>>> tblINQ.SymptomDesc, tblREQ.ReqLogDate, tblREQ.ReqTicket,
>>> tblREQ.ReqTicketDTM, tblREQ.ReqSeqNbr, tblREQ.ReqVerbiage,
>>> tblREQ.MaintDTM AS tblREQ_MaintDTM, tblIDT.ActualProblem,
>>> tblIDT.ActualArea, tblIDT.AssetCode, tblIDT.AssetName,
>>> tblIDT.CorrectiveActionCode, tblIDT.CorrectiveActionDesc,
>>> tblIDT.RootCauseCode, tblIDT.RootCauseDesc,
>>>
>>> CASE WHEN WEEKDAY(tblINQ.LOGDATE) = 1 THEN
>>> INFOP.VINQUIRY.INQ_STATUS_DATE WHEN WEEKDAY(tblINQ.LOGDATE) = 2
>>> THEN (tblINQ.LOGDATE)  - 1 DAYS WHEN WEEKDAY(tblINQ.LOGDATE) = 3
>>> THEN (tblINQ.LOGDATE) - 2 DAYS WHEN WEEKDAY(tblINQ.LOGDATE) = 4
>>> THEN (tblINQ.LOGDATE)  - 3 DAYS WHEN WEEKDAY(tblINQ.LOGDATE) = 5
>>> THEN (tblINQ.LOGDATE)  - 4 DAYS WHEN WEEKDAY(tblINQ.LOGDATE) = 6
>>> THEN (tblINQ.LOGDATE)  - 5 DAYS WHEN WEEKDAY(tblINQ.LOGDATE) = 7
>>> THEN (tblINQ.LOGDATE)  - 6 DAY ELSE NULL END AS WEEK ,
>>>
>>> INTO tblData
>>> FROM tblINQ INNER JOIN (tblIDT INNER JOIN tblREQ ON
>>> tblIDT.Ticket=tblREQ.ReqTicket) ON tblINQ.Ticket=tblREQ.ReqTicket
>>> WHERE (((tblINQ.LOGDATE)>Date()-90) AND
>>> ((tblINQ.LoggedByDivCd)="45") AND ((tblINQ.LoggedByDeptCd)="820")
>>> AND ((tblREQ.ReqSeqNbr)=1));
>>>
>>> I get a syntax error MissingOperator. Please Help!

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 


0
Bob
1/5/2010 11:56:03 AM
Hi,

     Sorry, I messed up.  You would actually want something like:

IIf(WEEKDAY(tblINQ.LOGDATE) = 1, INFOP.VINQUIRY.INQ_STATUS_DATE, tblINQ.
LOGDATE - (WEEKDAY(tblINQ.LOGDATE) - 1)) AS WEEK

     All but the first part and the else part do the exact same thing.  You
will need to adjust the Jet IIf() function to the appropriate SQL dialect if
function since you are doing a pass-through query.  WEEKDAY(tblINQ.LOGDATE)
should never be outside the 1 to 7 range so the else part at the end is not
really needed.  If tblINQ.LOGDATE can be null, the calculation in the false
part should return.

                Hope that helps better,

                     Clifford Bass

dtoney wrote:
>so is this in effect taking the logdate and subtracting it from the day of 
>the week then adding back 1 to show the Sunday date for the week of the log 
>date?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

0
Clifford
1/5/2010 4:50:53 PM
Reply:

Similar Artilces:

updating through a loop statement
I'm using a loop to update a table dynamically, meaning that only certain fields will be updated each time the stored procedure that contains the loop. I have noticed that it's taking a lot of time. I have a feeling that it's sending the various update statements to the sql server as quickly as it goes through the loop. Is there a way to pause the loop until each update statement is finished processing? I should add the the update statement is executed via sp_executesql as I have to build the statement with various variables. Hope this made sense scuba79 (sc...

Money 2001
I have recently moved to Papua New Guinea and have set up my local accounts on Money 2001. I have added the local currency - PGK kina to the currencies but when I try to download my bank statements I get this error box message: Import Completed Your file import was completed, but 1 item could not be processed... File import X Your statement contained a default currency (PGK) which was unrelated to the currency of the account "Westpac PNG Cheque". Money does not support statements containing unrelated default currencies. I was dowloading the file in OFX format. QIF files work...

If statement..... Possible Match
Hi, I'm trying to make two fields that are similar. I create but it won't give me the result. FieldA : Baseball FieldB: Baseb Possible Match: Yes FieldA: Football FieldB: Football Possible Match: Yes FieldA: Baseball FieldB: Football Possible Match: NO Please help me if I did the right or not. Your help would be much appreciated. Possible Match: IIF([FieldA] like [FieldB],"Yes","NO") Thanks learning_codes@hotmail.com wrote: >I'm trying to make two fields that are similar. > >I create but it won't g...

If/then statement between dates
Hello. I am trying to draft an if/then statement that will pull a rate from a table and use it to multiply by a number from a different table. Table: ExpensesMain Field: Mileage Field: Date of Expense Table: MileageRate Field: EffetiveDate Field: Rate I am trying to add into my form (ExpensesAll) a string to an unbound text box that will multiply the mileage by the mileage rate that was effective on the date of the expense. There are several different rates corresponding to their respective start dates in the MileageRate table. An...

If statements #3
worked perfectly thanks alo -- Brad198 ----------------------------------------------------------------------- Brad1982's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1569 View this thread: http://www.excelforum.com/showthread.php?threadid=27255 ...

If statements #2
I have this formula which works 99% of the time, only in the last instance do I get it to fail when I add if T<>""),"". ............O...............Q..................R.............S...................T 1) ........X 2) ........X...................................................X 3) ........X................X 4) ........X................X...................X 5) .......X.................X...................X.............X 6) .........X................X....................X..............X................X What I'm trying to do is create a formula so that the user ...

If
I have a code that is not processing correctly. Can someone look at it to see what I am missing. I have never done this kind of statement before. Private Sub CmdSearch_Click() Dim stDocNameSearch As String Dim VarSo As Variant Dim VarRMA As Variant Dim VarAccNum As Variant Dim VarPart As Variant Dim VarSN As Variant Dim VarLot As Variant VarSo = Me!CmboSO VarRMA = Me!CmboRMA VarAccNum = Me!CmboAccNum VarPart = Me!CmboPart VarSN = Me!CmboSN VarLot = Me!CmboLot stDocNameSO = "RMARequestSO" stDocNameRMA = "RMARequestRMA" stDocNameAccNum = "RMARequestAccNum" st...

Printing Statements
Provide the ability to print statements, in both Store Operations and Headquarters, by account number, account name (alphabetically), and/or by Statement Balance. Currently, statements print in an indeterminate order that cannot be easily matched with an account list or any other know filter. When my printer jams i get to reprint all the statements over again... a process that takes several hours... I get to employ 2 people full time to sort the statements on billing day... In the old, Quicksell days, before microsoft's buy out i could print in any order, in blocks, and restart t...

Deadlock on SQL SELECT statement
I have inherited the maintenance of a product which includes the snipet of code below. Every 10 seconds the code is executed. It is causing a deadlock in some instances, but I am undable to reproduce the problem on my machine. The "PC" table contains a list of PCs seen on a network, so isn't very large. Since I dont have much background in database programming, I was wondering if there is some simple answer to the deadlock issue...but from reading on deadlocks, there rarely seems to be a simple solution. //**************************************** // Find PCs to restart CStri...

Statement download problems
OK I figured out how to save my file and then import it. But I'd still like to simply open the file from my bank and have my file updated without having to first save it to my computer. Open IE click the tools menu and choose options - on the advanced tab scroll down to security - find the option for - do not save encrypted files to disk - change this option from what you have - this controls that behavior. Jim "David E" <anonymous@discussions.microsoft.com> wrote in message news:11e101c4abff$1aec7210$a401280a@phx.gbl... > OK I figured out how to save my file an...

nested if statements
Not sure if anyone can help me with this I have 2 sets of criteria.. and I'd like a formula that checks 2 cells and then calculates a result based on the criteria of those 2 cells. i'll try my best to explain. Sales: $50,001 - $75,000 GP: $20,000-24,999 Cell B4 is where the sales number will be entered Cell B5 will be where the GP is entered What I want is to have a formula that says If Sales are between 50,001-75,000 or if GP is btween 20k - 24,999, then take 3% of GP. or if Sales are 75k+ or GP is 25k+ then take 4% of GP. Not sure if anyone can help me with this. I tried using ...

If Statement 01-13-10
Need help with this formula? Need to calculate a formula for CELL A based upon the following IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 tss wrote: > Need help with this formula? > Need to calculate a formula for CELL A based upon the following > IF CELL B = STOCK and CELL C >= 1.0, <25.0 THAN CELL D * 4 =IF(AND(B1="STOCK",C1>=1,C1<25),D1*4,"**No idea what you want here**") IF( AND( logical1, logical2, logical3 ), value_if_true, **value_if_false** ) Put this in A1: =3DIF(AND(B1=3D"Stock",C1>=3D1,C1<...

SQL select statement question
Hi Everybody - I am trying to write a SQL query where I can select all vendors except one particular one - How do I write this? Is there some kind of 'except' command ? SELECT * FROM PMxxxxx WHERE VENDORID <> 'yourexcludedvendor' Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com And if you decide you want to exclude more then one use the NOT IN clause... SELECT * FROM PMxxxxx WHERE VENDORID NOT IN ( 'yourexcludedvendor1', 'yourexcludedvendor2', 'yourexcludedvendor....

Financial Statements as My Reports on Home Page
We recently upgraded to GP 9.0 and I would like to add some financial statements written with Advanced Financial to "My Reports" section. I have security clearance for everything in GP but none of the financial statements appear in my report list when I veiw all reports or financial reports. Am I doing something incorrectly? How can I add financial staterments to the "My Reports" section of my GP 9.0 home page? Thanks for your help. I don't believe the functionality works for AFA. "Don Johnson" wrote: > We recently upgraded to GP 9.0 and I would li...

Vendor statement of account #3
Hi community, I need to make a Vendor statement of account like the one for customer (also multicurrency). I thank you in anticipation. ...

Place If-Statement in Macro?
I'd like to automatically copy this If-Statement into a cell using a macro. =IF(OR(C1="05",C1="07",C1="10",C1="12",C1="03",C1="01",C1="08"),"31",IF(OR(C 1="04",C1="06",C1="09",C1="11"),"30",IF(C1="02","29",""))) Thanks for your help. Mike There are far better ways to accomplish what you're trying to do, but this will work: Public Sub EnterLongFormula() Selection.Formula = "=IF(OR(C1=""05"&q...

Web Statements versus Direct Statements in online services
New to Money 2004. I have a checking account that for some reason is set up for online services as a "Web Statement". In order to get statements I have to go to my bank's web site and download manually. All of my other accounts are set up to download every time I launch Money, and is labeled as "Direct Statements" on the online services list of accounts. I can't seem to figure out how to switch the settings on this one checking account to automatically download. Any help is appreciated You cannot switch that setting yourself. The web statements vs. direct s...

If-Then Statement #2
How do I do the 50-70 pound part? >50<70 ??? -- alan ----------------------------------------------------------------------- alanf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536 View this thread: http://www.excelforum.com/showthread.php?threadid=26983 ...

Access 2003 multiple if then statement help
Hello, I am having a problem constructing the proper clause for querying my database. I have multiple values used in computing daily averages of constituent data for measurements. An example of my table for my table [tbl_data] is shown below; Constit Value AdjConstitValue UseIterpolated DoNotUse S1 1300 1258 [x] [ ] S1 1352 [ ] [ ] S1 1564 [ ] [x] etc.. Using an if statement I put together a statement for the query to display the AdjConstitValue if UseInterpolated is checked and that works f...

More efficient code for multiple If...End If statements
In the on current event of my form, I'm setting the Backstyle and Borderstyle of several controls based on the whether the control is null. For example: ' 0 = Transparent ' 1 = Normal If IsNull(Me.FirstName) Then Me.FirstName.BackStyle = 0 ' Transparent Me.FirstName.BorderStyle = 1 Else Me.FirstName.BackStyle = 1 ' Normal Me.FirstName.BorderStyle = 1 End If If IsNull(Me.LastName) Then Me.LastName.BackStyle = 0 ' Transparent Me.LastName.BorderStyle = 1 Else Me.LastName.BackStyle = 1 ' Normal Me.LastName.Border...

Adding With If Statements
I need some assistance please. I have dollar amounts in column C and "Y" or "N" in column D. I need to add all dollar amounts in column C that have a "Y" in column D. I know it requires an "IF" statement, but I can not figure it out and neither can my friends. -- Thank you very much ...

Another IF statement
I have a minimum order quantity that must be maintained. Content of cell A1 will vary, B1 will be constant (90) and C1 will be the resulting cost. C1 has a minimum order charge of 180 applied to it so: C1 needs to show 0 when A1 is 0 C1 needs to show 180 when A1 is 1 C1 needs to show variable of A1 x B1 above 180 I hope this is clear. Many thanks, Dave Try in C1: =IF(A1=0,0,MAX(A1*B1,180)) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "Dave in Ampthill" wrote: > I have a minimum order quantity that must be ...

How do I use a wildcard in an if statement?
I want to use a conditional statement to see if a particular cell has a specific text within a cell. For example: I want to know if cell A1 contains the text "bag" even though it actually contains "Large BAG 3". I want this to work but it does not: if(A1="*bag*","yes","no") It should return "yes" since bag is within the cell. Please help. You could use: =IF(ISERROR((FIND("Bag",A1,1))),"no","yes") But keep in mind that FIND is case sensitive. You could also use: =IF(ISERROR((SEARCH("*Bag*&qu...

IIF statement, proper formula
Not entirely sure if the IIF statement is the correct way to go about this, I have a field on a query that has total pounds of either printed or plain product. I want to add a column to my query that only adds up the printed product. So, if "product type" is like printed, then add up a total, is basically what I am trying to say. Can you help me with the proper formula? -- Message posted via http://www.accessmonster.com Assuming you have: - a Text field named product type - a Number field named pounds put a text box in the Report Footer section, and set its Contro...

Excel Template 9
We need to link the running balance on this template to a aging summary with customer balance currently due. Are there any compatible templates such as A/R Summary based on the balances in the Statement of Account template? [Company Name] Statement Date: 2/23/2010 [Company Address] Date Due: [DATE DUE] [City, ST ZIP Code] [Phone] STATEMENT OF ACCOUNT [Customer Name] [Address] [City, State ZIP Code] DATE DESCRIPTION CHARGES CREDITS ACCOUNT BALANCE Balance brought forward $56.00 05/12/04 The Phone Company $500.00 ...