Dlookup question

[jan pieter]

Is it possible to have WHERE,ORDER BY and HAVING as criteria in de function dlookup?

Function:

DLookup(expr, domein [, criteria] )

Thanks in advance!

 

[Cronk]

Basically, no.

However the dlookup(...,..,[criteria]) is effectively the WHERE or HAVING and as it is returning an expression, not a set, ORDER is of no relevance.

What is it that you are trying to achieve?

 

[jan pieter]

Thanks for responding,

Well whant to achieve is:

I have 2 colums


car_number //////car_total

11///// 7
11///// 6
11//// / 5
12 ///// 4
12///// 5

and I whant to filter out the first column ( car_number)
to a specific car_total and return that value.

 

[Cronk]

So for car_number 11 for example, what do you want returned? The three car_total values, the maximum, the minimum, or something else?

 

[jan pieter]

Thanks for responding,

I have two textboxes in a form the first one is the one you can put in your car_number and in the other one you can put in your car_total

so when somebody types in his car_number and his car_total I first whant to filter with car_number and then match what is filled into the textbox car_total with what is in the database.

At the moment I' m using this.

" cartotal = " & me.cartotal"

But this value gives me all the values of all the car_total that has been filled into the textbox, but I whant the one that matches car_total and car_numbers


so in this case:

If textbox car_number is filled in with 11
and the textbox car_total is filled with 5

Then I whant it to return 5 and not twice 5

 

[Cronk]

So you do not want to lookup the value of car_total for car_number 5. I understand then that you want to show only the record corresponding to the car 5 and total 11.

If so, dlookup returns a value, not a record and it's not appropriate in this context.

What you want is a filter on the form. In the after_update event of txtCarTotal (the text box) add the following code

Me.filter = "Car_number=" & me.txtCarNumber & " and Car_total=" & me.txtCarTotal
Me.filteron=true