Runtime Error '424' Object Required..

S

sgadow

New member
Local time
Yesterday, 23:24
Joined
Feb 15, 2012
Messages
9
I am nto so hot at VBA so this is probably a mess.. but what I am attempting to do is use this function to populate a field in a query.. but i get the runtime error 424 Object Required.. there are more elseif statements but they are all the same format and context.. any ideas?

Code: 
Public Function hourtotalprogress()
If (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 1) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48)
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 2) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * (1 * Month(Now()))
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 3) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * (1 * Month(Now()))
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 4) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * (1 * Month(Now()))
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 5) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * (1 * Month(Now()))
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 6) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * (1 * Month(Now()))
End If
End Function
 
In a sentence or 2 what exactly do you want to do?
 
ok well basically.. i have a query studenthourtotals that I would like to populate a field datehoursneeded based on the if then else statement i showed..

what this statement does is takes a kids graduation year from an existing table - the current year to give me a 0-3 (the rest of the if-then's i truncated to make the query shorter for yall)

once it finds the correct month/grad date it calculates how many hours they should have invested at that point in time based on the total they need to complete [Hours Req] so we can tell if they are on track to complete their hours by the time they graduate at their current pace..

the math at the end is not correct.. but that shouldn't effect the query..
 
Your calculation never changes and it's not a function as it doesn't return anything.

Code: 
Public Function HourTotalProgress(ByRef Grad_year As Integer, byref hours_req as integer) As Single

  If Grad_year = Year(Now()) Then
    
    hourtotalprogress = (hours_req / 48) * (Month(Now()))
    
  Else
  
    'value to return if the year isn't this year
    
  End If
  
End Function
Does exactly the same as your existing function.

If the calculation is going to change based on the month then do that test once:

Code: 
Public Function hourtotalprogress(ByRef Grad_year As Integer, byref hours_req as integer) As Single

  If Grad_year = Year(Now()) Then

    select case Month(now())
      case 1
         'do something
      case 2
        'do something
      etc
     case else
        'catch all for everything you don't specifically want
   end select

  Else
  
    'value to return if the year isn't this year
    
  End If
  
End Function

to use In a query:

Code: 
SELECT hourtotalprogress(student_info.Grad_year, studenthourtotals.[Hours Req]) as HoursTotalProgress
FROM etc etc etc
 
Last edited:
tehNellie said:
Your calculation never changes and it's not a function as it doesn't return anything.

If the calculation is going to change based on the month then do that test once:

Code: 
Public Function hourtotalprogress(ByRef Grad_year As Integer, byref hours_req as integer) As Single

  If Grad_year = Year(Now()) Then

    select case Month(now())
      case 1
         'do something
      case 2
        'do something
      etc
     case else
        'catch all for everything you don't specifically want
   end select

  Else
  
    'value to return if the year isn't this year
    
  End If
  
End Function
to use In a query:

Code: 
SELECT hourtotalprogress(student_info.Grad_year, studenthourtotals.[Hours Req]) as HoursTotalProgress
FROM etc etc etc
Click to expand...

I see where you are going with this I just can't seem to put it with what I have.. like with the case statement.. at any rate i think i confused you by not putting the full code.. so maybe this will help...

Code: 
Option Compare Database
Public Function hourtotalprogress()
If (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 1) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 33
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 2) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 34
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 3) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 35
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 4) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 5) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 6) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 7) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 8) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 9) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 10) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 11) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 0) And (Month(Now()) = 12) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 36
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 1) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 21
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 2) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 22
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 3) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 23
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 4) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 24
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 5) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 25
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 6) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 26
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 7) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 27
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 8) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 28
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 9) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 29
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 10) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 30
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 11) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 31
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 12) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 32
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 1) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 9
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 2) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 10
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 3) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 11
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 4) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 12
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 5) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 13
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 6) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 14
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 7) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 15
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 8) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 16
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 9) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 17
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 10) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 18
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 11) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 19
ElseIf (student_info.[grad_year] - Year(Now()) = 2) And (Month(Now()) = 12) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 20
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 1) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 1
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 2) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 1
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 3) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 1
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 4) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 1
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 5) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 1
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 6) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 2
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 7) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 3
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 8) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 4
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 9) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 5
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 10) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 6
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 11) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 7
ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 12) Then
studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 36) * 8
End If
End Function
 
