I knew this going to be difficult! (1 Viewer)

[lightray]

Hi everyone, I appreciate all the responses to Posts, I have certainly learnt a few things since joining. Thanks

I have a form that calcs leave values for an employee. These calcs work fine on the form. So I thought I could use them in a report. I take it that the DSum function on a query works alright from a report?

Anyway I'm getting the dreaded 'Error 2001 - You canceled the previous Operation. I've done spurious research on this error and can't see where it's nobbling me. unless it's the DSum issue I mentioned above

Also how does one post code in a window that I have seen?
My calcs are: (Error line in Red)
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

' Run Calcs here for Leave other than Sick Leave.
If IsNull(EmpStartDate) Then
DaysWorked = 0
Else
DaysWorked = DateDiff("d", EmpStartDate, Now)
End If
' Calc number of days between Employee Start Date and Now

TotalLeaveAlloc = DaysWorked * (Me!AnnualLeaveDue / 365)
' Me.AllDaysWorked = TotalLeaveAlloc 'Just to see what it is!
' Divide Allocated days by 365 and multiply by number of days worked
' to get number of holidays due in total

If IsNull(DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then
Me.Taken01 = 0
Else
Me.Taken01 = DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")
End If
' Calculates the number of leave days taken from leave records in database based on qryLeaveRecords
' Used IsNull to check if there are no records found - set calc result to zero
' Display this on screen

If IsNull(Me.LeaveAccrued) Then
Me.Bal01 = TotalLeaveAlloc - Me.Taken01
Else
Me.Bal01 = TotalLeaveAlloc - (Me.Taken01 + Me.LeaveAccrued)
End If
' Check if there is any Accrued Leave [reduces the need to load historical Leave Records] and subtract them
' Display Balance

' Run Calcs for Sick Leave here.
If IsNull(DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then
Me.Taken02 = 0
Else
Me.Taken02 = DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")
End If
' Calculates the number of leave days taken from leave records in database based on qrySickLeaveRecs
' Used IsNull to check if there are no records found - set calc result to zero
' Display this on screen

Me.Bal02 = Me.SickLeaveDue - Me.Taken02
' Display Sick Leave Balance

End Sub
Many thanks

 

[gemma-the-husky]

-------------------------------------------------
your where clause looks wrong. I think it should be

"[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = " & [tblLeaveRecord]![EmployeeID]"

The last bit returns a value, and shouldn't be inside the quote marks

 

[lightray]

Thanks for your response gemma, unfortunately that didn't fix it. Placing the double quote on the end generated a Compile error: Expected: list separator or ) (... so I smiled ...)

If I leave it out I get a valid syntax but on Run I get Run-time error 2465: MSA can't find the field '|' referred to in your expression, and it stops on the same line.

Here is the code for my simple query, all the fields are there!

SELECT tblLeaveRecord.EmployeeID, tblLeaveRecord.AppNo, tblLeaveRecord.AppDate, tblLeaveRecord.LeaveType, tblLeaveRecord.StartDate, tblLeaveRecord.EndDate, tblLeaveRecord.DaysTaken, tblLeaveRecord.Comments
FROM tblLeaveRecord
ORDER BY tblLeaveRecord.AppDate DESC;

The original posted code works fine on the form, all values generated correctly, but won't work in the report.

Any further help will appreciated, otherwise end result will have to be form based

 

[gemma-the-husky]

sorry my posting was obviously wrong - the last quote shouldn't be there. I edited it out now.

thinking about it now - the bit of the expression
"and [tblEmployees]![EmployeeID] = " & [tblLeaveRecord]![EmployeeID] is trying to assign in your source query a selection criteria that sets [tblEmployees]![EmployeeID] equal to a variable, in this case [tblLeaveRecord]![EmployeeID].

On reflection this variable name probably makes no sense. It should be a variable coming from a form controlling the report, or something similar. I'm not sure where you are taking it from.

 

[lightray]

Hey! I think you are right! Gemma, this is why it works on the form.

This should be part of my DoCmd routine that picks up the main parameters to run the report.

I'd better get on in that direction. Will post back on result 8^)
Thanks for the focus

 

[damonc]

Also how does one post code in a window that I have seen?

Do you mean posting code like this?:

Your code goes here

If you what to do that, simply highlight the text you want to appear in the box, and click the # button on the WYSIWYG toolbar.

As for your Access problem, I can't help sorry!

 

[lightray]

Yep! that was what I was meaning. This might sound a little odd, but at the moment I access this forum on my Mac computer. My Web Browser is Safari. What's the best browser to use for the WYSIWYIG toolbar?
It's a bit back and forward at the moment, but at least I have a switch to share the monitor, keyboard and mouse.
Thanks for your response. damon

 

[damonc]

What's the best browser to use for the WYSIWYIG toolbar?

I think any browser should display the editor correctly. I only use Firefox, and everything works perfectly.

 

[lightray]

Nearly Solved

Hey, thanks to all who responded. This is close to being solved by another thread and a different direction, thanks to Pat Hartman

http://www.access-programmers.co.uk/forums/showthread.php?t=116839

 

[lightray]

You're right of course - just needed a little careful observation in the right place. Light-bulb went on! and I saw it. Thanks 8^)

 

[damonc]

That's good. Glad you got it worked out!