Date Type Mismatch Using DLookUp

[stampr18]

I keep getting a data type mismatch in the following code:

Me!Address = DLookup("[Address]", "TBLCustomer", "[CustomerID] = '" & Forms!FRMORDER!CustomerID & "'")

I am trying to lookup address when it matches the customer id.

This is how the customer id is coded:

SELECT DISTINCT [TBLCustomer].[CustomerID], [FirstName] & " " & [LastName] AS [Sold To] FROM TBLCustomer ORDER BY [FirstName] & " " & [LastName];

I think the type mismatch is coming from the first and last name being put in the field instead of the customerid?

Any help on how to fix this would be appreciated.

Thanks,
Nancy

 

[boblarson]

Is customer ID really a text field? That's what you are telling it. So, if it isn't, get rid of the single quotes.

 

[stampr18]

I did. It now tells me I "can't assign a value to this object"

 

[Bodisathva]

because you're trying to modify the Address field of the recordset, not a textbox on a form. the ! denotes a field, which you cannot alter without a .AddNew or .Edit ... change to .

Me.Address

 

[boblarson]

Don't use the Bang operator. Use Me.Address = instead. Also, if Me.Address is bound to a field named address in the same table as the Dlookup is looking, then it would not be able to be set using a dlookup to the same table.

 

[neileg]

The form wizard always names the controls the same as the underlying field. Flippin stupid if you ask me!

 

[boblarson]

The form wizard always names the controls the same as the underlying field. Flippin stupid if you ask me!

Yes, I agree - one of many of Microsoft's worst blunders.