Query result in textbox

[Stefan.Kauw]

Hiya,

The search function already hooked me up to using DLOOKUP for getting a result into a textbox.
For whatever reason it keeps saying the error #NAME?.

I have a table as seen in the attachment as image.
From this table i want to select only 1 column and 1 row.

I do this using this (working) query:

SELECT Plastic_Partijen.[Huidige behandelbeurt]
FROM Plastic_Partijen
WHERE ((Plastic_Partijen.Partijnummer)=[TempVars]![Partijnummer]);

This query works when executing it outside the form. I do get the single result i want.

What doesn't work is when i'm using DLOOKUP. Google eventually teached me the function needs to functions: the column name and the query:

=DLookUp([Plastic_Partijen].[Huidige behandelbeurt];[q_SelectBehandelbeurtFromPartijen])

The DLOOKUP code is added into the contorelement source. I tried several thing like using and not using the []. Using and not using the table source like "[Plastic_Partijen]". But it keeps me telling #NAME?.

Google helped me so much already but this one i cant solve....

 

[MajP]

The arguments of a dlookup are strings and need ", the seperator is a comma , not a ;

dlookUp("[Plastic_Partijen].[Huidige behandelbeurt]","[q_SelectBehandelbeurtFromPartijen]")

 

[MajP]

Even if you add a criteria you will have to convert that to a string.
DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

Also when working if vba you want to declare all variables
https://www.excel-easy.com/vba/examples/option-explicit.html
You will know this when at the top of every module you will see
Option Explicit

That would have helped you solve this problem, because not only would that code not work but it would not compile. It may have compiled because it thought those arguments were variables.

https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

 

[Stefan.Kauw]

Allright.. Please kill me.

This was the solution:
=DLookUp("[Huidige behandelbeurt]";"[q_SelectBehandelbeurtFromPartijen]")

Using the , didnt work. Using the ; did work immediately. Also never realised that DLOOKUP somehow performs some kind of query and a query needs quotations for strings...
I've just become a member and i'm not regretting it at all already

 

[The_Doc_Man]

We prefer to not kill you. Since you are an international member, the paperwork for the after-action report would be horrendous.

It is interesting that your DLookup didn't require criteria. That means that the query must be doing all the selection, or else you are just taking the first thing that comes back from the query.

Also interesting that it took the semicolon but didn't take the comma, since my top three syntax and semantics reference sites say that the semicolon syntax shouldn't work correctly. (TechOnTheNet, MSDN.Microsoft, Support.Office). I wonder what it is that you are actually executing.

As far as the Domain Aggregates (DLookup, DSum, DAvg, DMax, etc) they are ALL the same in one way - they form some type of SQL query. We don't get to see what they actually do since we can't see the code ourselves, but it might be as simple as building a query string, opening it as a recordset, doing a .MoveFirst, and picking out the required element. Plus cleanup, of course.

 

[Minty]

As others have stated you need to get the syntax correct. Assuming this is on a form

=Dookup("[Huidige behandelbeurt]","Plastic_Partijen","[Partijnummer] = '" & [TempVars]![Partijnummer] & "'")

You can replace the tempvar with the name of a control on your form if that is where you want to get the value from.

Check the links in my signature.

I'm not sure if you can refer to a a tempvar in a form ? I've never used them.

 

[Stefan.Kauw]

We prefer to not kill you. Since you are an international member, the paperwork for the after-action report would be horrendous.

Fair enough... Although, the after action report could be done in access which could bring in new members.

It is interesting that your DLookup didn't require criteria. That means that the query must be doing all the selection, or else you are just taking the first thing that comes back from the query.

I already have SQL experience while programming websites a long time ago. So the query does all the selection and the query it build so that it does not give more that one record

Also interesting that it took the semicolon but didn't take the comma, since my top three syntax and semantics reference sites say that the semicolon syntax shouldn't work correctly. (TechOnTheNet, MSDN.Microsoft, Support.Office). I wonder what it is that you are actually executing.

Since access does use the comma itself it suprises me to but hell... it works without problems

As far as the Domain Aggregates (DLookup, DSum, DAvg, DMax, etc) they are ALL the same in one way - they form some type of SQL query. We don't get to see what they actually do since we can't see the code ourselves, but it might be as simple as building a query string, opening it as a recordset, doing a .MoveFirst, and picking out the required element. Plus cleanup, of course.

Makes sense...!

As others have stated you need to get the syntax correct. Assuming this is on a form

=Dookup("[Huidige behandelbeurt]","Plastic_Partijen","[Partijnummer] = '" & [TempVars]![Partijnummer] & "'")

You can replace the tempvar with the name of a control on your form if that is where you want to get the value from.

Check the links in my signature.

I'm not sure if you can refer to a a tempvar in a form ? I've never used them.

Refering to a tempvar inside the textbox works. Already done that.
I do prefer sticking to SQL since that is a "language" that i already now and i can read more easily.

 

[June7]

I have seen the semicolon used in place of comma in many non-English or UK language Access.

Without WHERE argument, DLookup just returns value from first record.