DLookup issues (1 Viewer)

[JS Smith]

The setup:
Table BuyOut includes in it's columns Company (Short Text) and Contract Number (Short Text)
Form COLog includes in it's text boxes Contract # and Company
Form COLog feeds table COLog. Both Contract # and Company are Short Text

Goal: User keys in a Contract # and the Company field is autofilled on the form with the corresponding Company name.
Reasons: User data entry issues; we never get the same Company name twice. It'll alert the user if they typed the contract# incorrectly. We'll want this for upcoming reporting, mail merges and a few other things so accuracy is required.

Understandings:
It's a big faux pas to have same data in different tables; this is designed to be a proverbial snapshot in time. This is how the business wants it.
It's probably better to have contract # be called the same name in both places but I'm dyslexic and this helps me not scramble everything up.

I've played for hours with many iterations of the DLookup and fallen down many Google rabbit holes, the latest attempt is:

=DLookup("[Company]","[99BuyOut]","[Contract Number]=" & Forms!COLog!Contract #)

Error: The expression you entered has an invalid date value.

How should this thing read? I can no longer think logically how to proceed. Thanks, in advance. for any help and guidance you can provide.

 

[Isaac]

What is the name of the form on which resides a control named Contract #
Do you really have a control named Contract #? Space and everything? this should not be...

You might solve your problem with some simple bracketing, but I want to be sure of the answers to those questions first

 

[JS Smith]

Form is called COLog
Yes it's called Contract # until I have the DLookup sorted. I need to be able to tell the difference between the Form stuff and the Table stuff; see previously mentioned dyslexia issue.

 

[Isaac]

Form is called COLog

Okay, so...what is all the red highlighted stuff?

M__99_COLog_Test

Make sure your form and your control do not have spaces or symbols or characters in the name.

And is this dlookup residing in a control on the same form where [Contract #] control is?

 

[JS Smith]

D'oh, thought I corrected all the text. The stuff in red has been omitted in my original post. I had name different while designing this to make it easier for me to read.

I had the dlookup on the COLog form, in the Company field. I wish I could include the DB but it's too large and full of Company data. Maybe a picture of the form will help?

DLookup looks to me very similar to Excel's Index/Match; i.e. look here on this spreadsheet, match the value on another spreadsheet then give me the corresponding value in the adjacent field. Do I understand DLookup properly? This month is first time playing in Access...

 

[arnelgp]

=DLookup("[Company]","[99BuyOut]","[Contract #]='" & Forms!COLog![Contract #] & "'")

 

[JS Smith]

@arnelgp your formula gave circular reference error. Tried with various combinations using "Contract #" and "Contract Number" but did not have success. Tried to create some workable sample and cleaned any company data.

Attachment is a close analog of my DB. Yep, the naming convention is bad. Will edit later ;-)

 

[Pat Hartman]

Where is the code that is failing? I don't see it in the form.

You should probably fix the bad names sooner rather than later. The original error message which is talking about a "date" is because the # is the delimiter for a date field and that is what confused Access. Arnel's suggestion should have fixed the problem by encapsulating the bad column name inside square brackets.

 

[arnelgp]

here, test this one.

 

[JS Smith]

here, test this one.

Works like a dream! Can you explain why/how so I can learn?
I saw the filter on the form. I think this is you fix but don't understand all the syntax.

(M__99_COLog_Test.[CO Number]="YMC1")

 

[JS Smith]

Where is the code that is failing? I don't see it in the form.

You should probably fix the bad names sooner rather than later. The original error message which is talking about a "date" is because the # is the delimiter for a date field and that is what confused Access. Arnel's suggestion should have fixed the problem by encapsulating the bad column name inside square brackets.

Sorry Pat, I must've forgot to put it into my sample DB. I had @arnelgp's code in the Company's Control Source

 

[JS Smith]

Can anyone explain why (M__99_COLog_Test.[CO Number]="YMC1") is working?

What does the "YMC1" mean?

 

[arnelgp]

(M__99_COLog_Test.[CO Number]="YMC1")

when you Save the form, access saves the current filter.
but this has no effect since the other Property (Filter On Load) is set to No.