Switch Statement Not Accepting <,>,or = Operators

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm clearly doing something wrong and would love some guidance. I know enough SQL to dig myself into a deep hole, but not enough to get out of it, as I'm unfortunately very limited by our highly secured data which does not allow for any SQL statements outside of SELECT.

In short, I'm creating a productivity report for various warehouse shifts, and shifts that split days are proving complicated. This report basically totals up how many pallets shipped per hour with a 24 hour period starting at 5AM and ending at 4:59AM the following day. Basically I need the data from the screenshot here to be in a single line, so my thought was to simply change the date of the data to the next day for data recorded between the hours of midnight (hour 0) and 5AM.

In this example Aaron didn't do anything in the midnight hour at all, but had 464 cases picked in the hour of 1AM, so (red arrow), that 1AM data needs to be bumped to the previous date. Thought it would be simple, but with my database limitations from a locked down ODBC/AS400/IBM Client I'm not having fun. And to make matters worse, my DATE field refuses to accept any type of DateAdd functions - I'm thinking it might not be a legit field somehow but it's all I got.


2.png


SQL:
SELECT

MAX(HOUR),

Switch(
MAX(HOUR) < 5, MAX(DATE) -1 DAY,
false, MAX(DATE)
) AS NEW_DATE,

MAX(LTRIM(RTRIM(HMBLDG))) AS BLDG,

MAX(LTRIM(RTRIM(HMUSER))) AS USER_ID,

MAX(LTRIM(RTRIM(USDESC))) AS USER_NAME,

MAX(CASE WHEN HMTTYP = 'AW' THEN 'Putaway'
WHEN HMTTYP = 'FM' THEN 'Floor Move'
WHEN HMTTYP = 'IS' THEN 'Staging'
WHEN HMTTYP = 'LO' THEN 'Loading'
WHEN HMTTYP = 'PK' THEN 'Picking' ELSE LTRIM(RTRIM(HMTTYP)) END) AS MOVE_TYPE,

SUM(CASE WHEN HMPQTY = 0 THEN 1 ELSE LTRIM(RTRIM(HMPQTY)) END) AS PLT

FROM LOREPORTH

GROUP BY  DATE, HOUR, HMBLDG, HMUSER, USDESC, HMPQTY

ORDER BY DATE, HOUR, HMBLDG ASC

Any guidance, ideas, suggestions, etc. would be immensely appreciated.
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,301
Office Version
  1. 365
I am not familiar with /AS400/IBM Client. Can you describe your set up in terms of where Access fits?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
Every flavor of sql has its own syntax (and in some cases peculiarities). So really I also think that is important information that needs to be clarified (what database system are you working with?) To be honest, I've never seen switch used outside of MSAccess anyway -- that's somewhat suspicious at first glance.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
AS400 really isn't all that relevant for this particular question, as this is intended for Access. I just meant to say that because my data comes from AS400 that I'm limited with how I can manipulate it in an SQL query. All I need is a working SWITCH statement.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Also tried it as an If statement and it still doesn't like greater than or less than symbols.

SQL:
Iif(MAX(HOUR) < 5, MAX(DATE) -1 DAY, MAX(DATE)) AS NEW_DATE,

This results in an error stating "Token < was not valid".
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
So ... when you say it is intended for Access, do you mean it is actually an access querying running in an Access database with data stored in an Access table? Or is that only what you intend to do at some point in the future?

As far as dates in access causing problems, you will have to confirm that the dates are stored in a real DateTime field and not stored as Date-like strings in a Text field.

Also, this just looks like an invalid expression: MAX(DATE) -1 DAY

Also Max(Date) is just the same as date when you are talking about a single row operation so I'm not even sure this is on the right track at all. :(
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

It might be more helpful to show the data as it exists in the table rather than as it shows up in a report ... hopefully your table doesn't have 24 fields in it one for each hour but that is of course entirely possible! Then your sql really gets fun!
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,301
Office Version
  1. 365
Further to xenou's comments, your SQL is not valid Access SQL. ACCESS SQL does not have a CASE statement.
Also, Date, Day, Hour are reserved words in Access.
Suggest you provide some details with respect to your database/application.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
Also since it may not be immediately obvious (and AS400 was mentioned) you sometimes find that when you connect to another type of database (back end) with MS Access (as a front end) - then the queries sometimes have to be written in the back dialect of sql (or in a neutral ODBC-compliant dialect). It gets a little confusing fast - but generally, Access SQL when you are working solely and completely 100% in Access and Access alone is in fact probably not going to work directly as is in other databases, and vice-versa - so it very important to know the setup (that is, if there is indeed some other backend database connection involved).

More specifically, the sql you wrote looks a little like half Access sql and half SQL Server sql - so probably not going to work on either one. CASE, for instance, is used by SQL Server (and some others), where as switch and Iif is somewhat unique to MSAccess.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Fixed it! And it was way easier than I thought! Rather than changing the date, I used a subquery in the WHERE clause to select the data from the next record for data collected after midnight and before 5AM. And @xenou, that has been my exact problem since I started this project.

SQL:
WHERE (((Table_All_Data.HMQTY) In (SELECT HMQTY - 1 FROM Table_All_Data WHERE HOUR < 5) AND (Table_All_Data.HMQTY) In (SELECT HMQTY FROM Table_All_Data WHERE HOUR >= 5))

Hasn't been an easy few weeks, but I'm so thankful to have all your great minds anxious to help others simply because you can :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,358
Messages
5,601,149
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top