none
String Assistance RRS feed

  • Question

  • Hello - 

    I am having some trouble with a string and I can't for the life of me figure out what/why the same string that works perfectly in other subs, is receiving an error code?

    The string in question:

    strWhere = "SvcDate BETWEEN #" & Reports![100_All_Youth_Development_Report].StartDate & "# AND #" & Reports![100_All_Youth_Development_Report].EndDate & "#"

    Here is the error I am receiving:

    Any ideas or suggestions on why this works in one sub and not another would be greatly appreciated as I am at a total loss of reasoning.

    Thanks in advance for looking and any assistance you may be able to provide!

    Tuesday, October 22, 2019 7:21 PM

Answers

  • Ok so I found the issue. Did some more research and found the below to be true. I located two controls on the report that were throwing the #Name error. Corrected this and this resolved the issue. 

    Thank you to those who spent time on this. Your efforts are much appreciated!

    • Marked as answer by rstreets2 Tuesday, October 22, 2019 8:46 PM
    Tuesday, October 22, 2019 8:46 PM

All replies

  • Try replacing the . with ! like:

    strWhere = "SvcDate BETWEEN #" & Reports![100_All_Youth_Development_Report]![StartDate] & "# AND #" & Reports![100_All_Youth_Development_Report]![EndDate] & "#"

    Tuesday, October 22, 2019 8:32 PM
  • Any ideas or suggestions on why this works in one sub and not another would be greatly appreciated as I am at a total loss of reasoning.

    Hi rstreets2,

    Is the report opened on the moment you get the error? It should.

    With dates I NEVER use the BETWEEN operator, but always something like:

        "SvcDate >= " & As_date(…!StartDate) & " AND SvcDate < " & As_date(…!EndDate + 1), to account for any (unintentional?) time on EndDate.

    As_date is a Function in a general module, that converts a date to the unambiguous ISO-notation and surrounds with ##.

    Imb.

    Tuesday, October 22, 2019 8:34 PM
  • Hi Lawrence - 

    Thank you for your response and suggestion. I tried your suggestion and am still receiving the same error :(

    Tuesday, October 22, 2019 8:37 PM
  • Ok so I found the issue. Did some more research and found the below to be true. I located two controls on the report that were throwing the #Name error. Corrected this and this resolved the issue. 

    Thank you to those who spent time on this. Your efforts are much appreciated!

    • Marked as answer by rstreets2 Tuesday, October 22, 2019 8:46 PM
    Tuesday, October 22, 2019 8:46 PM
  • Hi lmb and thank you for your response!

    I did figure out why I was receiving the error; and have since resolved utilizing my existing code. I do, however, appreciate and like your suggestion! I have not seen that method on the net anywhere when I was researching on how to create a string to capture data between two date ranges.

    I will play around with a little and see how it works. ;-)

    Thank you again for you response. Your time and suggestion is much appreciated!

    Tuesday, October 22, 2019 8:55 PM