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

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

ES-Components

ASKER

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.

ASKER CERTIFIED SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ES-Components

ASKER

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

Thank you very much.
Rick