Dlookup in a query driving me carzy

[eatraas]

Hi,

this is driving me crazy, in access 2013 it seems to be impossible to use a dlookup function in a querie. I cannot use joined tables in this design.

i tried :

Expr1: DLookUp("[omschrijving]";"p8q";"[Id]=[p8id]")
Expr1: DLookUp("[omschrijving]";"p8q";"[Id]="& [p8id])

Also used single qoutes, with or without brackets.

What should be the correct code?

Thanks in advanced

Regards
Erwin

 

[Galaxiom]

Not much information to go on. What error do you get?

 

[static]

use a comma instead of the semicolon.

 

[Galaxiom]

use a comma instead of the semicolon.

Maybe, but functions often use the semicolon as argument separators in regions where the decimal separator is a comma.

 

[isladogs]

Your syntax is incorrect.
Use commas not semicolons (EDIT - but see Galaxiom's post)

You only need [] if the items contain spaces or special characters

The expression uses DLookup(Field Name, Table Name, Criteria)
i.e. field name =omschrijving & table name = p8q

Your id field value p8id is a string so you need:

Expr1: DLookUp("omschrijving","p8q","Id='p8id'")

 

[Galaxiom]

Your id field value p8id is a string so you need:

Expr1: DLookUp("omschrijving","p8q","Id='p8id'")

As it is being used in a query and with bracket delimiters, I expect they were trying to refer to a field rather than a literal string.

Try this, assuming p8id is a field with a text data type:

DLookUp("[omschrijving]";"p8q";"[Id]='"& [p8id] & "'")

BTW

The expression uses DLookup(Field Name, Table Name, Criteria)
i.e. field name =omschrijving & table name = p8q

To be precise, the first parameter of the domain functions is not limited to a fieldname. It is an expression.

 

[static]

A record can only have one value per field so the dlookup has to return a single value.
In which case he could use a join.
This makes no sense.

 

[Galaxiom]

A record can only have one value per field so the dlookup has to return a single value.
In which case he could use a join.
This makes no sense.

The Dlookup runs once for each record.

Of course this is not very efficient so best avoided. However it is a way to join data which would otherwise result in the recordset being not updateable.

 

[static]

Well, yes, but he said he couldn't use joins even though he appears to be using a join.
He's joining one value to another value to return a unique value (? or not) (we don't know what errors he's getting either).
Whether a query is updateable or not is down to primary keys (which we know nothing about), not the joins themselves.
And, of course there is no reason to have a dlookup in a query. Put them in your controls.

 

[Mark_]

Where is [p8id] coming from?
Is it a field in a table or query? Is it a control on the screen? Is it a variable you are generating some where?

 

[jdraw]

Erwin,

I cannot use joined tables in this design.

Please tell us more about the design and what you are trying to achieve in simple terms.