Dlookup function is not working

[Amit jha]

Hi There, i am not expert in access but trying to do something with Dlookup but not sure getting error with

my requirement is to get service charges if i am entering this two text and combo box value by using Dlookup . kindly assist.

Dim val As Double
Dim sert As String
Dim engp As Double

sert = Me.Service_Type.Value
engp = Me.Engine_Power.Value

val = DLookup("Ser_charge", "Service_Charges", "Service_Type='sert' AND Engine_power = 'engp'")

from table Service_charges

 

[Amit jha]

now its not showing any error but value shows null

Me.Service_Charge_cr_.Value = DLookup("[Ser_charge]", "[Service_Charges]", "[Service_Type] ='sert' & [Engine_power] = 'engp'")

 

[pbaldy]

The second value looks to be numeric and thus shouldn't be surrounded by single quotes. Also, the variables need to be concatenated into the string. More here:

General: DLookup Usage Samples

 

[The_Doc_Man]

Assuming that sert and engp are the names of the controls on your form, ...

Change "[Service_Type] ='sert' & [Engine_power] = 'engp'"

To " ( [Service_Type] = " & Me.sert & ") AND ( [Engine_power] = " & Me.engp & ")"

 

[CJ_London]

I would just add that val is a reserved word - it is a function that converts a number from text to a number e.g.

val("1234") with return 1234

So best not to use it as a variable name, use something like SCval

 

[pbaldy]

Assuming that sert and engp are the names of the controls on your form

They are variables declared and set in the code Doc. And one of them is text.

 

[The_Doc_Man]

Oops, missed that.

Change "[Service_Type] ='sert' & [Engine_power] = 'engp'"

To " ( [Service_Type] = '" & sert & "') AND ( [Engine_power] = " & engp & ")"

EDITED to include apostrophe marks for service type.

 

[Solo712]

now its not showing any error but value shows null....

Try this,

= DLookup("[Ser_charge]", "[Service_Charges]", "[Service_Type] ='sert' & AND [Engine_power] = " & engp)

Best,
Jiri

 

[pbaldy]

This is the problem with trying to give the fish.

Doc:

And one of them is text.

Jiri, they're both variables.

 

[Pat Hartman]

OOPs. I just realized that Doc already mentioned this. I'll leave my response in case it helps.

If the dllookup() is supposed to be referencing a variable, it is coded incorrectly. The Where clause is sent as is to the query built by the DLookup(). If you are referencing variables, you need to force the interpreter to substitute the actual value of the variable for the name for the name of the variable.

val = DLookup("Ser_charge", "Service_Charges", "Service_Type='sert' AND Engine_power = 'engp'")
should be

