Dlookup on unbound text

[E9-Tech]

I have a generic 'frmStatistics' not linked to a record source, I would like to display results from various queries as a purpose to overlook statistics.
One query example is below

The query returns a one line result, and I need to use the Avg calculated expressions (AvgOCR, AvgITSOR and AvgCPCR)

I want to add various unbound text boxes withn the form to read specific values within the queries, example I tried to add the AvgOCR from the above query and I used a DLookup in the control source of the unbound text named 'txtFrmAVGOCR'

I get an error 'The expression you entered has a function containing the wrong number of arguments'

Is it possible to achieve this with a look up function or does the form always have to be linked to a query or table? if so how do I go about getting results from multiple queries?
Thanks,

 

[arnelgp]

use the expression:

=Dlookup("AvgOCR","qryRIDAvg30Day")

 

[Gasman]

Why not look up the syntax of any command you want to use?

DLookup Function - Microsoft Support

Use the DLookup function in Access desktop databases to get the value of a particular field from a specified set of records.

support.microsoft.com

 

[E9-Tech]

Thanks @arnelgp that worked and thanks @Gasman for the link which I found useful, I am just puzzled and confused with when to use (), [] or " " in the expression @arnelgp changed the expression to use "" and it works, @Gasman the link provided uses some examples with [] mostly.
Would you be able to clarify this for future reference, I thought that the use of ""is for for specific reference such looking up for the word "apple" and [] is refered to the fields in the table/query, clearly I am getting this wrong.
Thanks

 

[Gasman]

You need [] if you have spaces in your field names, which is not recommended.

Here is one of mine.

 Me.cboDates = DLookup("StepsID", "tblDates", "StepsDate = " & Format(Date, strcJetDate))

 

[GPGeorge]

Thanks @arnelgp that worked and thanks @Gasman for the link which I found useful, I am just puzzled and confused with when to use (), [] or " " in the expression @arnelgp changed the expression to use "" and it works, @Gasman the link provided uses some examples with [] mostly.
Would you be able to clarify this for future reference, I thought that the use of ""is for for specific reference such looking up for the word "apple" and [] is refered to the fields in the table/query, clearly I am getting this wrong.
Thanks

Normally, the delimiter for names is simply the single space between words, but sometimes novices (and even the occasional experienced developer) use names that have spaces in them already:

tblPersonOfInterest vs Person of Interest

And that leads to the bane of any relational database application, ambiguity.

Is that supposed to be one table called "Person of Interest" (note I have to use quotes here to disambiguate)? Or is that supposed to be three different tables, called "Person", "Of" and "Interest"? Odd as that may seem, spaces and the absence of any other clue means Access can't figure it out on its own.

To you and me, that might seem silly. Of course it's one table name. But remember, Access is very, very literal. Three words and two spaces means three separate things and that's that.

In order to tell Access, "Treat these three words as one thing, not three things", we have to include an additional delimiter, i.e. the square brackets.

[Person of Interest] is one name because the square bracket delimiters supersede the internal space delimiters.

Hence, the admonition that we should always avoid spaces in table and field names or pay the price of the additional use of square bracket delimiters.

So, the answer to your question is that you are not wrong, at least in the sense that we mostly see square brackets in field and table names. The thing is that's only required when you let those pesky internal spaces slip into those names to raise the level of ambiguity.

 

[MajP]

clarify this for future reference, I thought that the use of ""is for for specific reference such looking up for the word "apple" and [] is refered to the fields in the table/query, clearly I am getting this wrong.

Kind of a strange example they used. That example should work without requiring [], but you cannot go wrong with always using []. In certain places you always have to use [], sometimes it may or may not work.

If you name fields, tables, queries, reports, forms, controls without the following you can limit when you have to use []. That is why you do not want to create the extra work by creating bad object names. However, in certain places such as Expressions you may still have to use [] even if you follow the below.

1. No spaces ProductName vs [Product Name]
2. No reserved words ReceiptDate vs [Date]

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

