DLookUp on form control not working for some strange reason

[McArthurGDM]

I came across an error I haven't seen before. I have a DLookUp function on a control on a form that works just fine. As is shown here

=DLookUp("Type","ReferenceT","MetricID = '" & [Forms]![MainNavigation]![NavigationSubform].[Form]![EditMetric].[Form]![EMMetricID] & "'")

So, it looks up the Field "Type" form the table "ReferenceT" and then finds the record relating to a particular ID. This works and it shows the value on the screen.

HOWEVER,

when I use the same function and instead change the field to a particular field, it stops working..

=DLookUp("Sub-Type2","ReferenceT","MetricID = '" & [Forms]![MainNavigation]![NavigationSubform].[Form]![EditMetric].[Form]![EMMetricID] & "'")

In fact, what happens is that the control flashes on the screen uncontrollably and doesn't stop.

I try a different field name

=DLookUp("Definition","ReferenceT","MetricID = '" & [Forms]![MainNavigation]![NavigationSubform].[Form]![EditMetric].[Form]![EMMetricID] & "'")



and it works again.

Now, the kicker here is that I have another field name "Sub-Type1" and try that, and AGAIN, it doesn't work.

My thoughts are that the issue lies in the defining of the field name. That DLookUps do not allow field names to take on this naming procedure with "-" or numbers.

Does anyone know if this is true? And if that is the case, then how do you get around this issue without having to rename your fields?

Thanks!

[June7]

Not strange at all. The hyphen is minus sign. The expression is trying to calc two non-existent fields. Try enclosing the field name in [].

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[McArthurGDM]

Ah, you are correct. The square brackets helped.

Thanks!

[CJ_London]

=DLookUp("Type","ReferenceT","MetricID.....

Also bear in mind that 'Type' is an access reserved word and using it will cause issues. Putting it in square brackets may solve the problem in some cases, but there are others where square brackets won't help and you will get an error you will spend hours trying to resolve.

here is a link to access reserved words

https://support.office.com/en-in/art...7-da237c63eabe