dlookup problem

[Naz]

Hi

I have a textbox on a form that should pull out the number of employees for
a manager from a query based on an unbound field on the form called userid. I
have used the formula

=IIf([Myself]=-1,DLookUp("[countofemps]","qry_HP_Keystatistics","[reports_to] = '" & [userid] & "'"),"-")

but it keeps showing "error", anyone know where i am going wrong?

All help is greatly appreciated

 

[Linq Adams via AccessMonster.com]

1) What error are you getting?

2) Where does the [userid] in your Criteria argument come from?

3) Is [userid] defined in the underlying table as Text or Number Datatype?

 

[John W. Vinson]

but it keeps showing "error", anyone know where i am going wrong?

If Userid is a Number datatype field, lose the quote delimiters:

=IIf([Myself]=-1,DLookUp("[countofemps]","qry_HP_Keystatistics","[reports_to]
= " & [userid]),"-")

Actually if it's a count you want, you might want to use a DCount() function
to count them, rather than calling some totals query - cut out the middleman.

 

[Naz]

Hi

1) The error message i get is #error
2) [Userid] is a simple unbound text box, where a managers name is input
3) [Userid] is unbound so i don't know, but i guess it would be text

 

[John W. Vinson]

Hi

I have a textbox on a form that should pull out the number of employees for
a manager from a query based on an unbound field on the form called userid. I
have used the formula

=IIf([Myself]=-1,DLookUp("[countofemps]","qry_HP_Keystatistics","[reports_to] = '" & [userid] & "'"),"-")

but it keeps showing "error", anyone know where i am going wrong?

All help is greatly appreciated

What is the datatype of [reports_to]? If it's text, is it a Lookup Field (in
which case it really isn't text but rather a concealed Number ID)?