3. No special Characters. ProductNo vs [Product#]

Un tested but their example should work even if they excluded some of the []. (where Order Details has to be in square brackets)

=DLookup("ProductName", "Products", "ProductID =" & Forms![Order Details]!ProductID)

But it would have to look like this if their other names are bad with spaces

=DLookup("[Product Name]", "[Tbl Products]", "[Product ID] =" & Forms![Order Details]![ProductID#])

You almost always need [] for the following when the table or field name contains.
1. Spaces in names [Product Name]
2. The table or field names that are Reserved Words. Example [Item], [Date]
3. Certain symbols such as #, ',. Example [ID#]

Example
vba: Forms!Form1![Product Name]
SQL: Select [Item], [ID#] from [Table One]


There are certain places I always use [] around field names , object names even if they are properly formatted names (no spaces, reserved words, or special characters). It may or may not work without the [] so it is just easier to not think it through
1. Expressions. Like in calculated controls. =[Amount] * [Quantity]
2. Conditional formatting. Expression is: [Amount] > 100
3. Parameters in Query editor: Criteria: Forms![Form1]![txtbxOne]

Here is a perfect example where it would have been just safer to use []. (Conditional formatting). Many accesss "MVP, VIPs" could not figure this problem out and it was simply the requirement to use [] in an expression.

VBA Conditional Formatting error because I have more than 3 formatting requirements.

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done. Sorry guys, I still can only respond using my phone. However, I have also been playing in the background and got the same results as @MajP. My demo from the other thread I...

www.access-programmers.co.uk

 

[GPGeorge]

Kind of a strange example they used. That example should work without requiring [], but you cannot go wrong with always using []. In certain places you always have to use [], sometimes it may or may not work.

If you name fields, tables, queries, reports, forms, controls without the following you can limit when you have to use []. That is why you do not want to create the extra work by creating bad object names. However, in certain places such as Expressions you may still have to use [] even if you follow the below.

1. No spaces ProductName vs [Product Name]
2. No reserved words ReceiptDate vs [Date]

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

3. No special Characters. ProductNo vs [Product#]

Un tested but their example should work even if they excluded some of the []. (where Order Details has to be in square brackets)

=DLookup("ProductName", "Products", "ProductID =" & Forms![Order Details]!ProductID)

But it would have to look like this if their other names are bad with spaces

=DLookup("[Product Name]", "[Tbl Products]", "[Product ID] =" & Forms![Order Details]![ProductID#])

You almost always need [] for the following when the table or field name contains.
1. Spaces in names [Product Name]
2. The table or field names that are Reserved Words. Example [Item], [Date]
3. Certain symbols such as #, ',. Example [ID#]

Example
vba: Forms!Form1![Product Name]
SQL: Select [Item], [ID#] from [Table One]


There are certain places I always use [] around field names , object names even if they are properly formatted names (no spaces, reserved words, or special characters). It may or may not work without the [] so it is just easier to not think it through
1. Expressions. Like in calculated controls. =[Amount] * [Quantity]
2. Conditional formatting. Expression is: [Amount] > 100
3. Parameters in Query editor: Criteria: Forms![Form1]![txtbxOne]

Here is a perfect example where it would have been just safer to use []. (Conditional formatting). Many accesss "MVP, VIPs" could not figure this problem out and it was simply the requirement to use [] in an expression.

VBA Conditional Formatting error because I have more than 3 formatting requirements.

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done. Sorry guys, I still can only respond using my phone. However, I have also been playing in the background and got the same results as @MajP. My demo from the other thread I...

www.access-programmers.co.uk

Disambiguation is the motivation for those counter-examples, such as Reserved Words, Expressions, conditional formatting and query parameters, etc.

Explicitly identifying field and object names with the square bracket delimiter tends to minimize the possible ways to interpret the intent. It can't hurt to be as explicit as possible, unless you intend to make a joke.

 

[E9-Tech]

Thanks @GPGeorge @MajP @Gasman very informative, hopefully I will get it right next time

 

[MajP]

Disambiguation is the motivation for those counter-examples,

My point is that is a poor example. Either show required only, or all optional cases, but do not mix and match.

Their example

DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

1. If they are trying to prove a point of being unambiguous it should have been.

DLookup("[ProductName]", "[Products]", "[ProductID] =" & Forms![Order Details]![ProductID])

2. If they are trying to show what is required then

DLookup("ProductName", "Products", "ProductID =" & Forms![Order Details]!ProductID)

Their example only adds confusion because it disambiguates some optional cases, it does not disambiguate other optional cases, and it includes a required case . Either they should have only included the required cases ([order Details] or should have shown all cases.

Just as confusing as showing an example that mixes bang and dot notation, IMO.

 

[GPGeorge]

My point is that is a poor example. Either show required only, or all optional cases, but do not mix and match.

Their example

DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

1. If they are trying to prove a point of being unambiguous it should have been.

DLookup("[ProductName]", "[Products]", "[ProductID] =" & Forms![Order Details]![ProductID])

2. If they are trying to show what is required then

DLookup("ProductName", "Products", "ProductID =" & Forms![Order Details]!ProductID)

Their example only adds confusion because it disambiguates some optional cases, it does not disambiguate other optional cases, and it includes a required case . Either they should have only included the required cases ([order Details] or should have shown all cases.

Just as confusing as showing an example that mixes bang and dot notation, IMO.

True, that. I agree.

My overarching point is that too often developers ignore the impact of ambiguity and rely on context and clever Access architecture to compensate for lack of discipline in syntax. That's a sin I've been guilty of at times I'm afraid.

I'd wager, for example, that the author of that particular example never even considered the fact that it was inconsistent because Access does clean up after us -- sometimes.