create function problem

[Andy]

Hi there,

I have a query "salary total"(2 tables) , I would like to create a function
to calculate the salary but it does not work, code as follows. Appreciate for
your expert help!

Function dailysalary()

Dim mysalary As Single
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef



Set db = CurrentDb
Set qdf = db.QueryDefs("salary total")
Set rst = qdf.OpenRecordset

Do

If rst![work details]![START TIME] > 8 And rst![work details]![START TIME] <
13 And rst![work details]![END TIME] > 13 And rst![work details]![END TIME] <
19 Then
mysalary = (13 - rst![work details]![START TIME] + rst![work details]![END
TIME] - 14) * rst![work details]![SALARY] / 8

ElseIf rst![work details]![START TIME] > 8 And rst![work details]![END TIME]
< 14 Then
mysalary = (rst![work details]![END TIME] - rst![work details]![START TIME])
* rst![work details]![SALARY] / 8

ElseIf rst![work details]![START TIME] > 13 And rst![work details]![END
TIME] < 19 Then
mysalary = (rst![work details]![END TIME] - rst![work details]![START TIME])
* rst![work details]![SALARY] / 8

ElseIf rst![work details]![START TIME] > 8 And rst![work details]![START
TIME] < 13 And rst![work details]![END TIME] > 13 And rst![work details]![END
TIME] > 19 And rst![work details]![END TIME] <= 24 Then
mysalary = (13 - rst![work details]![START TIME] + 18 - 14) * rst![work
details]![SALARY] / 8 + (rst![work details]![END TIME] - 18) * rst![work
details]![SALARY] / 8 * 1.5

ElseIf rst![work details]![START TIME] > 8 And rst![work details]![START
TIME] < 13 And rst![work details]![END TIME] > 13 And rst![work details]![END
TIME] > 19 And rst![work details]![END TIME] > 24 Then
mysalary = (13 - rst![work details]![START TIME] + 18 - 14) * rst![work
details]![SALARY] / 8 + (24 - 18) * rst![work details]![SALARY] / 8 * 1.5 +
(rst![work details]![END TIME] - 24) * rst![work details]![SALARY] / 8 * 2

ElseIf rst![work details]![START TIME] > 13 And rst![work details]![END
TIME] > 18 And rst![work details]![END TIME] <= 24 Then
mysalary = (18 - rst![work details]![START TIME]) * rst![work
details]![SALARY] / 8 + (rst![work details]![END TIME] - 18) * rst![work
details]![SALARY] / 8 * 1.5

ElseIf rst![work details]![START TIME] > 13 And rst![work details]![END
TIME] > 18 And rst![work details]![END TIME] > 24 Then
mysalary = (18 - rst![work details]![START TIME]) * rst![work
details]![SALARY] / 8 + (24 - 18) * rst![work details]![SALARY] / 8 * 1.5 +
(rst![work details]![END TIME] - 24) * rst![work details]![SALARY] / 8 * 2

Else
mysalary = 0
End If

rst.MoveNext
Loop Until rst.EOF

dailysalary = mysalary

End Function

[tina]

well, "it does not work" doesn't give us much to go on. lacking any details
of the problem you're seeing, my only comment is that you're looping through
one or more records in the recordset and setting the value of mysalary on
each pass - but not putting that value anywhere. so the value of dailysalary
is always going to equal the value of mysalary *from the last record in the
recordset*.

beyond that, i'm lost on where you're getting rst![work details]![START
TIME]. presumably [START TIME] is a field in query "salary total", which is
the query your recordset is based on. but what is [work details] referring
to?

hth

"Andy" <An...@discussions.microsoft.com> wrote in message
news:F5A0B87A-083B-46EF...@microsoft.com...

[Andy]

Thanks, Tina.

Please ingore the "[work details]" (I have removed) and exactly what you
said the value of dailysalary is always going to equal the value of mysalary

*from the last record in the recordset*.

I would like the value of dailysalary is going to equal the value of
mysalary *from EACH record in the recordset* show in the query "salary total"
but I do not know what I can do?? Very appreciate for your expert advise!!!

