Dlookup with multi Criteria

[rkalapura]

Hai,



I want to use a Dlookup() function with multi criteria of two Numeric fields in Access 2010. The function give me result when I use them individually. When I combine both criterias, it is not filtering records to t combined criteria. Can anybody tell me what I am doing wrong here? The following statement I use as the control source of that field:

IIf([ToolNumber]=0,"N/A", DLookUp("[bslocation]","[ballscoop]","[noofballs] =" & [Forms]![ASSEMBLYTOOLS]![ToolNumber] And "[ballsize] =" & [Forms]![ASSEMBLYTOOLS]![bsballsize]))

Fields [noofballs], [bsballsize] and [ToolNumber] are numeric

Second Question:

In a tabl, I want to use a field to enter data like 1/16, 3/32, 1/8, 5/16, 1/4 etc etc... and do calculations with that field, what format I should choose?. When I choose the field as number type, it converts the value to decimals. Can I use Text format and do calculations with those fields? Or is there any special type available in Access 2010 to accomodate such formats?

Thank you and best regards

[John_G]

Hi -

First question - your syntax in the DLookup is not quite right. The AND has to be inside the quotes as well:

IIf([ToolNumber]=0,"N/A", DLookUp("[bslocation]","[ballscoop]","[noofballs] =" & [Forms]![ASSEMBLYTOOLS]![ToolNumber] & " And [ballsize] = " & [Forms]![ASSEMBLYTOOLS]![bsballsize]))

Second question :

No, there is no data type to handle fractions, other than text. And if you use text, you would have to convert to numeric values all the time (The eval function will do that) to do calculations. And what would you do with the results? They would be decimal. Fractions are for humans, not computers, so stick with decimal values, if you can.

John

[rkalapura]

Thanks John for assistance. I did not tried it yet, probably tomorrow in my office. Regarding fractions, I was almost sure that it is not possible, but before taking a final decision, need an Access expert advise to confirm. Its acutally for some users who are well versed with fractions only specially when talking the sizes in our work area. I can make the drop down with both fraction and equivalant decimals, which will convenient for both users.

Thank you once again for your help. If any problem, I will come back
God Bless You.