Recent content by gRegulator

It works as long as the dates selected are on of before todays date. This is weird, but I will leave my code as is. We don't need to calculate stats for the future anyway, so this should be sufficient. Thanks for your help PC. Greg

Also, I have checked the date entries and I am positive there are no errors. This seems very confusing to me....

Here is an example of when dates are not picked up: If I type 1/1/2005 as start and 4/1/2005 as end, the numbers are correct. if I type 4/1/2005 as start and 3/31/2006 as end, the numbers are way off. I would expect this query to return hundreds of results, but it only returns 4! I...

Hi PC, Thanks for your reply. I have changed Admit to qry_q31_offence_types.Admit. I have checked all records and there are no null values. The dates are entered in the format mm/dd/yyyy. I am still having the same luck. I'm very confused because as you said, the SQL looks ok. What do...

I have the following SQL Query: SELECT qry_q31_offence_types.Offence, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients] FROM qry_q31_offence_types GROUP BY qry_q31_offence_types.Offence; I have been trying to put in date parameters but I have been out of luck. I have tried...

Yeah the sum is wrong. For example, If i enter the start date ([Forms]![frm_dateparameter_q31]![txtStart]) as 4/1/2005 and the end date ([Forms]![frm_dateparameter_q31]![txtEnd]) as 3/3/2006, I get values that are 2 , 3, etc, where the number should actually be more like 200 or so. For shorter...

Hi, I have the following SQL query. For some reason, the results it produces are very wrong. What I am trying to do is get a count of the types of offences committed by people (grouped by male and female) for clients admitted during a given time period. My query looks like this: PARAMETERS...

WOW! That is exactly what I needed. Thanks for that last step. I am not very familiar with the switch statement, so that helped alot! Tom, Thank you so much. Have a great day!

Tom, That seems to be what I'm looking for. Right now I have the code written as: PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime; SELECT MALE, FEMALE, Sum(IIf([Probation...

Hi, I have been using the following SQL code to find the average number of clients that are supervised over a selected time period. It works great. The date fields that it uses are [Probation Start] and [Probation Expiry]. PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime...

gol4, thanks again, i managed to have both male and female in there just by adding the female field to your sql statement to read: PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime; SELECT MALE, FEMALE, Sum(IIf([Probation...

gol4, That code you gave me works great, now i am just wondering if you can show me how to divide the data so it will break it down into male and female clients. Male and Female are options from the same table (tbl_offenders) and are check boxes. So instead of having a total average, i...

Hi, Thank you both for your advice. gol4, I used your suggestion and it gave me exactly what I was looking for! I really appreciate the help, that was one of the last things I have to finish on my database, this brings me one step closer! Thanks again and have a great day! Greg

Hi, I am trying to figure out how to calculate an average daily count of clients that we supervise. I have all my data stored in a Master table called tbl_offenders. From the tbl_offenders I would like the following fields: Male, Female, Probation Start, Probation Expiry. Where Male and...

I think thats on the right track... Each case load per month should reflect whether the cases are active or not. _______ SELECT 'Probation' AS ConditionDescription,Male, Female, [Probation Start] as [Start], [Probation Expiry] as [Expiry] FROM tbl_Offenders WHERE Probation = True UNION ALL...