Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30

    Red face Unanswered: problems with sql in rowsource...

    hello all,

    I am having problems with the following rowsource in my on_open form script:

    CallsList.Rowsource = "SELECT [Call Type] FROM Billing
    WHERE ([Billing].[Date] Between '" & Me.startdate & "' And '" & Me.enddate & "')"

    When the form is opened the calls list is populated with nothing however there is records in the table between those two dates. startdate and enddate are just textfields with a data sourced from a field of type date from another table.

    Any ideas on what is going on here? Any help would be greatly appreciated.

    Cheers.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: problems with sql in rowsource...

    Originally posted by chillylounge
    hello all,

    I am having problems with the following rowsource in my on_open form script:

    CallsList.Rowsource = "SELECT [Call Type] FROM Billing
    WHERE ([Billing].[Date] Between '" & Me.startdate & "' And '" & Me.enddate & "')"

    When the form is opened the calls list is populated with nothing however there is records in the table between those two dates. startdate and enddate are just textfields with a data sourced from a field of type date from another table.

    Any ideas on what is going on here? Any help would be greatly appreciated.

    Cheers.
    Try Between #" & Me.startdate & "# And #" & Me.enddate & "#"

  3. #3
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    hmmm i tryed that. now it is actually showing some data but it is still out of the range....

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by chillylounge
    hmmm i tryed that. now it is actually showing some data but it is still out of the range....
    Try using greater than >, less that < logical operators instead of between.

    Gregg

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That won't help gregg ... BETWEEN is a "Greater Than or Equal to" and a "Less Than or Equal to" construct .. It includes the endpoints...

    Chilly,

    On your dates, did you try to use the CDate function? Ex: CDate(Me.startdate) ... Try that. You may also want to pop a messagebox displaying the resultant SQL statement to verify that it looks proper ... Also Verify that the start and end dates are proper (i.e. the start is <= to the end date) That also would screw things up ...

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by M Owen
    That won't help gregg ... BETWEEN is a "Greater Than or Equal to" and a "Less Than or Equal to" construct .. It includes the endpoints...

    Chilly,

    On your dates, did you try to use the CDate function? Ex: CDate(Me.startdate) ... Try that. You may also want to pop a messagebox displaying the resultant SQL statement to verify that it looks proper ... Also Verify that the start and end dates are proper (i.e. the start is <= to the end date) That also would screw things up ...
    M,

    In my mind I was including them. Should have typed what I was thinking. They're also comparison not logical operators.

    Gregg

  7. #7
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    I am still getting results outside the range. My startdate is 24/08/03 and enddate is the 03/09/03. I have set my regional settings to display the date as dd/mm/yy but do I have to change anything in the format in access.....the fields are set to "Short Date" and displaying correctly.

    Here is my query thus far:

    Me.CallsList.RowSource = "SELECT [Call Type], Date FROM Billing WHERE ([Billing].[Date] between #" & CDate(Me.StartDate) & "# And #" & CDate(Me.EndDate) & "#)"

    Still no luck though :-(

  8. #8
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30
    Also, I did a text output of the query in a message box and the where statement looks like "Where ([Billing].[Date] between #24/08/03# and #03/09/03#)"

    Which looks fine also! .... this is driving me crazy

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by chillylounge
    Also, I did a text output of the query in a message box and the where statement looks like "Where ([Billing].[Date] between #24/08/03# and #03/09/03#)"

    Which looks fine also! .... this is driving me crazy
    I know what you mean. Well this probably won't help (I'm sure you love to hear that) but I set up an unbound listbox and two unbound textboxes on a form and was able to set the rowsource of the listbox and retrieve the values that I selected. I used a query first to test the syntax and the results. The between operator worked fine. Here's mine...


    strSQL = "SELECT tblWO.InDate FROM tblWO WHERE tblWO.InDate Between #" & Me.txt1 & "# And #" & Me.txt2 & "#"

    MsgBox strSQL --Just to see what it looked like.

    lst1.RowSource = strSQL

    The only thing I did was to set the listbox type for Table/Query and the textbox formats to short date. If it were me, I would try setting the date format for the system to mmddyy and see what happens.

    Good luck. I'm sure it's a simple answer that's just well hidden.

    Gregg

  10. #10
    Join Date
    Oct 2003
    Location
    Brisbane, Australia
    Posts
    30

    success!!!

    Fixed it finally! Changed the regional settings back to mm/dd/yy and then changed all the date fields in the database to a custom format of dd/mm/yy and it works!

    Thanks for all your help Basicmek, was very much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •