how should i do this...

[mission2java]

Im having a difficult time trying to approach this. Basically I have a form which allows the user to enter rates of pay for mon-fri, for sat., and for sun, also one for the holidays. Now basically I have a query which queries out job numbers in our workplace which are place in a table where all jobs have been worked on. Along with the job number there is a date and the number of hours worked on the job for that date. Now I query this information out but I need a way to get on a report the total # of hours along with the total cost. What im having trouble with is how do I, once I get the query figure out the cost for each day? Do I do this code in a report basically i need to do the following:
query the table of jobs, which will return to me all the records with the job number, hours, and date
then I need to be able to do something like this
for each record in the query
Select case 'find out what day it is
case monday
total=total + theRateOnMonday 'rate on mon from the form
case tuesday
total= total + theRateOnTuesday
....
case holiday
total = total + theRateForAHoliday

Finally I have to show this total on the report...but where do I do all this coding. Can anyone offer help or samples. Thanks

 

[DoogieB]

This is rather off-the-cuff, but I have a couple of approaches that come to mind.
1) Use the DLookup() function to get some of the calculations you need on your report.
2) Use VBA code to create a recordset of the source data. Loop through that data record by record, make the calculation you need, write that record to a teporary table using DoCmd.RunSQL. It sounds like your calculation rate will depend on what day of the week it is for. With more that two options (what the IIF function is for) I usually resort to coding a function that does it.

Hope this helps.

 

[LonnieJohnson]

You could also put a flag in your table that tells you what is weekend and what is holiday. The weekend dates can be found by adding a column in a query like so...

Expr1: Format([MyDate],"w&quot

All dates that show a 1 or 7 in this new column would be weekend dates. The holiday thing is more a manual process.

Hope this helps... ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[braindead2]

you are on the right track. You need to create a function in the module section
your problem becomes tracking the holidays
you will need to have a table that Lists all of the Holidays and then if the date is in that table it is a holiday
'this is example only typed on the fly will need serious rework
public function ChargeRate(workdate as variant)as integer

if dlookup("[dateholiday]","holidaytbl","dateholiday = #" & workdate &"#&quot then
workdate = holidayrate
else
Select case weekday(workdate)
case 1
chargerate = theRateOnSunday 'get using dlookup
case 7
chargerate = theRateOnSaturday
case else
chargerate = weekdayrate ' or one for each day
end select
end if
end function

then you would use it like
total = total + Chargerate(dateyouarepassing)
good luck hopefully this gives you the idea