Code: 
Public Function HourTotalProgress(Grad_year As Integer, hours_req As Integer) As Double
  Dim intMultiplier As Integer
  
  'check the graduation year against the current year and
  'act accordingly
  Select Case Grad_year - Year(Now())
   Case 0
      'if the month under 4 the mutliplier is 32 + the current month
      If Month(Now()) < 4 Then
        intMutiplier = 32 + Month(Now())
      Else
        'otherwise it's 36
        intMultiplier = 36
      End If
     
   Case 1
    intMultiplier = 20 + Month(Now())
    
   Case 2
      intMultiplier = 8 + Month(Now())
      
   Case 3
    'check the month and set the multiplier as appropriate.
    Select Case Month(Now())
    Case 1 - 5
      intMultiplier = 1
    
    Case 6 - 12
      intMultiplier = Month(Now()) - 4
    
    End Select
     
  Case Else
    ' do something if the year is more than 3 otherwise the answer will be 0
    
  End Select
  
  'return the result
  HourTotalProgress = (hours_req / 36) * intMultiplier

End Function

So what we're doing here is creating a function that returns a double.

You have a base calculation: (hours_req / 36) that is always performed.

There is a value that you multiply this by based on the year and the month.
The first case statement, looks at Grad_year minus the current year and then acts to work out the mutliplier according to the result.

I.e. if the value is 0, it goes to the case 0 section, if it's one the case 1 and so on. At the moment if the difference is more than 3 the function will return 0 because the multiplier is not being set, the Case Else statement can cater for that.

I'll I've then done is look at what you've set the multipliers to be and worked them out in a bit of code to save you having to write the same equation and test out 36 + times.

I think there's a smarter way to set the multiplier, but I haven't quite got my head around it yet.

Again to use this in a query you do exactly the same as before

Code: 
SELECT HourTotalProgress(tablename.Grad_year, tablename.Hours_req)
FROM tablename etc etc etc.
 
That didn't help much and as tehNellie said your function is ????

AS I understand things, there are students would are working away on their courses and are accumulating hours.
These students have a projected Graduation Date. For Graduation the students require a certain number of hours of effort/attendance.

You seem to be looking for method to determine if a student will have the required number of hours by Graduation, given his/her current accumulation rate.
Is that close?

Your "if else if " seems complex and there may be options.
 
jdraw said:
That didn't help much and as tehNellie said your function is ????

AS I understand things, there are students would are working away on their courses and are accumulating hours.
These students have a projected Graduation Date. For Graduation the students require a certain number of hours of effort/attendance.

You seem to be looking for method to determine if a student will have the required number of hours by Graduation, given his/her current accumulation rate.
Is that close?

Your "if else if " seems complex and there may be options.
Click to expand...

