Operator and Null errors with Dlookup

[breezett93]

On a form, I'm trying to compare two dates. Date A is located in Table A, and Date B is located in Table B. They are related by both tables having the column LinkedColumn. LinkedColumn is a PK in Table B, but not in Table A.



The record source of the form is Table A; so I am trying to bring Date B onto the form using Dlookup. I'd like to avoid changing the record source because the form has a lot of other sections that rely on Table A.
The strangest part is that sometimes it works and sometimes it doesn't.

Here is the actual code of what I've been trying:

Dim dateB as variant     \\using As Date was also not working

dateB = Dlookup("Date B", "Table B", "Table B.LinkedColumn = Table A.LinkedColumn"
dateB = Dlookup("Date B", "Table B", "LinkedColumn = Table A.LinkedColumn"
dateB = Dlookup("Date B", "Table B", "LinkedColumn = Table​ B.LinkedColumn"
dateB = Dlookup("Date B", "Table B", "LinkedColumn = " & Dlookup("LinkedColumn", "Table A"))
dateB = DLookup("Date B", "Table B", "TableB.LinkedColumn =" & Forms![Form]!LinkedColumn)

There's probably several other combinations that I've tried that I am forgetting. Every error either says there is a operator error in the criteria part or an invalid use of null.

[davegri]

Probably because of spaces in object names. Either redefine and remove all spaces or enclose names with spaces in brackets [], like [Date B].

[breezett93]

Those aren't the actual names. Sorry for the confusion. All actual names are one word.

[John_G]

The last of the 4 options you listed should work with the change shown in red, and with blanks in the field and table names removed:

dateB = DLookup("DateB", "TableB", "TableB.LinkedColumn =" & Me!LinkedColumn)

The only things that might cause that to fail are a) there is no control called LinkedColumn on the form or b) the form contains a blank record, i.e. LinkedColumn is Null.

[June7]

Why not use real names?

LinkedColumn fields are number type?

Don't need VBA for this and if the form is Continuous or Datasheet, every record would show the same result. Put expression in textbox ControlSource: = DLookup("DateB", "TableB", "LinkedColumn =" & [LinkedColumn])

Alternatively, include TableB in the form RecordSource (join type "Include all records from TableA...") and bind textbox to TableB DateB field.

[breezett93]

The last of the 4 options you listed should work with the change shown in red, and with blanks in the field and table names removed:

dateB = DLookup("DateB", "TableB", "TableB.LinkedColumn =" & Me!LinkedColumn)

The only things that might cause that to fail are a) there is no control called LinkedColumn on the form or b) the form contains a blank record, i.e. LinkedColumn is Null.

Made that change and the exact error is:
Run-time error 3075: Syntax error (missing operator) in query expression 'OrdTbl.OrdId ='.

LinkedColumn/OrdId is definitely not Null. I entered test data myself. There is no control on the form for OrdId. There is a subform that displays part of a table with it though.

[breezett93]

Why not use real names?

LinkedColumn fields are number type?

Don't need VBA for this and if the form is Continuous or Datasheet, every record would show the same result. Put expression in textbox ControlSource: = DLookup("DateB", "TableB", "LinkedColumn =" & [LinkedColumn])

Alternatively, include TableB in the form RecordSource (join type "Include all records from TableA...") and bind textbox to TableB DateB field.

If the actual names are more useful, I can provide that.

LinkedColumn/OrdId is a number type in both tables.

The form is Single Form.

" Put expression in textbox ControlSource: = DLookup("DateB", "TableB", "LinkedColumn =" & [LinkedColumn])" gives me #Name? each time.

[June7]

Then Access can't find a name used in the expression. Make sure spellings are correct.

[John_G]

Is tableB the actual name of the table, or is it OrdTbl? Is "LinkedColumn" the actual name of the field in TableB/OrdTbl, or is it OrdID? Is DateB the actual field name?

Any one of those can cause the #Name error.

[June7]

OP already said in post 3 these are dummy names and offered in post 7 to provide real names. I expect breeze has now learned that using real names in post would be better.

[breezett93]

OrdTbl, OrdId, and ShpDt are the actual names.

[breezett93]

Then Access can't find a name used in the expression. Make sure spellings are correct.

I have triple checked my spellings. Everything is okay there. I did mentioned earlier that sometimes it will work and sometimes it will not.

[June7]

Assuming you are using those correctly in the expression, it should work. At this point, I think we need to see your db. If you want to provide, follow instructions at bottom of my post.

And what happens when it doesn't work - error message, wrong results, nothing?

[John_G]

Is there any chance that OrdTbl would not have a record where OrdID matches the value on the form? If the 'where' clause in the DLookup does not select a record, then the Dlookup return a Null, which may or may not generate an error, depending on how you use the Dlookup.

[breezett93]

Is there any chance that OrdTbl would not have a record where OrdID matches the value on the form? If the 'where' clause in the DLookup does not select a record, then the Dlookup return a Null, which may or may not generate an error, depending on how you use the Dlookup.

Good suggestion. I looked at IvcTbl (Table A) and noticed that some records do have a blank OrdId value. I then found that it looks like the OrdId is filled in later in the process than where I am currently trying to evaluate. But by then, it's too late as the record gets locked.

So I need to do some backtracking. Will post more questions if I have any.

Thank you.