creating and calling a function...

  • Thread starter Thread starter Erin Freeman
  • Start date Start date
E

Erin Freeman

Hi,

I am wanting to create a function to call from an autoexec macro so it
checks this upon startup..

I have 2 currency fields and 2 date fields,

The code that i want to be in the function is:
If RateChangeDate < IDate then
DBillablerate = CurrentRate
End if

Is this how I would write the function?

Public Function RateChange(RateChange Date As Text, IDate as
text,DBillableRate as text,CurrentRate as text) As String
 
Erin,
You will need to use queries to lookup the values for RateChangeDate ,
IDate, CurrentRate and set DBillablerate.
Do you have a table that stores the value for CurrentRate and DBillablerate
?

This is pseudo code:

Public Function RateChange()
If DLookup the RateChangeDate <Dlookup IDate
update a setting in a table that makes DBillableRate equal to Dlookup the
CurrentRate.
Else
'more code here
End If
End Function

Jeanette Cunningham
 
Hi,

I have a table called billing change where the RateChangeDate Resides

I have a table called hospitaldepts where the billable and current rate
fields sit

and I have a table called invoice where the I-Date field sits,

we need to change the rate of what we bill the hospital but i dont want it
to affect any old invoices.
 
This is the code but it doesnt work, i dont really need an else statment (i
dont think) the else would be that billable rate would just = billable rate
and not the current rate....


If DLookup("[Dept Rate Change Date]", "Billing Change") > DLookup("[IDate]",
"Invoice") Then
DLookup("[D Billable Rate]", "Hospital Specific Departments1") = DLookup("[D
Current Rate]", "Hospital Specific Departments1")
 
Erin,
doesn't work isn't much to go on.

Put these 2 lines above your code

Debug.Print "rate change date" & DLookup("[Dept Rate Change Date]",
"Billing Change")
Debug.Print "IDate" & DLookup("[IDate]"

run the form, press Ctl + G to open the immediate window
see what access finds for rate change date and idate
Can you see any problems with comparing the dates?

Jeanette Cunningham

Erin Freeman said:
This is the code but it doesnt work, i dont really need an else statment
(i
dont think) the else would be that billable rate would just = billable
rate
and not the current rate....


If DLookup("[Dept Rate Change Date]", "Billing Change") >
DLookup("[IDate]",
"Invoice") Then
DLookup("[D Billable Rate]", "Hospital Specific Departments1") =
DLookup("[D
Current Rate]", "Hospital Specific Departments1")

Erin Freeman said:
Hi,

I am wanting to create a function to call from an autoexec macro so it
checks this upon startup..

I have 2 currency fields and 2 date fields,

The code that i want to be in the function is:
If RateChangeDate < IDate then
DBillablerate = CurrentRate
End if

Is this how I would write the function?

Public Function RateChange(RateChange Date As Text, IDate as
text,DBillableRate as text,CurrentRate as text) As String
Click to expand...
Click to expand...
 
Erin,
If you had a field for billing rate in the table ( I'm not sure which
table - but you will know), and this rate is used in the invoice amount
calculation then old invoices would use the old rate and new invoices would
use the new rate and so on each time the rate changes.

Jeanette Cunningham
 
That is exactly what I would like to do, the billing rate and the "old rate"
both reside in the hospital department table, i just cant seem to wrap my
head around how to do it...

Thanks for your help
 
Erin,
There is a table where you record the service provided, how many hours or
item no of the service.
If you have a field for the rate and store the rate in the table with the
above, you can always calculate the correct cost of the invoice, using the
same rate that is stored with the other service details.
When you are adding a new record to this table, your form looks up the
correct rate from the hospital department table and stores it with the
record.

Jeanette Cunningham
 
If you have a table for storing the rates and the dates they apply to, you
can look up the correct rate to use.
I don't know your application setup - this is a suggestion.
The rate table has 2 fields - Rate and DateApplied
The original rate and the date you started using that date are the first
record.
The next record is the new rate and the date you started using that date.

When your form needs to know the rate to use for a new record, it looks up
the most recent date.
You can set up a query with rate, date applied and get the max date.
The rate corresponding to the max date is the one to use when adding new
invoice details.

Jeanette Cunningham
 
I think you're making this a lot harder than it has to be. Why are you
worried about changing records that are already in the table? Just use the
new billing rate when you write a new invoice and that billing rate will stay
with that invoice. Unless the problem lies in your table structure... which
sounds highly likely. Are you not storing the billing rate information on
each invoice record? If not, that would be the first issue you should correct.
 
Back
Top