none
No records retieved RRS feed

  • Question

  • Hi again,

    I have a form with 2 text boxes with dates. I then retieve a record from a table. I need to know if the date in the table is between the 2 dates in the text boxes, but it never results in TRUE.

    My SQL statement:

        Set Dbs = CurrentDb
        Sql = "SELECT * FROM Tbl_Facilitators WHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
        Set rst = Dbs.OpenRecordset(Sql)

    I manupilate the date in the table to fall between the 2 dates in the text boxes, but it alway result in FALSE.

    Thanks

    Deon

    Wednesday, January 10, 2018 1:51 PM

Answers

  • Hi all,

    I found the solution - I changed the SQL statement as follows and it resolved the problem:

        Sql = "SELECT * FROM Tbl_Facilitators WHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN " & Format(StartDate, "\#m-d-yyyy\#") & " AND " & Format(EndDate, "\#m-d-yyyy\#") & ";"
        Set rst = Dbs.OpenRecordset(Sql)

    Thanks

    Deon

    • Marked as answer by Deon SA Wednesday, January 10, 2018 2:13 PM
    Wednesday, January 10, 2018 2:13 PM
  •     Sql = "SELECT * FROM Tbl_Facilitators WHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN " & Format(StartDate, "\#m-d-yyyy\#") & " AND " & Format(EndDate, "\#m-d-yyyy\#") & ";"

    Hi Deon,

    If you put your findings in a small function, for instance:

    Function As_date(cur_date) As String
        As_date = Format(cur_date, "\#m-d-yyyy\#")
    End Function

    And put this function in a general module, then you have solved this problem for once and always.

    The Sql string now writes (and reads) very easy:

    Sql = "SELECT * FROM Tbl_Facilitators" _
    & " WHERE FacilitatorName = " & As_text(Text1A) _ & " AND DateNotAvailableFrom BETWEEN " & As_date(StartDate) & " AND " & As_date(EndDate)


    As you can see, I did the same for String types with a function As_text. This function As_text  can also accomodate for the use of single qoutes (') in names.

    Finally, I placed the space after  Text1A in frront of a new line instead of behind the line. Now it is far easier to see if you have all the necessary separating spaces.

    Imb.




    • Edited by Imb-hb Wednesday, January 10, 2018 2:42 PM type
    • Marked as answer by Deon SA Friday, January 12, 2018 7:12 AM
    Wednesday, January 10, 2018 2:40 PM

All replies

  • Hi all,

    I found the solution - I changed the SQL statement as follows and it resolved the problem:

        Sql = "SELECT * FROM Tbl_Facilitators WHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN " & Format(StartDate, "\#m-d-yyyy\#") & " AND " & Format(EndDate, "\#m-d-yyyy\#") & ";"
        Set rst = Dbs.OpenRecordset(Sql)

    Thanks

    Deon

    • Marked as answer by Deon SA Wednesday, January 10, 2018 2:13 PM
    Wednesday, January 10, 2018 2:13 PM
  •     Sql = "SELECT * FROM Tbl_Facilitators WsHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"

    Hi Deon,

    Are  StartDate  and  EndDate  in the American or ISO notation?

    Imb.

    Wednesday, January 10, 2018 2:19 PM
  •     Sql = "SELECT * FROM Tbl_Facilitators WHERE FacilitatorName = " & "'" & Text1A & "'" & " " _
            & "AND DateNotAvailableFrom BETWEEN " & Format(StartDate, "\#m-d-yyyy\#") & " AND " & Format(EndDate, "\#m-d-yyyy\#") & ";"

    Hi Deon,

    If you put your findings in a small function, for instance:

    Function As_date(cur_date) As String
        As_date = Format(cur_date, "\#m-d-yyyy\#")
    End Function

    And put this function in a general module, then you have solved this problem for once and always.

    The Sql string now writes (and reads) very easy:

    Sql = "SELECT * FROM Tbl_Facilitators" _
    & " WHERE FacilitatorName = " & As_text(Text1A) _ & " AND DateNotAvailableFrom BETWEEN " & As_date(StartDate) & " AND " & As_date(EndDate)


    As you can see, I did the same for String types with a function As_text. This function As_text  can also accomodate for the use of single qoutes (') in names.

    Finally, I placed the space after  Text1A in frront of a new line instead of behind the line. Now it is far easier to see if you have all the necessary separating spaces.

    Imb.




    • Edited by Imb-hb Wednesday, January 10, 2018 2:42 PM type
    • Marked as answer by Deon SA Friday, January 12, 2018 7:12 AM
    Wednesday, January 10, 2018 2:40 PM
  • Thanks Imb. This is even a better solution.
    Friday, January 12, 2018 7:12 AM
  • Thanks Imb. This is even a better solution.

    Hi Deon,

    Good to hear!

    Structure in code is as important as structure in data.

    Imb.

    Friday, January 12, 2018 8:41 AM