DLookup help

[KiwiGirl]

Hi all

I am trying to set a criteria in a DLookup and I don’t know if it’s possible. I’ve managed to get a result using the following code in a form but it only matches an exact date.

strValue = DLookup("Price", "Price List", "ServiceItemID = " & "ServiceItemID" & " and CStr([ContractStartDate]) ='" & CStr([Date]) & "'").

What I really need is to be able to do a between criteria on the [Date] field and check out whether that fits between [ContractStartDate] and [ContractEndDate] and then return the Price according to the [Date].

I actually want to run it as a query but have no idea how to do this. So, can a "between criteria" be put into a DLookup? and can it all be put into a select query? If so how? If not, what better way it acheive the desired outcome?

Thanks

 

[KiwiGirl]

Thanks for your reply. Yes I had seen that link before I posted but i was not able to figure out the between criteria with any success.

 

[CraigDolphin]

Couple of things.

On your form you should have a control(textbox) bound to the ServiceItemID field in the underlying table. Your criteria expression should refer to the control, not a text string with the name of the control.

Instead of:

strValue = DLookup("Price", "Price List", "ServiceItemID = " & [COLOR="Red"]"ServiceItemID"[/COLOR] & " and CStr([ContractStartDate]) ='" & CStr([Date]) & "'")

It should be

strValue = DLookup("Price", "Price List", "ServiceItemID = " & [COLOR="Blue"]Me.ServiceItemID[/COLOR] & " and CStr([ContractStartDate]) ='" & CStr([Date]) & "'")

The word 'Me.' is a shortcut way for the form to refer to itself, so Me.ServiceItemID is the same as writing more fully Forms!YourFormeName!ServiceItemID


You mention you have a [Date] field which you want to be between the values in the ContractStartDate field and the ContractEndDate field.

The first thing you should be aware of is that Date is a reserved word in Access. You should never name a field the same as a reserved word because it causes unexpected errors. There is a list of reserved words here: http://support.microsoft.com/kb/209187

I'm going to assume you will rename the [Date] field to [MyDate].

