create function problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...
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 said:
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 said:
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 said:
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.
Click to expand...
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
Click to expand...
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 *
Click to expand...
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 *
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
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 said:
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!

:

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
Click to expand...
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
Click to expand...
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",
Click to expand...
which
is
the query your recordset is based on. but what is [work details] referring
to?

hth


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
Click to expand...
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] /
Click to expand...
8 *
1.5
+
(rst![work details]![END TIME] - 24) * rst![work details]![SALARY]
Click to expand...
/ 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] /
Click to expand...
8 *
1.5
+
(rst![work details]![END TIME] - 24) * rst![work details]![SALARY]
Click to expand...
/ 8
* 2
Else
mysalary = 0
End If

rst.MoveNext
Loop Until rst.EOF

dailysalary = mysalary

End Function
Click to expand...
Click to expand...
Click to expand...
 
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?

Thanks again!

tina said:
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 said:
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 said:
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


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!

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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?

Thanks again!

tina said:
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 said:
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
Click to expand...
update
the
table by this query, could you please advise and teach me what I can
Click to expand...
do?
or
suggest any best solution?

Thanks so much!

:

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


Thanks, Tina.

Please ingore the "[work details]" (I have removed) and exactly
Click to expand...
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!

:

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
Click to expand...
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
Click to expand...
total",
which
is
the query your recordset is based on. but what is [work details]
referring
to?

hth


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
Click to expand...
details]![SALARY] /
8 *
1.5
+
(rst![work details]![END TIME] - 24) * rst![work
Click to expand...
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
Click to expand...
details]![SALARY] /
8 *
1.5
+
(rst![work details]![END TIME] - 24) * rst![work
Click to expand...
details]![SALARY]
/ 8
* 2

Else
mysalary = 0
End If

rst.MoveNext
Loop Until rst.EOF

dailysalary = mysalary

End Function
Click to expand...
Click to expand...
Click to expand...
 
It works perfectly.
Thanks soooooooooo much, Tina.

tina said:
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 said:
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?

Thanks again!

tina said:
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



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!

:

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


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!

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
you're welcome :)


Andy said:
It works perfectly.
Thanks soooooooooo much, Tina.

tina said:
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 said:
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?

Thanks again!

:

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
Click to expand...
table
at
all. it violates normalization rules, and always creates the
Click to expand...
potential
for
inconsistent - and therefore inaccurate - data. i'd strongly recommend
against it, in most cases.

hth



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
Click to expand...
can
do?
or
suggest any best solution?

Thanks so much!

:

I would like the value of dailysalary is going to equal the
Click to expand...
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
Click to expand...
within
the
loop, NOT after the loop is completed.

hth


Thanks, Tina.

Please ingore the "[work details]" (I have removed) and
Click to expand...
exactly
what
you
said the value of dailysalary is always going to equal the
Click to expand...
value
of
mysalary
*from the last record in the recordset*.

I would like the value of dailysalary is going to equal the
Click to expand...
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!

:

well, "it does not work" doesn't give us much to go on.
Click to expand...
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
Click to expand...
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


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top