correct.. so for example the school year for my purposes runs from june to may so a freshman in june would be on month 1 so for example (had to change my math from my original code because it didn't work correctly)
Code: 
    ElseIf (student_info.[grad_year] - Year(Now()) = 3) And (Month(Now()) = 1) Then
  studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * 1
a freshman (this year) will graduate in 2015 (grad_year) and this query is run in january of current year so in my table said freshman who has required hours (Hours Req) of 400 divide that by the total number of months they will be in high school (48) and multiply it by the number of months that have passed since they started their career.. which in this case is 1 and the result is 8.33

in that same table there are other students so lets take a junior for example also needing 400 hours for the same query in January however 32 months have passed since they started their career because they are a junior.. so the result says they should have 266.66 hours at that time in order to be on track
Code: 
ElseIf (student_info.[grad_year] - Year(Now()) = 1) And (Month(Now()) = 1) Then
   studenthourtotals.[datehoursneeded] = (studenthourtotals.[Hours Req] / 48) * 32
the math works out great.. I just can't get the code to do what i need it to do i get that error..

here is how i have it set up..
I have a query studenthourtotals
from which i grab the hours req

a table student_info from which i grab the grad year

I would like to place this code to populate a field in my studenthourtotals query if possible. if i need to use a different query because I am grabbing a expression field [hours req]
Code: 
 Hours Req: Avg(IIf([manual_hours]=0,[hours_required],[manual_hours]))
from the studenthourtotals query i can do that as well it's not a big deal..

I am a complete noob at all of this really or at least i feel like one.. so if my questions have already been answered then please bear with me it is quite frustrating to say the least lol..

I guess unless its impossible for it to work i am not really looking for someone to come up with an alternate way of doing it.. I just am looking if it is possible how to get my if then else statement to populate a query field..
 
Last edited:
Code: 
Function HourTotalProgress(ByRef Grad_year As Integer, ByRef hours_req As Integer) As Double
  Dim dblPercentComplete As Double
  Dim IntMonthsFromGraduation As Integer

  
  'Find out how many months away from graducation
  IntMonthsFromGraduation = DateDiff("m", Now(), CDate("01/05/" & Grad_year))
  
  'set the % of course months complete
  dblPercentComplete = (36 - IntMonthsFromGraduation) / 36
  
  
  If dblPercentComplete < 0 Then ' They Haven't started yet
    dblPercentComplete = 0
  ElseIf dblPercentComplete > 1 Then ' they should have left.
    dblPercentComplete = 1
  End If

  'return the value
  HourTotalProgress = hours_req * dblPercentComplete

   
End Function

right, easier version. Works out the percentage of months completed until graduation (based on May, that can be altered) and returns number of hours that should have been completed to date.

I had originally set April as the Final month as that matched your calculations, but you seem to imply that May is their final month.

The initial dblpercentComplete is based on a 36month course, you could replace the two 36s with an integer variable to cater for courses that run longer/shorter.

eg

Code: 
Function HourTotalProgress(ByRef Grad_year As Integer, ByRef hours_req As Integer, byref intCourseMonths as integer) As Double
  Dim dblPercentComplete As Double
  Dim IntMonthsFromGraduation As Integer

  
  'Find out how many months away from graducation
  IntMonthsFromGraduation = DateDiff("m", Now(), CDate("01/05/" & Grad_year))
  
  'set the % of course months complete
  dblPercentComplete = (intCourseMonths - IntMonthsFromGraduation) / intCourseMonths 
  
  
  If dblPercentComplete < 0 Then ' They Haven't started yet
    dblPercentComplete = 0
  ElseIf dblPercentComplete > 1 Then ' they should have left.
    dblPercentComplete = 1
  End If

  'return the value
  HourTotalProgress = hours_req * dblPercentComplete

   
End Function
 
Last edited:
Ok.. using your first code and changing the 36 to 48.. I think i also need to reference the table/query location of the parameters don't i like below?.. just adding the x.y don't work.. my ( are off now i think.... also what would i put in the query field?

Code: 
Function HourTotalProgress(ByRef student_info.grad_year As Integer, ByRef studenthourtotals.hours_req As Integer) As Double
  Dim dblPercentComplete As Double
  Dim IntMonthsFromGraduation As Integer

  
  'Find out how many months away from graducation
  IntMonthsFromGraduation = DateDiff("m", Now(), CDate("01/05/" & student_info.grad_year))
  
  'set the % of course months complete
  dblPercentComplete = (48 - IntMonthsFromGraduation) / 48
  
  
  If dblPercentComplete < 0 Then ' They Haven't started yet
    dblPercentComplete = 0
  ElseIf dblPercentComplete > 1 Then ' they should have left.
    dblPercentComplete = 1
  End If

  'return the value
  HourTotalProgress = studenthourtotals.hours_req * dblPercentComplete

   
End Function
 
Last edited:
Do you have students in both 36 and 48 month courses?
If so, you could add a parm to the function. If not, ignore the statement.

Are you still getting an error from Access, or is it a math issue?
 
sgadow said:
Ok.. using your first code and changing the 36 to 48.. I think i also need to reference the table/query location of the parameters don't i like below?.. just adding the x.y don't work.. my ( are off now i think.... also what would i put in the query field?
Click to expand...

You don't need to touch the function at all, just copy and paste it into a Module and pass the parameters to it from the query as per the example before.

Code: 
SELECT HourTotalProgress(tablename.Grad_year, tablename.Hours_req, NumberofMonths) as TargetHours
FROM tablename etc etc etc.

Trying to put variable names with period (".") separators in will cause problems as they're not valid syntax. If you want to rename them, don't use special characters.
 
Last edited:
tehNellie said:
You don't need to touch the function at all, just copy and paste it into a Module and pass the parameters to it from the query as per the example before.
Click to expand...
ok got it have an error though

Wrong number of arguments used with function in query expression
Code: 
'HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req], NumberofMonths)'
Where does NumberofMonths come from? (if i replace that with 48 i still get the same error btw)

Code: 
SELECT HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req, NumberofMonths) as TargetHours,  student_info.student_number, studenthourtotals.Fullname,  studenthourtotals.SumOfHours, studenthourtotals.grad_year
FROM student_info INNER JOIN studenthourtotals ON student_info.student_number = studenthourtotals.student_number;

and no they are all 48 I was told incorrectly before
 
This
'HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req], NumberofMonths)'

should NOT have the ]
 
jdraw said:
This
'HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req], NumberofMonths)'

should NOT have the ]
Click to expand...

