DLookup with 2 criteria. (1 Viewer)

[hooi]

DLookup with 3 criteria.

I have a aPointsRate table such as shown below:

DayTypeID____BeginTime_____EndTime____MultiplyBy
2____________00:00am______8:59am____2.0
2____________9:00am_______5:59pm____1.0
2____________6:00pm_______11:59pm___1.5
3____________00:00am______8:59am____2.0
3____________9:00am_______5:59pm____1.0
3____________6:00pm_______11:59pm___1.5

When a user enters a Date and TimeIn, eg:
Date=21/10/2003, TimeIn=3:00pm
I expect to retrieve MultiplyBy=1.0

My DLookup looks like this:
=DLookUp("[MultiplyBy]","aPointsRate",("[DayTypeID]= " & Weekday(Forms!ufmService!ufmServiceVisit.Form!Date)) And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceDetails.Form!TimeIn & " >= [BeginTime]") And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceDetails.Form!TimeIn & "<= [EndTime]"))

but the result I get is: No current record.

Would appreciate any help rendered. Thanks...

 

[namliam]

1) dont name a control on a form Date, Date is a function retrieving todays date. This may cause conflicts... so dont...
2) Your Weekday is using the system default, thus may cause unexpected behauvoir. Change it to Weekday(...,vbMonday) or Weekday(...,vbSunday) to make sure ....
3)You are sure that all your fields
-TimeIn
-BeginTime
-EndTime
-Date (bad bad you)

Are of the date/time format?
4) Dont use to much brackets its only confusing the matter,
5) The and's should also be part of the complete string... Like so:
6) You might also need to tell access its dealing with times by adding # around each time.. Like So:

=DLookUp("[MultiplyBy]","aPointsRate","[DayTypeID]= " & Weekday(Forms!ufmService!ufmServiceVisit.Form!Date,vbSunday) & " And #" & Forms!ufmService!ufmServiceVisit.Form!ufmServiceDetails.Form!TimeIn & "# >= [BeginTime] And #" & Forms!ufmService!ufmServiceVisit.Form!ufmServiceDetails.Form!TimeIn & "#<= [EndTime]")
Note: the much needed spaces around the and's

7) Its good practice to allways have the search fields first followed by the search value... Like you do with your DayTypeID but not with the other 2...

If all that is in order, it should work just fine... But change 1 2 and 7 to make it even better

Regards

Editted to add 4 6 and 7

 

[hooi]

Thank you Namliam for your response.

TimeIn, BeginTime and EndTime are date/time fields.

Since I've tested the [DayTypeID] part of the DLookup and know that it works, I've kept it the same. Changes have been made based on your other suggestions. My DLookup now looks like this:

=DLookUp("[MultiplyBy]","aPointsRate","[DayTypeID]= " & Weekday([Forms]![ufmService]![ufmServiceVisit].[Form]![ServiceDate]) And
"[BeginTime] <= # " & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn] And
"[EndTime] >= # " & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn])

but it still says: no current record.
Is my string correct?

 

[namliam]

=DLookUp("[MultiplyBy]","aPointsRate","[DayTypeID]= " & Weekday([Forms]![ufmService]![ufmServiceVisit].[Form]![ServiceDate]) And
"[BeginTime] <= # " & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn] And
"[EndTime] >= # " & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn])

You are missing 5 and 6....

=DLookUp("[MultiplyBy]","aPointsRate","[DayTypeID]= " & Weekday([Forms]![ufmService]![ufmServiceVisit].[Form]![ServiceDate]) & " And " &
"[BeginTime] <= # " & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn] & "# And " &
"[EndTime] >= #" & [Forms]![ufmService]![ufmServiceVisit].[Form]![ufmServiceDetails].[Form]![TimeIn] & "#")

That should do it....

Regards

The Mailman

 

[hooi]

Fantastic. It works! Thank you Mailman. Appreciate your help.

 

[hooi]

My application now requires computation which I believe can only be achieved using VBA. But since I’m not a programmer, would really appreciate some help on this.

Here is my problem: I’m creating a customer service module which needs to calculate the total number of points used which depends on the contracted rates, the hours spent and the type of day/hour of the service (eg. in the aPointsRate table shown above. Holiday always multiply by 2)

So, apart from the aPointsRate table, there are two more tables involved for this computation:
1. ContractedRates table (This table is linked via query in the service form.)
Eg:
CRateID__ServiceTypeID__1stTimeSlot__PntsToDeduct__SubSlot__SubPts
1________1____________1:00_________5_______________0:30____2.5

(ie Service rate for a particular customer is based on 1st hour = 5 points, Subsequent half hour = 2.5 points)

2. Holiday table:
Eg:
HolidyID____Description______HolidayDate
1_________Christmas________25/12/2003

If a service engineer spends three hours at the customer site, let’s say from 4:30pm till 7:30pm. This means:

First check whether the day of service falls on a holiday, if true, MultiplyBy = 2.
Else:
From 4:30pm till 5:59pm, MultiplyBy = 1. Total points used for the first three hours = (5 + 2.5) x 1 = 7.5
From 6:00pm till 7:30pm, MultiplyBy = 1.5. Total points used for the next three hours = (2.5 + 2.5 + 2.5) x 1.5 = 11.25
Total points is therefore 7.5 + 11.25 = 18.75

Thanks...