Dllookup returning a numeric value as text (1 Viewer)

I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. Can anyone see in the following syntax what could be wrong?

DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")

Your syntax is wrong. I'm surprised that it actually returns something and doesn't just result in an error. It should be;

DLookUp("SSER","TABSSS","[grosspay] Between " & [TABSSS].[Minrange] & " And " & [TABSSS].[Maxrange])

Although, I'm not sure DLookup is necessarily what you want here. If you're using the Between operator, that would normally indicate that you are looking for all records that fall within a certain range. DLookup will only return the first record found, regardless of how many may actually meet the criteria.

Thanks Beetle,

I put your syntax in and it started returning other errors (no operator). You eluded to the fact I may be using the wrong approach. Let me describe in more detail what I am attempting.

I have a query which has [GrossSalary] calculated field for each employee. I want to return the social security amount [SSER] from a second table [TABSSS] into that query where [GrossSalary] falls between the values of [Minrange] and [Maxrange] which is also in the [TABSSS] table.

TABSSS looks like:

SSER, Maxrange, Minrange
100, 000, 999
200, 1000, 1999
300, 2000, 2999

if [GrossSalary] = 1540, then I want to return 200 [SSER] (a single value and not a range) into the query for each Employee.

Am I approaching it all wrong or maybe the Dlookup needs tweaking based on this info?

If your answer output is right and problem with only datatype text. then just use "Val" Function or CDbl function for converting same. example: Val(Dlookup(.......................)).

Maosc,

The simple solution. I love it. Problem solved. Thanks!

Although I feel like a bit of a ditz! Thanks for taking your time to help a "special needs" guy. I also appreciate the lack of derogatory comments which I so obviously deserve in retrospect. :banghead:

Woody