strValue = DLookup("Price", "Price List", "[COLOR="Blue"]([/COLOR][ServiceItemID] = " & Me.ServiceItemID & "[COLOR="Blue"]) AND ([MyDate] Between #" & Me.ContractStartDate & "# And #" & Me.ContractEndDate & "#)[/COLOR])"

This assumes that you have three controls on your form (ServiceItemID, ContractStartDate, and ContractEndDate) and that your MyDate field in the [Price List] table contains data in a datetime datatype.

 

[KiwiGirl]

Thanks so much for your reply and for the link to the reserved words. Yes, i will definately change the "Date" feild to something better.

I actually have the ContractStartDate and ContractEndDate in the [Price List] table and MyDate field on the form.

As soon as I get a chance (in the next couple of days) I will work with the code you gave me and then see if I can get that into a query.

 

[CraigDolphin]

I actually have the ContractStartDate and ContractEndDate in the [Price List] table and MyDate field on the form.

In that case the example I gave you won't work and the overall approach might not be what you actually want either.

Am I right in saying that the comparison you are trying to make is to find the price in the [Price List] table where the value you've selected in the MyDate field (on the form) falls between the ContractStartDate and the ContractEndDate fields in the table? If so, you can do this readily enough, but you must be cognizant that this function will only return the price for the first record that meets the conditions you've specified.

So, let's say there are 3 records in the [Price List] table that have a ContractStartDate and a ContractEndDate that fall either side of a given date (MyDate). The Dlookup function will only return the price from the first record that it encounters and ignore the remaining two records.

Is that what you really want? If so, then something like

strValue = DLookup("Price", "Price List", "([ServiceItemID] = " & Me.ServiceItemID & ") AND ([ContractStartDate ] <= #" & Me.MyDate & "# And [ContractEndDate ]>=#" & Me.MyDate & "#))"

...should work.

But if you want to be able to sort through the options if more than one record exists, then you might need to take an alternative approach.

 

[KiwiGirl]

Yes you are right in saying that the comparison i am trying to make is to find the price in the [Price List] table where the value selected in the MyDate field (on the form) falls between the ContractStartDate and the ContractEndDate fields in the table.

The price list contains items that have a new price entered each year for example:
item ATA01 Contract Start is 1/1/07, contract End 31/12/07 Price is $90
item ATA01 Contract Start is 1/1/08, contract end 31/12/08 Price is $110

So me.myDate is 1/12/07 = $90

Thanks for your help
KG

 

[KiwiGirl]

I have had a chance now to try out that code.
It first came up with a complile error where it expected a list separator or a ). So I put a ) at the end of the line of code (because I didn't know where else).

It then came up with the following error:
Run-time error ‘3075’
Extra ) in query expression ‘([ServiceItemID]=ATA01) AND ([ContractStartDate] <=#01/02/2008# And [ContractEndDate]>=#01/02/2008#))’.

Not sure what to try now...any suggestions?
KG

 

[CraigDolphin]

Sorry...easy to misplace those when you're 'air-coding' on a forum like this.

strValue = DLookup("Price", "Price List", "([ServiceItemID] = " & Me.ServiceItemID & ") AND ([ContractStartDate ] <= #" & Me.MyDate & "#) And ([ContractEndDate ]>=#" & Me.MyDate & "#)")

Hopefully that does it.

 

[KiwiGirl]

Hi again

The code produced a run-time error 2471 "the expression you entered as a query parameter produced this error: 'ATA01'. Which is the value of me.ServiceItemID.

Thank you for perservering with me.
KG

 

[CraigDolphin]

Oh....to me an ID in a field name indicates a numeric value is present whereas it appears that it is actually a text string so you will need to enclose this with some single quotes as below.

strValue = DLookup("Price", "Price List", "([ServiceItemID] = [COLOR="Red"][B]'[/B][/COLOR]" & Me.ServiceItemID & "[COLOR="red"][B]'[/B][/COLOR]) AND ([ContractStartDate ] <= #" & Me.MyDate & "#) And ([ContractEndDate ]>=#" & Me.MyDate & "#)")

If you keep getting errors maybe you'd do better to actually post a zipped copy of your db (stripped of ay sensitive data but with enough data to illustrate the problem).

 

[KiwiGirl]

Thank you for that it works very well. I thought I might have been able to put it in a query which is where I really wanted it, but after spending time trying, I realise I can’t.

I have attached the database in the hope that I can explain what I need to do now. What I'd really like to achieve is the qryInvoicing query to produce a price from the Price List table based on the DateOfWork field. To have the DLookup run in this query would be fantastic. Is this possible? When you run the query it has a Job ID parameter. You could just type in 67 or 15 to get a result.

FYI
TotalInvoice (Query) is based on qryInvoicing
R_Invoice summary is the report produced from the queries.
The timesheet entries are added for work done on a particular Job No.

Please excuse the naming conventions of objects. I will get around to tidying them up.
Thanks in advance

 

[CraigDolphin]

KiwiGirl,

There's no reason you can't use domain aggregate functions in queries. I've done so many times.

For some reason evertime I try to open qryInvoicing, either in design view or table view, it causes Access to crash (on two different machines). I suspect the query is corrupted. Can you post the sql of that query so I can create the query and look at it.

Incidentally, I see you're a fellow Cantab Nice to see someone from my old stomping grounds here!

 

[KiwiGirl]

Hey...I was wondering if upokororo was a Maori word then i looked at your location and thought No...but if i'd gone into your public profile, I would have seen that you were (once) a kiwi.

The SQL for qryInvoicing is:

SELECT T_JobRecord.Job_ID, [Staff_Forenames] & " " & [Staff_Surname] AS StaffName, [Patient_Forenames] & " " & [Patient_Surname] AS ClientName, T_Timesheet.Quantity, T_ACCServiceItem.Service_Item_Description, [Price List].Price, T_PatientDetail.[Date of Birth], T_JobRecord.ACC_Claim_Number, T_JobRecord.ACC_Case_Manager, T_JobRecord.Date_Job_Closed, T_JobRecord.[Order Number], T_JobRecord.Referral_Date_Received, T_PatientDetail.Patient_NHI, T_ACCServiceSchedule.[Service Schedule Name], T_Timesheet.TimesheetID, T_ACCServiceItem.Service_Item_ID, T_ACCServiceSchedule.[Service Schedule ID], IIf([Service_Item_ID]="TRAVD10",[Quantity],[Quantity]/60) AS TotQty, [TotQty]*[Price] AS TotPrice, T_JobRecord.Assessment_Type, T_JobRecord.ACC_Branch, T_JobRecord.[Cost Centre], T_Timesheet.DateOfWork
FROM T_PatientDetail INNER JOIN (T_JobRecord INNER JOIN ((T_ACCServiceSchedule INNER JOIN (T_ACCServiceItem INNER JOIN [Price List] ON T_ACCServiceItem.Service_Item_ID = [Price List].ServiceItemID) ON T_ACCServiceSchedule.[Service Schedule ID] = [Price List].[Service Schedule ID]) INNER JOIN (T_StaffDetail INNER JOIN T_Timesheet ON T_StaffDetail.Short_Name = T_Timesheet.Staff_ID) ON T_ACCServiceItem.Service_Item_ID = T_Timesheet.ServiceItemID) ON T_JobRecord.Job_ID = T_Timesheet.Job_ID) ON T_PatientDetail.Patient_NHI = T_JobRecord.Patient_NHI
WHERE (((T_JobRecord.Job_ID)=[Forms]![JobRecord]![Job_ID]));

Thanks a lot for your help, it is much appreciated.

 

[CraigDolphin]

but if i'd gone into your public profile, I would have seen that you were (once) a kiwi.

Once a Kiwi, always a Kiwi. Can't tell you how much I miss home.

Anyways, have a look at the attached. I'm not entirely sure how you're wanting to use the queries but I've modified them to:

qryInvoicing
1. Replaced the [Price] field via the join with a lookup of the price based on the dateofwork field and the serviceitemID.
2.Used grouping to avoid double records in the query due to the join.
3.Modified the TotPrice to work with the lookup value instead of the [Price] from the join.

TotalInvoice
1. Replace the [Price] field reference to the Price_Lookup field in qryInvoicing.

R_Invoice Summary
1. Bound the textbox to [Price_Lookup] instead of [Price] in the recordset.

I also added some checking for nulls behind the button on your form to avoid errors if the button is pressed on a new record.

 

[KiwiGirl]

That works perfect! You have saved me so much time. It would have taken for ever for me to work that out.

Thank you very much for all your help.

 

[CraigDolphin]

No worries. Throw a stone in the ocean for me next time you're down at New Brighton

 

[KiwiGirl]

I sure will. I'll throw one from the Pier.