Thanks again!

[tina]

> I would like the value of dailysalary is going to equal the value of
> mysalary *from EACH record in the recordset* show in the query "salary
total"

*how are you going to use each value* as you retrieve it from each record?
if you're going to do something with it, then you must do it within the
loop, NOT after the loop is completed.

hth

"Andy" <An...@discussions.microsoft.com> wrote in message

news:4D24F624-928F-4EF5...@microsoft.com...

[Andy]

Have added the new line within the loop as below :
Do
.....
.....
rst![daily total] = mysalary 'add new line here

rst.MoveNext
Loop Until rst.EOF
.........
.........

But find the error message :
"error#3027, the query can not be updated"

Have mentioned that the query is based on 2 tables, so I can not update the
table by this query, could you please advise and teach me what I can do? or
suggest any best solution?

Thanks so much!

[tina]

well, you could open a second recordset on the one table, and update that;
it would probably involve doing a Find action to get to the appropriate
record in the second recordset, within each loop of the first recordset.

but, generally speaking, you shouldn't store calculated data in a table at
all. it violates normalization rules, and always creates the potential for
inconsistent - and therefore inaccurate - data. i'd strongly recommend
against it, in most cases.

hth

"Andy" <An...@discussions.microsoft.com> wrote in message

news:D74BE274-F764-4A0A...@microsoft.com...

[Andy]

Sorry for the confusion! Tina,
Actually I would like the calculated filed [daily total] show on the query.

For example :
[daily total] : dailysalary()

It does not work as per the error message#3027,
I believe that I have something wrong, could you please correct me?

[tina]

okay, you want a custom function that returns a calculated value to each
record in the query's recordset. forget opening recordsets and using
querydefs in the function - you already have the data available within the
query. just pass the data to the public function using arguments, calculate
the value, and return it to the query, as

Public Function dailysalary(ByVal sglStart As Single, _
ByVal sglEnd As Single, ByVal sglSalary As Single) As Single

If sglStart > 8 And sglStart < 13 And _
sglEnd > 13 And sglEnd < 19 Then
dailysalary = (13 - sglStart + sglEnd - 14) * sglSalary / 8
ElseIf sglStart > 8 And sglEnd < 14 Then
dailysalary = (sglEnd - sglStart) * sglSalary / 8
ElseIf sglStart > 13 And sglEnd < 19 Then
dailysalary = (sglEnd - sglStart) * sglSalary / 8
ElseIf sglStart > 8 And sglStart < 13 And _
sglEnd > 13 And sglEnd > 19 And sglEnd <= 24 Then
dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 +
(sglEnd - 18) * sglSalary / 8 * 1.5
ElseIf sglStart > 8 And sglStart < 13 And _
sglEnd > 13 And sglEnd > 19 And sglEnd > 24 Then
dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 + (24 -
18) * sglSalary / 8 * 1.5 + (sglEnd - 24) * sglSalary / 8 * 2
ElseIf sglStart > 13 And sglEnd > 18 And sglEnd <= 24 Then
dailysalary = (18 - sglStart) * sglSalary / 8 + (sglEnd - 18) *
sglSalary / 8 * 1.5
ElseIf sglStart > 13 And sglEnd > 18 And sglEnd > 24 Then
dailysalary = (18 - sglStart) * sglSalary / 8 + (24 - 18) *
sglSalary / 8 * 1.5 +
(sglEnd - 24) * sglSalary / 8 * 2
Else
dailysalary = 0
End If

End Function

make each equation (dailysalary = "a math expression") run on one line,
regardless of linewrap here.

add the calculated field to the query's Design grid, as

[daily total]: dailysalary([START TIME], [END TIME], [SALARY])

hth

"Andy" <An...@discussions.microsoft.com> wrote in message

news:6AEFCAF8-4D49-41FC...@microsoft.com...

[Andy]

It works perfectly.
Thanks soooooooooo much, Tina.

[tina]

you're welcome :)

"Andy" <An...@discussions.microsoft.com> wrote in message

news:F1C8C7C3-D9C8-44FD...@microsoft.com...