SelectCase statement

[dtoney]

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!

 

[Dorian]

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".

 

[Clifford Bass via AccessMonster.com]

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]

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.

 

[dtoney]

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?

 

[John W. Vinson]

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.

 

[Bob Barrows]

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.

 

[Clifford Bass via AccessMonster.com]

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