Database Dlookup() Function throwing Error

Very simple but I don't know what I am missing? It's throwing error

Oopss... I got it

I am just missing the single quotes

Q: DLookUp("[Order]","tDesg","[Desg]='" & [RDesg] & "'")

Hi,

I'd first try to replace the [DResg] with a number or word and see if Access likes it.

Access is all about naming conventions. Second I'd try to put Forms.[DResg] in the build function.

Third - Instead of using DLookup between two table, bring the second table into the query and link two fields together and then drag both down to the field area and make the criteria correct for the field you want.

Glad you got this one on your own. Syntax and building queries in Access is harder than in Excel.

Thanks for joining in my thread and offering some more tips on my question

Originally Posted by MarvinP

I'd first try to replace the [DResg] with a number or word and see if Access likes it.

Yes, you are right numbers don't need single quote in DLookup() Criteria but Text needs single quotes

Originally Posted by MarvinP

Third - Instead of using DLookup between two table, bring the second table into the query and link two fields together and then drag both down to the field area and make the criteria correct for the field you want.

I kept it in the same way as you described, but after that I realized keeping link between two tables created a problem. I will explain the problem I faced because of the linking...

I kept Designations in a table with Serial Numbers (For Sorting based on levels) so that users can add or delete the designations as per their requirements. So based on that the Employees Designation entry Drop Down List will populate the current Designations at the time of creating new employee.

I kept both the designations and the employees entry linked each other and made some entry and it seemed to be working fine.

But the real problem was identified when I change/add the designation order and the sl number in the designation table then all the data in the employee table reflected a new value instead of the actually picked value.

For example:

When I did entry in employee table I selected Manager which is 2 in sl number
I Changed the Designation table with Manager as sl.no 5 and VP as 2
In the next second all the entries in the employee table was automatically changed to VP instead of the actual entry Manager

May be I did something wrong while building relationship. But I don't know what it is...

So I completely stopped using Links and trying to make use of Functions