that was a typo when i was retyping it.. it's not in the query
 
Right. Stop.

Step 1. replace whichever function you have currently with this one, which takes the graduation year, Required Hours and the duration in months of the course as parameters:

Code: 
Function HourTotalProgress(ByRef Grad_year As Integer, ByRef hours_req As Integer, byref intCourseMonths as integer) As Double
  Dim dblPercentComplete As Double
  Dim IntMonthsFromGraduation As Integer

  
  'Find out how many months away from graducation
  IntMonthsFromGraduation = DateDiff("m", Now(), CDate("01/05/" & Grad_year))
  
  'set the % of course months complete
  dblPercentComplete = (intCourseMonths - IntMonthsFromGraduation) / intCourseMonths 
  
  
  If dblPercentComplete < 0 Then ' They Haven't started yet
    dblPercentComplete = 0
  ElseIf dblPercentComplete > 1 Then ' they should have left.
    dblPercentComplete = 1
  End If

  'return the value
  HourTotalProgress = hours_req * dblPercentComplete

   
End Function

You do not need to touch this code at all. The only part of it that might need adjusting is the "01/05/" bit which assumes the final month where hours are accrued during the course, before graduation, is May.

To call this function in your query the following syntax is used.

Code: 
SELECT HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req, [b]NumberofMonths[/b])

The number of months is in bold because you've not made it clear whether this is a value stored in your tables or not. You can manually put a figure in here if you want:

Code: 
SELECT HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req, [b]36[/b])

Or it can come from a table:

Code: 
SELECT HourTotalProgress(student_info.grad_year, studenthourtotals.hours_req, [b]student_info.courseLength[/b])
I've used student info in the example just because...

I don't know your data structure.
 
ok must have had something messed up now it works :) i am so sorry i probably frustrated you guys to all hell..

ok well it kind of works..

Query1
TargetHours student_number grad_year
 208.333333333333 1320002 2014
400 1270009 2012
308.333333333333 1270012 2013
208.333333333333 1320070 2014
 308.333333333333 1320080 2013
400 1320095 2012
308.333333333333 1320105 2013
400 1320106 2012
 208.333333333333 1370040 2014
208.333333333333 1320110 2014
 400 1330124 2012
208.333333333333 1220130 2014
400 1320150 2012
208.333333333333 1320160 2014
108.333333333333 1220190 2015
 108.333333333333 1320180 2015
308.333333333333 1320200 2013
400 1320240 2012

I am running this in febuary.. so target hours for someone graduating in 2012 should be around the 375-380 range not at 400 yet.. 2013 should be around 275-280 2014 should be around 170ish and 2015 around 70ish)


Code: 
TargetHours: HourTotalProgress([student_info].[grad_year],[studenthourtotals].[hours_req],48)
with the code left as you put it
 
nevermind I figured it out.. the date was set for january 5th i thought it was the 1st of may.. so i swapped it and it works fine now :)

you guys are amazing thank you so so much
 
debug.Print HourTotalProgress(2012,400,48)

= 375

debug.Print HourTotalProgress(2013,400,48)
= 275

debug.Print HourTotalProgress(2014,400,48)
= 175

debug.Print HourTotalProgress(2015,400,48)
= 75.

I'm going to blame your data :D

Never had so much problem with 3 functional lines of code before. I even ran the calcs manually in Excel just to make sure I wasn't losing my mind :)
 
sgadow said:
nevermind I figured it out.. the date was set for january 5th i thought it was the 1st of may.. so i swapped it and it works fine now :)

you guys are amazing thank you so so much
Click to expand...

That'd do it, perish the thought we'd all agree on a common date format :D
 
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
491
June7
June7
S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
571
Pat Hartman
P
B
Solved Error 6 Overflow
2
Replies
26
Views
1,105
Gasman
Gasman
jwcolby54
VBA and ChatGPT
2 3
Replies
55
Views
2,576
Uncle Gizmo
Uncle Gizmo
C
When accHitTest generates an 'invalid procedure call or argument' error
Replies
4
Views
463
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom