Search results

totals query? Forget question 3, I've updated WorkedHours() to calculate it.

Don't worry, this isn't about parameters. I've searched the forum and can't find the answer to this (but loads on crosstab perameters!). I have a table: DateWorked | StaffPIN | StartTime | FinishTime | ShiftType ------------|-------- -|-------- --|-----------|----------...

I suffered similar problems to you with a similar database. My problems were solved by normalising my tables. The two .png's were intended to show you how I did this. I have not yet had any problems with this structure. I asked people to check it as I didn't want to give you flawed advice. I'm...

reading that back, I think I could get rid of the otherHours table, the ShiftType field of the Hours table would be sufficient to identify if the shift is chargeable or not if I put a chargeable/non-chargeable flag in the shiftType table . Thanks.

The Hours table records the hours that are chargeable to the client. ie, the hours that the staff members actuall spend doing their job. The 'other hours' table records the hours that the staff spend doing other stuff. This will include authorised/unauthorised absences, training hours, holiday...

I'm trying to do a similar thing, I spent ages on the 'old' db to no avail. I changed the table structure to the 'new' version. So far it's making things easier, can anyone spot any pit falls I might face? Edit: Have changed the new table structure since I posted this, nearly finished the...

Would this be any be any better?

With this example, can one employee work more than one day? It would also mean there'd be multiple entries for the same day. I'm not trying to argue. In my ignoance I don't quite follow you.

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...