Results 1 to 3 of 3
  1. #1
    rkalapura is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    New York
    Posts
    69

    Dlookup with multi Criteria

    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

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

  3. #3
    rkalapura is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    New York
    Posts
    69
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. dlookup with a contains criteria?
    By noretoc in forum Access
    Replies: 3
    Last Post: 05-02-2012, 10:18 AM
  2. Multi Criteria Searchform
    By cap.zadi in forum Queries
    Replies: 31
    Last Post: 01-20-2012, 01:56 PM
  3. Multi Criteria Query
    By hawkins in forum Queries
    Replies: 1
    Last Post: 07-18-2011, 01:44 PM
  4. Replies: 1
    Last Post: 06-17-2011, 12:59 AM
  5. Multi Based Criteria for SubReport
    By lostfan789 in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:27 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums