Search results

I can't see how I could. Think I'm in a real mess with this though. Don't want to spend more time on it if it's fundamentally wrong. Could anyone please help?

I want to combine the data from 2 tables in a report. I think I need to do this through a crosstab query, but I've no idea how as it needs to calculate it's data. The row heading needs to be [DateWorked] from the "Hours" table. Each Column heading needs to be every [StaffPIN] from the...

[Forms].[Recalc] in the On Click event property will cause all the statements on the form to recalc. (the one's that depend on other objects in the form anyway) [Forms].[Refresh] will also refresh any records that the form links to. more on these in access help.

Still can't get this to work. I've narrowed down the problem further and here's a simplified example of the problem. Public Function Test(workedDate As Date) Test = DLookup("[NoOfStaffRequired]", "Hours", "[DateWorked] = #" & workedDate & "#") End Function This function works in the...

Thanks, nearly solves it. It still evaluates it as '#error' but it doesn't show an 'invalid use of null' anymore. And it doesn't debug my code any more. Now I just have to work out whats causing this new error. Thanks again.

I'm getting an error when using a function in a calculated field of my query. In the query there's a field call [DateWorked], it get's it data from a field in a table that stores short dates. There's a between statement in it's criteria. The calculated field that generates the error is ...

Thanks but I don't want it bypassed. I want it to execute. I think that's what's happening. The argument passed to the function is used in the criteria for the DLookUp. When I call the function in the immediate window like: dayMissedHours(#01/11/07#) it works dayMissedHours(01/11/07) doesn't...

I started a thread in the VBA section about a problem with a function I'd written. I think it's more to do with how I'm implementing it in a query. http://www.access-programmers.co.uk/forums/showthread.php?t=139066 Please help.

Looked in to it a bit further: Public Function dayMissedHours(WorkedDay As Date) Dim s1 As Integer If DLookup("[NoOfStaffRequired]", "hours", "[DateWorked] = #" & WorkedDay & "#") >= 1 And Not (IsNull(DLookup("[Staff1PIN]", "hours", "[DateWorked] = #" & WorkedDay & "#"))) Then 'If staff is...

Edit: 6th post (5th reply) is simpler version of the problem This function works in the immediate window of the VB builder. When I use it in a query it throws up an Invalid use of null error for each reccord. Public Function dayMissedHours(WorkedDay As Date) Dim s1 As Integer If...

I think I've solved it: Dim n As Integer Dim fieldList(6) As String Dim I As Integer fieldList(0) = "[S1Type]" fieldList(1) = "[S2Type]" fieldList(2) = "[S3Type]" fieldList(3) = "[S4Type]" fieldList(4) = "[S5Type]" fieldList(5) = "[S6Type]" fieldList(6) = "[S7Type]" n = 0 For I = 0 To 6...

been thinking... could I use a For Each loop such as, storing the list of fields that I want to check (and count each 'not null' value) in an array then: For Each 'fieldName in an array' If (look up current fieldName from For loop, and record index from function args) is not null then...

I need to wite a function that will count the number of field in a record that are not null. I don't want it to count all fields that are not null, just the fields specified in the function's code. The record's index is passed to the function as an argument. The solutions that I think I've...

It worked Thanks (again) :)

This line keeps throwing an error: DLookup("[NoOfStaffRequired]", "Hours", "[DateWorked] = " & WorkedDay) WorkedDay is a variable passed as a Short Date in to the function. It is Declared As Date. The [DateWorked] field in the 'Hours' table contains Short Dates. The error I keep getting...

Solved it (I think) using a very helpful tutorial http://www.fontstuff.com/vba/vbatut01.htm Function MissedHours(StartTime As Date, EndTime As Date, ShiftLength As Integer) Dim Hbefore12 As Date 'Declare variables Dim Hafter12 As Date Dim HoursWorked As Integer If StartTime > EndTime...

:) Thanks. I used 'Recalc' as I think 'Refresh' refreshes the form and everything the form is linked to (such as records), 'Recalc' just recalculates the expresions in the form. It still doesn't like the 'Me' bit, it says it can't find the object called 'Me'. I had to use '=[Form].[Recalc]'

Me.Refresh doesn't work. It thinks it's an object.

sorry, I have a table that records the number of staff required per day, each staff member's ID, start and finish time (just time, not time and date), and the shift type. It is indexed by the date the shift was started. There is a seperate shift-type look-up table that stores each shift type...

I'm new to Access, so please be patient. I'm trying to write an expresion that will work out how many hours were missed from a given shift, using the start and finish times, how long the shift was ment to be, and how many staff showed up compared to how many were ment to. The expression is...