Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Date Range VBA Code Help

    Hi,

    I am picking up data from a table that satisfy my date condition thru following code.

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption " & _
    "WHERE ((ShiftDate)> #03/08/2006#) " & _
    "order by RolNo, RollSize;")

    This runs fine.

    Now, I have placed 2 textboxes named "From" and "To" on form F_PeriodicPaper_Consum.

    I need to know what would be the code in Where clause of the above code if I need to get the data from same table but the date range must be pick up from form F_PeriodicPaper_Consum's text boxes i.e. "From" and "To" ?

    Thanks in advance.

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You can refer to the values in ojbects on your current form using Me!... So the value in the box called From would be Me!From.

    So you code becomes...

    Code:
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption WHERE ((ShiftDate)> #" & Me!From & "# And ShiftDate< #" & Me!To & "# ) order by RolNo, RollSize;")
    I've put it all on one line for clarity. If you are going to put this on seperate lines, make sure you break it up in the string parts not the value parts.

    I've a feeling you might have a problem with the date being in non-USA format i.e. I think you have to feed the date in USA format mm/dd/yyyy. There's some code out there somewhere for converting. But try it and see.

    hth
    Chris

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Chris,

    Excellent ! ! !

    It works now. I just made it >= and <= due the requirement.

    Regarding the date format I am comfortable with UK date format as my client like the same.

    Thanks a lot for immediate attention.

    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Ashfaque
    Regarding the date format I am comfortable with UK date format as my client like the same.
    You might like it and your client might like it too but Bill just doesn't.

    Try entering a few dates that could be interpreted either as dd/mm/yyyy or mm/dd/yyyy (e.g. 4th september, 12th July etc) and scrutinse the results. Be certain the records that are returned are correct.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Set Rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption WHERE ShiftDate BETWEEN #" & _
    Me!From & "# AND #" & Me!To & "# ORDER BY RolNo, RollSize;")


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks again.

    CyberLynx ; Your code line also works the same. May be it might have increased little speed because you removed () after Where Clause. Thanks.

    Pootle : I will check it little later. Thanks for the advice.

    With kind regards,
    Ashfaque

Posting Permissions

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