My Dlookup is returning a string (I want a number)

Expr1: DLookUp("(AllowanceAMount)","tblAllowances","AllowanceID=1")

This returns the correct value BUT as a string...

I want [AllowanceAmount] returned as a number (which is how it is held on tblAllowances).

What should I do?
Patrick O'DeaAsked:
Who is Participating?
Rey Obrero (Capricorn1)Commented:
in the design view of the query, select the column
right click and on the pop up menu select Properties

in the Format property, select the kind of format you want
Patrick O'DeaAuthor Commented:
Thank Rey,

I just learnt something!
If you are using this DLookup() in a query, then you should change your method to use an outer join (left) to tblAllowances instead.  This will return the data you want with far less overhead and will not change the data type.

the DLookup() runs a SEPARATE query for EVERY row you select.  So, if you select 100 rows, you are running 101 queries.  If you select a thousand rows, you are running 1,001 queries.

NEVER, use domain functions in queries (or code loops) unless there is no alternative and except in rare cases, there is always an alternative.
Patrick O'DeaAuthor Commented:
Thanks Pat,
Your words of caution are appreciated ....
I will re-engineer the query (without the Dlookup)

Many thanks once again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.