Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2014
    Posts
    26

    Dlookup() With Multiple Criteria (Specifically: a String, & Two dates)

    Currently I have a problem using Dlookup function with the following statement:



    Code:
    DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# AND  [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy")&"#")
    the result from the Function is 'NULL', when it should be "£31785".

    if I remove

    Code:
      AND  [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy")&"#")
    and only have

    Code:
    DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# ")
    it displays the correct result,furthermore (with the working code with only one date criteria, above), If the [FromDate] is not in the current year, the result is 'NULL', the test date was 25/06/2016 which resulted in 'NULL'. The Test date was in the table [tblTaxBands] to show next years tax thresholds.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmm, it may be as simple as the extra space in your string.
    change
    AND [ToDate]
    to
    AND [ToDate]

  3. #3
    Join Date
    Jul 2014
    Posts
    26
    Well spotted on the extra space, however, sadly it did not solve the issue.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would move to a form to do some testing in the Visual Basic Editor. I mocked up some tests in notepad. As I did, I noticed one difference between the working code and the non working code. I do not believe it matters but ...

    in the working code you have this at the end
    ],"mm/dd/yyyy") & "# ")

    non working
    "mm/dd/yyyy")&"#")

    Anyway, maybe you can implement the following and view the results in the immediate window. Shortcut to display the immediate window is Ctrl + G

    Code:
    Dim strOne as string
    Dim strTwo as string
    Dim strThree as string
    
    strOne = "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# "
    
    strTwo = "[ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy") & "#"
    
    strThree = "[Band] = 'TaxBasic' AND [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy") & "#"
    
    debug.print "strOne = " & strOne & vbcrlf
    debug.print "strTwo = " & strTwo & vbcrlf
    debug.print "strThree = " & strThree & vbcrlf

  5. #5
    Join Date
    Jul 2014
    Posts
    26
    Code:
     strOne = [Band] = 'TaxBasic' AND [FromDate] >= # 04/25/2015# 
    
    strTwo = [ToDate] <= # 05/01/2015#
    
    strThree = [Band] = 'TaxBasic' AND [ToDate] <= # 05/01/2015#
    that is the results

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would try this
    Code:
    strTwo = "[ToDate] <= #" & format(forms![frm_Income]![ToDate], "mm/dd/yyyy") & "#"
    Your results show a space between the qualifier and the variable result. This may be creating an issue.
    [ToDate] <= # 05/

  7. #7
    Join Date
    Jul 2014
    Posts
    26
    OK i think i figured it out: My logic didn't match the statement in my code:

    Code:
    DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# AND  [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy")&"#")
    so this was asking for

    Code:
    [fromdate] (06/04/2015) >= forms![frm_Income]![FromDate] (25/04/2015)
    when I want it to be -
    Code:
    forms![frm_Income]![FromDate] (25/04/2015) >= [FromDate] (06/04/2015)
    &

    Code:
    [ToDate] (05/04/2016) <= forms![frm_Income]![ToDate] (26/04/2015)
    when I want it to be -
    Code:
    forms![frm_Income]![ToDate] (26/04/2015) <= [ToDate] (06/04/2016)
    The actual code i wanted was

    Code:
    DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND   format(forms![frm_Income]![FromDate],"mm/dd/yyyy") >= # " & [FromDate]  & "# AND  & format(forms![frm_Income]![ToDate],  "mm/dd/yyyy") <= #" & [ToDate] & "#")
    any solutions, cause VBA doesn't like that statement. I did think about turn the operators to the opposite (i.e. >= [to] <=) however, not too sure if that would be advisable.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You have an issue because you are not referencing a Date field within the table.

    Consider WHERE criteria ...
    WHERE [MyField] >= #01/01/2010#

    You cannot place a string literal where a Field belongs.
    WHERE #10/10/2015# >= #01/01/2010#

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Seem to recall another recent thread that had similar issue with using Format function on date parameter - although in a query WHERE clause if I remember right. Think I finally gave up.

    ItsMe - I have used criteria expressions that put the literal before the field, such as:

    DSum("Qty", "Sales", "#" & Me.tbxDate & "# BETWEEN [FromDate] AND [EndDate]")
    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.

  10. #10
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    Has anyone suggested this? -

    DLookup("MaxThreshold", "tblTaxBands", "Band = 'TaxBasic' AND FromDate >= #" & format(forms!frm_Income!FromDate,"mm/dd/yyyy") & "# AND ToDate <= #" & format(forms!frm_Income!ToDate, "mm/dd/yyyy") & "#")

    I suspect the problem may be that you do not have a table named [MaxThreshold] (i.e. with brackets in the name) or fields named [tblTaxBands] or [Band].

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    knarfreppep, the [] are not part of the name, they are delimiters that define the object names. If object names include spaces, special characters/punctuation (underscore is exception) or are reserved words, it is necessary to enclose them in [].

    So the expression you suggested has already been attempted, with or without [] makes no difference in this case.

    Look at the SQL statement built by Query Designer, Access will often automatically add the [] regardless of naming convention, especially when fields are used in expressions.
    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.

  12. #12
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    Sorry, but I respectfully suggest you are mistaken about what, in other circumstances, would be correct.

    What's between quotes ("") must in your case be literally there ... You don't have a table named [MaxThreshold] ... you do have one named MaxThreshold.

    Please open your frm_Income and enter dates then open a module and in the Immediate window paste the below and hit enter.


    ?DLookup("MaxThreshold", "tblTaxBands", "Band = 'TaxBasic' AND FromDate >= #" & format(forms!frm_Income!FromDate,"mm/dd/yyyy") & "# AND ToDate <= #" & format(forms!frm_Income!ToDate, "mm/dd/yyyy") & "#")

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I just tested and verified the [] do work when used within the quote marks. They act as object name delimiters. Data was correctly retrieved, however, not trying to use formatted date criteria.

    SELECT Airports.FAAID, DLookUp("[DistrictManager]","[DistrictInfo]","[DistrictID]='" & [District] & "'") AS T FROM Airports;

    If any of the object names had a space and I attempted this without the [], it would fail - also confirmed.
    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.

  14. #14
    knarfreppep is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Location
    Adelaide, Australia
    Posts
    106
    I've just created your table and done it with this -

    ?DLookup("MaxThreshold", "tblTaxBands", "Band = 'TaxBasic' AND FromDate >= #" & "12/01/14" & "# AND ToDate <= #" & "02/28/16" & "#")


  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    We know that literal date strings work. The issue appears to be Format function as noted in post 9.

    OP's dates appear to be international and is trying to reformat them to Access standard.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to use DLookup with string variable Criteria
    By JrMontgom in forum Programming
    Replies: 1
    Last Post: 03-09-2014, 07:55 PM
  2. Replies: 2
    Last Post: 01-22-2014, 09:38 AM
  3. Replies: 11
    Last Post: 04-30-2012, 07:22 PM
  4. Multiple Fields in Criteria - String
    By alsoto in forum Forms
    Replies: 3
    Last Post: 08-24-2011, 12:23 PM
  5. Multiple Criteria in a string
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 08-04-2010, 11:54 AM

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