MyVal = DLookup("Ser_charge", "Service_Charges", "Service_Type='" & sert & "' AND Engine_power = '" & engp & '")

Better still:
Dim strWhere as String
strWhere = "Service_Type='" & sert & "' AND Engine_power = '" & engp & '"
MyVal = DLookup("Ser_charge", "Service_Charges", strWhere)

This second method gives you the ability to put a stop in the code and print the strWhere to the debug window so you can be certain it makes sense.

Just FYI, if there are multiple rows that satisfy the criteria, the DLookup() will return ONLY the first it finds.

Also, Engine_power looks like it is numeric. If it were text, it would be left justified in the field so remove the single quotes in that part of the expression if I am correct.

 

[Amit jha]

Oops, missed that.

Change "[Service_Type] ='sert' & [Engine_power] = 'engp'"

To " ( [Service_Type] = '" & sert & "') AND ( [Engine_power] = " & engp & ")"

EDITED to include apostrophe marks for service type.

Thank you so much sir .. someone assisted with this and that worked .. thanks again for your kind assistance .

val = DLookup("Ser_charge", "Service_Charges", "Service_Type='" & sert & "' AND Engine_power = " & engp & "")

 

[Amit jha]

T

OOPs. I just realized that Doc already mentioned this. I'll leave my response in case it helps.

If the dllookup() is supposed to be referencing a variable, it is coded incorrectly. The Where clause is sent as is to the query built by the DLookup(). If you are referencing variables, you need to force the interpreter to substitute the actual value of the variable for the name for the name of the variable.

val = DLookup("Ser_charge", "Service_Charges", "Service_Type='sert' AND Engine_power = 'engp'")
should be

MyVal = DLookup("Ser_charge", "Service_Charges", "Service_Type='" & sert & "' AND Engine_power = '" & engp & '")

Better still:
Dim strWhere as String
strWhere = "Service_Type='" & sert & "' AND Engine_power = '" & engp & '"
MyVal = DLookup("Ser_charge", "Service_Charges", strWhere)

This second method gives you the ability to put a stop in the code and print the strWhere to the debug window so you can be certain it makes sense.

Just FYI, if there are multiple rows that satisfy the criteria, the DLookup() will return ONLY the first it finds.

Also, Engine_power looks like it is numeric. If it were text, it would be left justified in the field so remove the single quotes in that part of the expression if I am correct.

Thanks for your kind assistance ... it worked with small change
val = DLookup("Ser_charge", "Service_Charges", "Service_Type='" & sert & "' AND Engine_power = " & engp & "") and defined val as currency

 

[nonakag]

I've tried using the Syntax in the above examples. However, I'm not getting an error message and my multiple Criteria Dlookup code is not giving me the correct result.

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50'"

This part works fine and returns the last table row occurrence of 'CR50' inspection due dates in the table Fleet_PMCS. However, when I add the Second Criteria below it ignores the CR# criteria and for some reason settles on a date for another row for CR01. I understand I should not be using the '#' as it is a reserved symbol and perhaps this is why it is not working. I'm 99.9% completed with my project and just wanted to add this feature into the project. CR# is used throughout the project and I would pay dearly if I had to go back and correct it. I'm just asking here if it is actually the problem at hand? The added second criteria is below:

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And " Completed = False ")

What I'm trying to get DLookup to retrieve is an inspection due date (DueODue) in the Table Fleet_PMCS with a second Criteria of the Inspection being completed (Yes) or not completed (No) in a Yes/No Field called Completed. If I can get it to work, I would then add a 3rd Criteria for not completed inspections with a DueODue date prior to today (Now()). But, hopefully, someone can point something out that I'm missing without having to change the Field Name CR#.

DueODue is a calculated field in the table Fleet_PMCS.

Thanks in advance!

 

[arnelgp]

You have extra ":

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False")

 

[nonakag]

I'm also using the code in a Switchboard. I just used the code and the text box just says #error. I have multiple Dlookup statements on a Switchboard looking at the same table all at the same time. This never happened before but when I corrected the extra " all seven of the DLookup statements now show error. I only corrected one of the statement to try it. Does this mean I have to use the # around the Date Fields?

 

[arnelgp]

yes you need to delimit the date fields with #.

 

[nonakag]

I tried this:

Dlookup("# DueODue #", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False")

I'm still getting the same #error. Does this also mean no records? I know the table has the data I need. I just can't get DLookup to retrieve it.

 

[arnelgp]

only in the criteria you need to put the #, example:

Dlookup("somefield", "Fleet_PMCS", "[CR#] = 'CR50' And DueODue = #9/16/2020#")

on your dlookup:

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False")

if dlookup does not find anything it will return a Null, so you need to capture that one
and define your variable as Variant.

 

[nonakag]

I tried this:
IsNull( Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False"))

still same #error.

 

[nonakag]

Thanks Arnelgp, I got this to work. I had to close out Compact and Repair and the code starting working. I can now get CR# and Complete = False to get the right Dates. I need your help to add the third criteria. I need to use DueODue again but need to find the date between Today() and Today()+30. In other words dates that fall between today and 30 days ahead of today. Any suggestions on how the Syntax would be for this Thanks again!

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False")

maybe: And "DueODue Between Today() and Today()+30") ?