Results 1 to 6 of 6
  1. #1
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194

    Need a helping hand moving forward in project... multiple Dlookup criteria??

    Hi pplz!

    So ill explain my whole situation in the hope i can keep this thread going for some help along the way!!

    Im designing my first DB using access for the pharmacy I work at.
    Im very fluent at Excel but still learning Access, (lots every day).

    The DB is going to be used to:
    Store customer details (First, Last names, Insurance company and Workers comp number)
    Store their prescription details
    Enter an invoice that refers to the prescriptions (which shows that the insurance company has paid and what they paid)


    My current journey:

    Created Tables for:
    - Customer
    - Insurance Companies
    - Drug List
    - Script information
    - Single invoice info
    - Batch script data
    - Batch Invoice data


    Pretty much i have related all fields and used lookup values which works really well.
    Customers have their scripts allocated to them, and the invoices are added and i can see which scripts have an invoice added and which ones are still waiting payment.


    My issue:!



    When an invoice comes in, I have the fields "Script ID invoiced", "amount invoiced ($)"
    however right now i say "script 1 has been paid" using the script ID from the Script table (which then gives info on the actual script) HOWEVER i want someone to be able to use this and instead of looking up the script ID I want them to be able to put in a field "Date of script", "drug invoiced", "Customer invoiced" then in the 4th field it will return the Script ID by looking up those 3 features in the script tables and bringing back the specific script that corresponds to them.

    eg.

    instead of
    Script ID Amount
    1 $35.00


    I want

    Date of script Customer name Drug Amount (ScriptID Hidden)
    12/12/12 Bob Smith Viagra 100mg $35.00 1


    Any help/direction or even the name of the kind of calc i should learn about would be amazing... im getting really annoyed as i cant find the answer!!!!

    GLenn

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use as many criteria as you need in domain aggregate function. An example for date, text, number fields:

    DLookup("ID", "table or query name", "[date field]=#" & control1 & "# AND [text field]='" & control2 & "' AND [number field]=" & control3)

    This will require all 3 parameters to be provided. If you want more flexibility, review http://www.allenbrowne.com/ser-62.html


    Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    Thanks June7 ill give it a crack!

  4. #4
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    K that worked which is awesome, however if i load this form in another form as a sub, none of these vba codes work...

    How can i get this to work as a subform?

    Form is MasterInvoice
    Subvoice is Invoice

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What VBA code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    nvm i figured it out, the subform was running as a table not as a form, so its macros werent working!

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

Similar Threads

  1. Replies: 2
    Last Post: 01-22-2014, 09:38 AM
  2. Using DLookup with multiple criteria
    By halt4814 in forum Access
    Replies: 2
    Last Post: 04-08-2013, 12:26 PM
  3. Replies: 11
    Last Post: 04-30-2012, 07:22 PM
  4. Multiple criteria in DLookup
    By crowegreg in forum Forms
    Replies: 7
    Last Post: 06-22-2011, 01:47 AM
  5. Total noob needs a helping hand?
    By Naz in forum Access
    Replies: 7
    Last Post: 01-28-2010, 08:35 AM

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