Help w/ SQL Statement in Module

[ecugrad]

In a module, I'm trying to count 1/2 Sick Days and Sick Days(which would be a full day). The below statement will count Sick Days in the table. How do I change to count 1/2 sickdays as 1/2 and sick days as 1. Example in the table I have:

tblInput Table InputText field :

Sick Day
1/2 Sick Day

"I want to output 1 1/2 sick days"

strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText) Like '*Sick Day*'));"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtHolMo = rs!TotDays
rs.Close

 

[JohnLowell]

You need to Group By the Type:

strSQL = "SELECT Count(InputID) AS TotDays, "
strSQL = strSQL & "tblInput.InputText FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText) Like '*Sick Day*')) "
strSQL = strSQL & "Group By tblInput.InputText;"

 

[LLudden]

Dim dbs As Database, rst As Recordset
Dim fltSickDays As Single
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Count(DayStatus) as " & _
"Numberdays,DayStatus FROM tblTest1 WHERE DayStatus " & _
"LIKE '*Sick Day' GROUP BY DayStatus;&quot
rst.MoveFirst
fltSickDays = 0
Do While Not rst.EOF
fltSickDays = fltSickDays + rst.Fields(0).Value * _
IIf(rst.Fields(1).Value = "Sick Day", 1, 0.5)
rst.MoveNext
Loop
Debug.Print fltSickDays

 

[Durkin]

How about:

SELECT SUM(IIF(InputText = '1/2 Sick Day', .5, IIF(InputText = 'Sick Day', 1,0)) AS TotSickDays FROM tblInput WHERE Month(InputDate) = "& CInt(gstrMonth) & " AND tblInput.UserID = " & glngUserID [\code] Durkin

 

[Quehay]

Ecugrad,

It seems like a lot of work trying to interpret text as numeric values here. Why not use the input form to store 1 or .5 internally while showing users the options "Sick Day" or "1/2/ Sick Day"? The table will hold a Single datatype for the SickDays column. Then your query would be the much more intuitive SUM of these days.

The form could use a combo with a hidden bound column storing the numeric values and a display column with the text values. Or an option group within a frame with descriptive labels and values of 1 & .5.

Converting existing data would be easy with UPDATE queries.

Otherwise...


SELECT SUM(Switch([InputText]= "SickDay",1,"1/2 SickDay",.5))AS TotalDays from tblInput

 

[Quehay]

Switch returns a variant, so if the implicit conversion doesn't happen use CSng( ) outside of Switch.