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
ES-ComponentsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

datAdrenalineCommented:
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.)
0
ES-ComponentsAuthor Commented:
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
0
datAdrenalineCommented:
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.
0
datAdrenalineCommented:
Have you had a chance to give my suggestion a try?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ES-ComponentsAuthor Commented:
Finally got it to work.
Great Job!!!!!!!!!!!!!!!!!!!!

Thank you very much.
Rick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.