Results 1 to 2 of 2
  1. #1
    robsworld78 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    181

    DLookUp with multi criteria including between date range


    Hi, I've been struggling to get a DLookup to work. I have a report that I want to look up some data.

    I'm trying to make a report that will print an old invoice based on a date and employee and show the correct inventory levels at that time. Right now I have it working so a person can print todays invoice however once new data is entered I can't get the lookup working for older data.

    I'm tracking inventory and have 2 bound fields, called "given" and "sold" in a table called "inventory" everyday when inventory is given to an employee its entered in a subform and at the end of the day the sold items are entered in as well. After the day is over the employee retains his/her inventory for the next day and more is added to it. So a balance of the inventory is kept and works fine.

    I have an invoice created which shows how much of each product the select employee had at the beginning of the day and how much they sold and then how much is remaining.

    Here's the DLookUp I use in my invoice report and on a form to give me the current inventory balance for a certain employee and product to date.

    Code:
    =DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]")
    Here's the Dlookup I use in my invoice report to give me the amount the day was started with.

    Code:
    =DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]")+[Sold]
    Those both work fine but now I'm trying to add a "between date" criteria to the DLookup as well. I made a form which asks for an employee and date to lookup the invoice however it only brings today's inventory balances even when I select a date from a week ago. I'm hoping I can have the date that's entered in the form reflect on the Dlookup.

    So I want the Dlookup to look up the same data I have it set to but only lookup that data from the beginning to the date selected in the form that opens the report not to todays date. The beginning date will always be the same obviously it just needs to stop at the date I select when I runt he report so my balances are for that date not today.

    I hope all this makes sense. I've tried so many different variations of Dlookup including the date but can't get it to only pull the data up to the date and do the math up to that point.

    Any help is greatly appreciated.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I have a simple replication of you problem here:

    1) I have a table with the following fields

    • ID (Primary Key)
    • empID (Number)
    • inDate (date)
    • given (Number)
    • sold (number)

    Now I have a simple for in which I have the following Objects:


    1. Text0 = For empID
    2. Text2 = Starting Date
    3. Text4 = Ending Date
    4. Text7 = To Display Total Given - Total Sold
    5. Command6 = Code is attached to the Onclick even of this button clicking on it the Result is displayed in Text7

    I have a attached to the OnClick Even of Command6

    Code:
    Dim tlGiven As Integer
    Dim tlSold As Integer
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    
    
    tlGiven = 0
    tlSold = 0
    
    strSQL = "Select * From tbinventory Where empID=" & Me.Text0 & " And inDate Between #" & Me.Text2 & "# And #" & Me.Text4 & "#;"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If rst.BOF And rst.EOF Then
        Me.Text7 = 0
    Else
        Do While Not rst.EOF
        tlGiven = tlGiven + rst!given
        tlSold = tlSold + rst!sold
        rst.MoveNext
        Loop
    End If
    rst.Close
    Set rst = Nothing
    
        
    Me.Text7 = tlGiven - tlSold
    Explanation:

    I have Used A DAO recordset which is the SQL stored in the string strSQL. You will notice criteria in the SQL it contains both employee ID and between date criteria which you wanted. A do loop is used to loop through the recordset and the total given and total sold is calculated

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

Similar Threads

  1. Date range
    By eacollie in forum Queries
    Replies: 7
    Last Post: 06-05-2011, 03:38 PM
  2. Passing Multi Select string to Query criteria
    By oleBucky in forum Queries
    Replies: 4
    Last Post: 05-15-2011, 02:11 PM
  3. Criteria to show date range
    By Douglasrac in forum Queries
    Replies: 2
    Last Post: 03-24-2011, 03:58 PM
  4. Date range help!!
    By bananatodd in forum Access
    Replies: 26
    Last Post: 10-18-2010, 01:57 PM
  5. Multi Based Criteria for SubReport
    By lostfan789 in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:27 PM

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