Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    61

    Question Unanswered: need help in VB Code

    Hi All
    I have this Code
    Dim fd2 as date
    dim td2 as date
    dim f1 as string

    fd2 = text9.text
    td2 = text10.text
    f1 = datacombo1.text

    ----- NOTE --- all the date format is dd/mm/yyyy
    ------ etc
    RS.Open "Select " & F1 & ", Sum(Qty) As Qty From CarSales WHERE CCDate Between #" & fd2 & "# and #" & td2 & "# Group By CarSales." & F1 & ""

    the proble is
    when i enter the the date like this
    text9.text = 1/2/2006
    text10.text = 1/2/2006

    it gives me nothing and there is no recored even that i have records with the date 1/2/2006

    and if i enter the date like this
    text9.text = 2/1/2006
    text10.text = 2/1/2006

    its gives me the informations for month 2
    i dont know why this happen

    anyhelp
    Sami Al Damiri ...

  2. #2
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    Hi Sam76,

    Could it be the case of your variable (f1 vs F1)?
    or the value which is entered into the combo box which is linked to the variable?

    Cheers,

    5p0ng3b0b

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    Are you connecting to an Access DB?

    If so the SQL query only accepts dates in the American Formet mm/dd/yyyy. Even if the date is in dd/mm/yyyy in the database.

    Hope this helps.

  4. #4
    Join Date
    Aug 2006
    Location
    Dewsbury, UK
    Posts
    9
    if that's the issue, you can change it in the variable instance, I also just thought about the fact that you're comparing text against a date.

    you could use:
    Format(CDate(text9.text),"dd/mm/yyyy")

    hope that helps?

    5p0ng3b0b
    www.benmoorhouse.blogspot.com

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, remember that a date field in Access is actually a double, containing the day number and the decimal portion of the day. 1.45 would represent 10:48:00 AM on December 31, 1899. (Since the zero date is 12:00:00 AM on Dec 30, 1899.) If you don't specify a time, Access assumes midnight of the date in question.

    However, this also implies that you are looking for a date that falls between two identical dates at midnight. I would suggest formatting to include start and end times as well.

    in other words,
    Code:
    select Field1, Field2 from Table1 Where DateField Between #1/2/2006 00:00:00# And #1/2/2006 23:59:59#
    (actually, the 00:00:00 isn't needed, as it is assumed...)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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