Help!!!

[mtagliaferri]

I have a form containing worked hours and a date field, I need to obtain a result by multiply the hours worked by a value that is contained in another table called "rate", the tabel rate has the field datefrom, dateto, rate.
So if on my form I have the date 20/06/06 the value I need to show on my form the value from the table "rate" included in the range.
Hope I have been clear !!!!

 

[pbaldy]

You can use a DLookup or recordset to find the rate based on the date. You could also join the tables with a non-equi join.

 

[mtagliaferri]

I am quite new, could you give me adetailed example!

 

[pbaldy]

Sorry, I was out of town. I assume by now you've looked in Help or searched here and found the plentiful examples?

 

[mtagliaferri]

I have actually looked but got completely lost as I don't really know what I am looking at as I am quite new in ACCESS, can I eventually attach the file I need to sort out?
Marco

 

[deBassMan]

DLookup is slow but might be your best bet

Here's the syntax:

varX= DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

In the above example:
[LastName] - what you're looking for
"Employees" - the tablename
"[EmployeeID] = 1") - the where condition

good luck

 

[pbaldy]

Sure; it's often easier.

 

[mtagliaferri]

Thanks Paul, here is the file

 

[pbaldy]

I'll have to look at it tonight. I only have A2k where I am now. I have 2003 at home.

 

[pbaldy]

the tabel rate has the field datefrom, dateto, rate

Did this change, since the sample does not include the 2 date fields, just a "Valid from" date (whose real name is "date", which is a bad idea)?

 

[mtagliaferri]

Yes I took it out to try some examplesbut with no success but I can put back in "valid from" and "valid to"!!
Here is the original one

 

[pbaldy]

One way in a query:

Rate: DLookUp("rate","tblRate","ValidFrom <= #" & [DateCost] & "# AND ValidTo >= #" & [DateCost] & "#")

The slicker way, the non-equi join:

SELECT DateCost, HoursWorked, Rate
FROM tblCost Left JOIN tblRate ON tblCost.DateCost >= tblRate.ValidFrom AND tblCost.DateCost <= tblRate.ValidTo;

 

[mtagliaferri]

Hi Paul, thanks, can you give me furthe r info as I am quite new with query!!!
Do I aactually open the form from the query? and what is a non-equi join? Sorry If I tamper you with these stupid questions, but I am just learning from scratch!!!