dlookup giving error

[rehanemis]

Hi,

I am using dlookup to retrieve value from table. I have set after-update even when user select a department name and got the department id into the field by using dlookup.

Me.DepID = DLookup("DepID", "DepMain", "DepName.value='" & Me.Department.Value & "'")

Just the problem is when apostrophe is used in department name it gives error.

Can anyone give solution how to solve the issue.

Thanks and waiting of your kind response.

 

[arnelgp]

Me.DepID = DLookup("DepID", "DepMain", "DepName = '" & Me.Department.Value & "'")

 

[rehanemis]

Hi,

But it is the same code i have posted. Can you please tell me how to resolve the issue when apostrophe (') is used in criteria and how to resolve it)

 

[Minty]

To be honest it would be better if your department was taken from a table with a DeptID so that the text description of the department would make no difference.

Table Departments
FieldName | Data Type
DeptID | PK unique
DeptDescription | text

You then store the DeptID in your main table rather than the name in text. If the Dept name is ever changed you just adjust the text description and miraculously all the details will update accordingly

 

[rehanemis]

let me try this one. thanks

 

[rehanemis]

Your suggestion works well.

Thanks a lot.