Avatar of ES-Components
ES-ComponentsFlag for United States of America asked on

Can I Create A Conditional Select Statement In Access 2007?

I have attached an Excel spreadsheet of what my Access 2007 table looks like.
Currently I am displaying the results on a web page that has 3 select statements that produce results.
The first select statement gives me the "Total Sales" for the Week.
The second select statement gives me the "Total Sales" for the Day.
The third select statement gives me the "Detail Information" for the day.

1.) SELECT IIF(SUM(Sales) is NULL,0,SUM(Sales))
      FROM WeekBookTotal
      WHERE Expr1=-1

2.) SELECT SUM(Sales)
      FROM WeekBookTotal
      WHERE Day=Date()

3.) SELECT *
      FROM WeekBookTotal
      WHERE Day=Date()

My problem is, is if there are NO Bookings(Sales) today, (Select statements 2 & 3),
the Total for the week, (Select statement 1), does NOT display.

Is there a way of showing the total for the week even if there are NO sales today?

Any help would be greatly appreciated.

Thank you...
Rick
WeekBookTotal.xls
Microsoft Access

Avatar of undefined
Last Comment
ES-Components

8/22/2022 - Mon
datAdrenaline

First off, add this function to a Standard Module ...
Public Function FirstDOW(ByVal dtDate As Date, Optional intWeekBegin As Integer = vbUseSystemDayOfWeek) As Date
'Returns the first day of the week of the passed date.
    
    FirstDOW = DateSerial(Year(dtDate), 1, DatePart("y", dtDate, intWeekBegin) - (Weekday(dtDate, intWeekBegin) - 1))
        
End Function

Open in new window


Then ...

1) Summation of this week:
SELECT SUM(Sales) As ThisWeek
FROM
(
    SELECT Sales, [Day] FROM WeekBookTotal
    UNION ALL SELECT TOP 1 0 As Sales, Date() As [Day] FROM MSysObjects
) As vTbl
WHERE FirstDOW([Day], 0) = FirstDOW(Date(), 0)
 
2) Summation of today:
SELECT SUM(Sales) As ThisWeek
FROM
(
    SELECT Sales, [Day] FROM WeekBookTotal
    UNION ALL SELECT TOP 1 0 As Sales, Date() As [Day] FROM MSysObjects
) As vTbl
WHERE [Day] = Date()

Data for today:
SELECT *
FROM WeekBookTotal
WHERE [Day] = Date()


The reason for the UNION All in statements 1 and 2 is to provide AT LEAST ONE ROW for the Sum() aggregrate to work on, and since the row does nothing to the value aggregated by Sum(), its painless ... sort of like a "Nz()" for rowsets --- if no REAL rows, then add 0.  You can use any table with records (use one with a Primary Key) ... even WeekBookTotal if you like.  I often use MSysObjects because I know it will ALWAYS have records, but many people don't like to use system objects, so do what you feel best about.

If you have issues with implementing the VBA function for FirstDOW(), please let me know and I can provide you with a Query expression that does the same thing, or a sample db with FirstDOW() implemented.  Please note that the second argument of FirstDOW() being set to 0 means that the system definition for "first day of the week" will be used (most of the time, its Sunday for the U.S.)
ASKER
ES-Components

I am not at all familar with standard modules so I guess I would need a query to get this to work. Any additional help in the form of a query would be greatly appreciated.

Thank you...
Rick
datAdrenaline

Sure ... it may look convoluted though ...

1) Summation of this week:
SELECT SUM(Sales) As ThisWeek
FROM
(
    SELECT Sales, [Day] FROM WeekBookTotal
    UNION ALL SELECT TOP 1 0 As Sales, Date() As [Day] FROM MSysObjects
) As vTbl
WHERE DateSerial(Year([Day]), 1, DatePart("y", [Day], 0) - (Weekday([Day], 0) - 1)) =
       DateSerial(Year(Date()), 1, DatePart("y", Date(), 0) - (Weekday(Date(), 0) - 1))

The other query object SQL Views would not change.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
datAdrenaline

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ES-Components

Finally got it to work.
Great Job!!!!!!!!!!!!!!!!!!!!

Thank you very much.
Rick