none
String Assistance RRS feed

  • Question

  • Hello -

    I am looking for some assistance with a string I pieced together from some examples found thru research. I am looking to open a report filtering data between two date periods.

    I think I am close, but still receiving errors.

    Here is what I have thus far:

    Dim strWhere As String

     

    strWhere = "SvcDate ='" & "BETWEEN #" & Forms!Preview_Report_Category_All.Beginning_Date & "# AND #" & Forms!Preview_Report_Category_All.Ending_Date & "#"

     

    DoCmd.OpenReport "100_ServiceHours_Report_allCategory_View ", acViewReport, , , strWhere

    If anyone has any ideas as to where I might be going wrong...lol...other than trying to be creative ;-) , please let me know. If there may be a better way of doing this I am open to suggestions.

    Thank you in advance for your time.



    • Edited by rstreets2 Wednesday, June 26, 2019 6:47 PM
    Wednesday, June 26, 2019 6:46 PM

Answers

  • It should look like this:

    strWhere = "SvcDate BETWEEN #" & Forms!Preview_Report_Category_All.Beginning_Date & "# AND #" & Forms!Preview_Report_Category_All.Ending_Date & "#"
    DoCmd.OpenReport "100_ServiceHours_Report_allCategory_View ", acViewReport, , strWhere

    Note in particular that there is one comma less in the DoCmd.OpenReport line.

    Remark: if there is any chance that your database will be used by someone who does not use mm/dd/yyyy date format, you should format the dates explicitly:

    strWhere = "SvcDate BETWEEN #" & Format(Forms!Preview_Report_Category_All.Beginning_Date, "mm/dd/yyyy") & "# AND #" & Format(Forms!Preview_Report_Category_All.Ending_Date, "mm/dd/yyyy") & "#"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Wednesday, June 26, 2019 7:03 PM
    Wednesday, June 26, 2019 6:52 PM

All replies

  • It should look like this:

    strWhere = "SvcDate BETWEEN #" & Forms!Preview_Report_Category_All.Beginning_Date & "# AND #" & Forms!Preview_Report_Category_All.Ending_Date & "#"
    DoCmd.OpenReport "100_ServiceHours_Report_allCategory_View ", acViewReport, , strWhere

    Note in particular that there is one comma less in the DoCmd.OpenReport line.

    Remark: if there is any chance that your database will be used by someone who does not use mm/dd/yyyy date format, you should format the dates explicitly:

    strWhere = "SvcDate BETWEEN #" & Format(Forms!Preview_Report_Category_All.Beginning_Date, "mm/dd/yyyy") & "# AND #" & Format(Forms!Preview_Report_Category_All.Ending_Date, "mm/dd/yyyy") & "#"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Wednesday, June 26, 2019 7:03 PM
    Wednesday, June 26, 2019 6:52 PM
  • Wow that was fast! Thanks Hans! You are a life saver once again! I had a feeling it was in the areas you corrected...the "SvcDate BETWEEN #" and the extra comma OpenRpt line. Just wasn't real sure where they should be.

    Anyhow, I can't thank you enough for all the support you have provided me! You are awesome!!

    Thank you, thank you, thank you!

    Best regards

    Wednesday, June 26, 2019 7:02 PM