Value or Reference

[Dan2kx]

just to satisfy my curiousity what the difference?

=DLookUp("[order received]","[qryorder received]","[order ID] = '" & Forms!mainform![order id] & "'")

=DLookUp("[order received]","[qryorder received]","[order ID] = [Forms]![mainform]![order id])

i apologise if im digressing the thread

Dan

[Stewart Ross]

The difference is as follows:

When "[somefield] = [somecontrol]" is passed as a string to the function there is no attempt to substitute a value for the [somecontrol] reference. The code behind the DLookup function must then evaluate what [somecontrol] means, if it can. It may not be interpretable at all - in which case it will treat the [somecontrol] reference as an unknown parameter missing a value and return an error accordingly.

When the expression is sent as "[somefield] = '" & [somecontrol] & "'" the expression is evaluated before the string is sent to the function, and the value of the control is substituted for the control reference. Say the control contained ABC123 as its value. The string sent to the DLookup function would be

"[somefield] = 'ABC123'"

which it can interpret without trying to obtain a value from an unknown control.

-Stewart

[Dan2kx]

so i presume that your method is faster/efficient does the same apply in SQL WHERE, VBA etc,

[Stewart Ross]

Dan, there are many posts in which the error message 'too few parameters - expected 1' has arisen (try a search for that phrase and you will see what I mean). This arises when queries and other expressions that refer directly to form controls in their WHERE clauses fail on being run within VBA code (for example where the query concerned is the source specified for the DAO OpenRecordset method).

The problem does not arise consistently, because within the query editor say Access can interpret most of these references before they get to the inner core of the database engine - but OpenRecordset and similar VBA functions working on objects within the inner core of the database do not 'see' the forms environment, and are not able to interpret such references.

Even when run from the Access Query Editor such form field references will fail in some circumstances. A form control reference in the WHERE clause will work absolutely fine in a SELECT query and fail entirely if the same query is changed to be a crosstab - except this time Access will return an error message saying that the reference concerned is an unrecognised field name.

It's not a matter of efficiency, but of being able to do it at all in some cases. A consistent approach is that in code you should always use the value of the control concerned instead of trying to pass the name of the control as part of a WHERE clause or similar element.

-Stewart

[Dan2kx]

can't say i've had any problems myself so far but i will change my method

thanks

Dan

[Stewart Ross]

Good to hear, Dan.

Here's a reference to this problem from another (very useful) site called the Access Web:

Queries: Too few parameters - expected n

-Stewart

[NeoPa]

This (quite interesting) question has been split off from http://bytes.com/topic/access/answers/866790-dlookup.

PS. I would certainly go along with Stewart on all of this Dan. When creating SQL in VBA, use the literal value where possible. Save the references for QueryDefs which are run without VBA control, keeping them to the bare minimum.