Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    17

    Unanswered: SQL Where Date problem

    Hi,

    Could anyone give me a hand to figure out what is wrong with the SQL statment below. If i substitute the variable for the actual values i want it works ok, i am having a problem with the vdate variable part.

    Thanks in advance,

    Code:
    "SELECT tblBookings.BookingID FROM tblTutors INNER JOIN (tblStatus INNER JOIN (tblResources INNER JOIN ((tblOrganisations INNER JOIN tblBookings ON tblOrganisations.OrgID = tblBookings.OrgID) INNER JOIN tblResourceBookings ON tblBookings.BookingID = tblResourceBookings.BookingID) ON tblResources.ResourceID = tblResourceBookings.ResourceID) ON tblStatus.StatusID = tblBookings.StatusID) ON tblTutors.OrgID = tblBookings.OrgID WHERE (((tblBookings.StartTime)=#" & vDate & "#) AND ((tblResources.ResourceID)=" & vRow & ") AND ((tblBookings.StatusID)<4))")
    The Where section that i am having the problem with is;
    Code:
    WHERE (((tblBookings.StartTime)=#" & vDate & "#) AND ((tblResources.ResourceID)=" & vRow & ") AND ((tblBookings.StatusID)<4))")
    Last edited by jolpool; 08-11-11 at 10:22.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Is the format of vdate the same as that of the field in the table?

    In most cases it will recognise that 11 Aug 2011 = 11/08/2011 but it won't recognise that "11/08/2011 14:30" is 11/08/2011...

    If you haven't specified that vDate is date format, why not add a datevalue() into your SQL and see how that works?

    Code:
    WHERE (((tblBookings.StartTime)=" & datevalue(vDate) & ")

  3. #3
    Join Date
    Mar 2006
    Posts
    17
    Hi christyxo,

    Thanks for your reply. vDate is specifed as Date format. The field on the form also stores the full date and time.

    I have tried using your suggestion and i am getting the no current record error.
    Last edited by jolpool; 08-11-11 at 10:48.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the SQL you are actually sending to the SQL engine
    forget about your code, look at the actual values.

    to do this you need to assign the SQL to a variable and
    either display the SQL using a message box
    OR
    set a breakpoint on the Assignement and examine the values

    its very very easy to convince yourself that your SQL is correct be reading the code, its another thing to prove the SQL is correct by examing what you are actually sending

    if you do not know how to set a breakpoint, then now is a very very good time to learn about debugging VBA
    ms access debugging - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2006
    Posts
    17
    Hi healdem,

    Using the brakpoint i have check the vales that are being used in the variables and they are correct. If i substitute the vDate variabel for the actual value it works correctly.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so lets see what those values actually are:-

    my guess is that you are submittign a date in non US format
    Access expects dates in US format mm/dd/yyyy Or (i think ISO format yyyy/dd/mm)
    that would be my guess, or possibly the value of VDate is not a date.

    theres various solutions
    expressly convert to a date using cdate(myvariable)
    use format eg format(mycontrol,"nn/dd/yyyy")
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2006
    Posts
    17
    The values that i use to replace the variable that work are;

    #8/12/2011 9:30:0#

    The value will still need to include the date and the time. do i still need to convert the format to the mm/dd/yyy format or is it different beacuse the time is also needed?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this date and time retrospective or is it the time the this record is entered. the reason I ask is if its time the record was entered then you can use the now() function, if you dont' need the time then use the date() function.

    if you need to astore the date and time then you need to make certain its in the correct fromat #mm/dd/yyyy hh:mm:ss#
    How to store, calculate, and compare Date&#47;Time data in Microsoft Access
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2006
    Posts
    17
    Thanks Healdem,

    I finally managed to sort this out. I ended up needing to format the vdate correctly. I used the following code.

    Code:
     Format(StrConv(vDate, vbLowerCase), "\#mm\/dd\/yyyy\ hh:mm:ss#")
    Thank you very much!

Posting Permissions

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