Dlooup grrrrr

[monvani]

Form is based on tblColLabels, and I have a field on the form that I want to DLookup values from an unassociated query. I can't seem to get the syntax right to get it to display the query values.

View attachment test.zip
This is in the expression on the form (attached for reference):
=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriodMaster=" & Forms!tblColLabels!month2)

 

[Banana]

What data type is the month2? If it's a string or date, then it should be delimited:

If it's a string:
"rptPeriod Master=""" & Forms!tblColLabels!month2 & """"

If it's a date:
"rptPeriod Master=#" & Forms!tblColLabels!month2 & "#"

 

[Galaxiom]

Is month2 formatted as text?
If so:

=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriod Master='" & Forms!tblColLabels!month2 & "'")

 

[monvani]

The field is text-

What I had originally that didn't work:
=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriodMaster=" & Forms!tblColLabels!month2)

I've tried each of these and none worked:
=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriodMaster=" & Forms!tblColLabels!month2 & "'")

=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriodMaster='" & Forms!tblColLabels!month2 & "'")

=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriod Master=""" & Forms!tblColLabels!month2 & """")

 

[Banana]

Let's rule out the syntax out.

Can you get a simple reference to work:

In the same textbox, change controlsource to:

=Forms!tblColLabels!month2

See if it gives you the same result as in the actual month2 to verify that the reference is correct. If it's incorrect, check the syntax and whether it's nested in a subform. Alternatively use expression builder to help you.

 

[Galaxiom]

Your naming scheme is very confusing.
What is rptPeriod Master? Normally this would be a report object but you appear to have it as a field.

Spaces in field and table names is always a bad idea too. Though it may be due to the way this site breaks long text strings. This is why you should put code inside code tags unless it is very short.

I also hate the way Access Form Wizard defaults to naming a form with the table name used as its record source. eg Forms!tblColLabels

 

[Banana]

Ooo, Galaxiom is on something here!

If "rptPeriod Master" is a field or something like that it may need to be delimited as well:

[rptPeriod Master]...

I also agree that spaces are best not used and wizards has likely to be too user-friendly that it ends up confusing the engine more.

 

[monvani]

Ok, I had the wrong form name, but this part works-
=Forms!frmShowActuals!month2

So, I want to lookup that value (it exists in the rptPeriodMaster field) within the qryColLbl query, and return the associated value in the FirstofrptColLbl field for that record.

All fields are txt.

This is what I'm working with, but I'm pretty confused with the talk of when I'm supposed to use brackets[] as opposed to quotes"". I've tried both of the below, without success.

=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriod Master="&Forms!frmShowActuals!month2)
=DLookUp("FirstOfrptColLbl","qryColLbl","rptPeriod Master='" & Forms!frmShowActuals!month2 & "'")

 

[Galaxiom]

Also I notice you appear to be using a First expression in a Totals query that you then lookup for the match. Note that first does not necessarily return the first that you might expect. Better if you can use Max or Min on a field that can be unambiguously ordered.

Also instead of the query followed by the DLookUp yo may be able to use DMax or DMin.

 

[Galaxiom]

This is what I'm working with, but I'm pretty confused with the talk of when I'm supposed to use brackets[] as opposed to quotes"".

Brackets are used to delimit objects (collections,tables, queries, fields, controls, etc) particularly when they have spaces in their names
Quotes are used for text strings.

The arguments in the domain functions are strings. The brackets are object delimiters inside those strings so you use both.

The string arguments can also be built from concatenated expressions or variables.
"whatever= [Forms]![form name]![controlname]" is a string that is passed to the function exactly as it is written.

"whatever=" & [Forms]![form name]![controlname] is reduced to a string with the value of the control and passed to the function.
eg: whatever=5

 

[monvani]

So sometimes within expressions, my brackets are automatically replaced by quotes by Access when i hit enter, or try to display the form in Form view. Especially, when they are within a function.
Know why?

 

[Banana]

For simple reason that Access doesn't always know whether you are referring to 'foo' as a string literal or an object so it's best to provide correct delimiters to ensure that Access recongize foo as an identifier rather than a string literal.

 

[Galaxiom]

Using brackets and quotes.

=DLookUp("FirstOfrptColLbl","qryColLbl","[qryColLbl].[rptPeriod Master]='" & Forms!tblColLabels!month2 & "'")

 

[monvani]

Glax, I really thought that was going to work, but just the same old error# message. I hilited the problem field on the attached if you have time to look at it. Maybe I'm just missing something obvious.
View attachment test.zip

 

[Galaxiom]

I can't get my head around what you are doing but looks to me like you are going about it the hard way.

I think you should change the recordsource to a query that collects only the records you want. Then just show them with a set of bound controls.

 

[monvani]

I wanted to, but I'm taking inputs on the form so it must be a table and not a query.

 

[RossWindows]

I wanted to, but I'm taking inputs on the form so it must be a table and not a query.

Even though you have set the form's allowedits and allowadditions properties to No, in addition to setting the Locked property of all the controls on the form to Yes?

Try this: copy and paste this verbatim

=DLookUp("[FirstOfrptColLbl]","qryColLbl","[rptPeriodMaster]= '" & Forms!frmShowActuals!month2 & "'")

 

[Galaxiom]

You can still have a query with input from a form. You just can't have Totals type fields in the query becuase these are not updateable.

However having looked closer at your database sample I can tell you the whole strategy is flawed. The Calendar table breaks normalisation rules throughout. Everything in it could be replaced by functions. Such as to find the last Friday of the month (I assume this is the basis of the periods), name the column by year and month or quarter etc.

The active periods should be derived by giving a starting period or a number of periods before today or something base on a function of such values like all last year plus this year's results.

You should start over with these kinds of strategies in mind.