Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    Query in VBA and date range

    Hi all,

    I tried this query statement in VBA but in return no record.

    Dim dtDate1 As Date
    Dim dtDate2 As Date
    dtDate1 = Trim(Me.txtClosing1)
    dtDate2 = Trim(Me.txtClosing2)

    Set rs1 = dbs.OpenRecordset("SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" _
    & " and ([eff date]>= " & dtDate1 & " and [eff date]<=" & dtDate2 & ")")

    [eff date] is in datetime MS access format.

    This thing really made me confuse, as i see no reason for it to return 0 record. Please advice if you find anything wrong with my query.
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Hi

    Three things:
    1) Check out BETWEEN in help (passe Mike (passe Izy))
    2) You have enclosed your text with single quotes. You need to use hashes (or pound signs if you are from the US) i.e. # for dates.
    3) Are you working in MM/DD/YYYY format?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Try

    " AND ([eff date]>= #" & dtDate1 & "# and [eff date]<=#" & dtDate2 & "#)")

  4. #4
    Join Date
    Jul 2002
    Posts
    30
    Hi Hammbakka and HTH,

    Thanks for the reply. Aprreciate it.
    1) I've tried "BETWEEN"
    2) I'm from Asia and my date format is in dd/mm/yyyy
    3) Have tried using #

    Still returning 0 records.

    Any more ideas?

    Thanks

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Run a debug statement:

    Code:
    debug.print "SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" & " and ([eff date]>= " & dtDate1 & " and [eff date]<=" & dtDate2 & ")"
    Check that the output looks as you would expect. Run the output (copy/paste) as standalone SQL to check it's giving you the desired results. If it still gives 0 records then I think you have no records meeting the criteria.

    Chris

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Hi

    Howey's idea is always a good one though remember to insert the #s!

    VBA is a bit US centric. When you concatenate dates into a string it changes them to MM/DD/YYYY where ever possible (e.g. 31/01/2005 remains as is since it cannot be a MM/DD/YYYY date but 04/01/2005 is changed to 01/04/2005) irrespective of your local date\ time settings.

    Personally I would do something like:
    Code:
    Dim dtDate1 As String
    Dim dtDate2 As String
    
    dtDate1 = Trim(Me.txtClosing1)
    dtDate2 = Trim(Me.txtClosing2)
    
    IF Not ISDATE(dtDate1) OR Not ISDATE(dtDate2) THEN
    'Not two dates. Give some message
    ELSE
    Set rs1 = dbs.OpenRecordset("SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" _
    & " and [eff date] BETWEEN #" & dtDate1 & "# and #" & dtDate2 & "#")
    END IF
    HTH (HTH = Hope this helps )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2002
    Posts
    30
    Thanks all. Really fruitfull discussion.

    I manage to get the results i want.

    I just retype everything and swap the condition (where date between date1 and date 2 and transport co = cmbtransporter.

    I dont know why, but i guess, it's Window.

    Thanks mates...

  8. #8
    Join Date
    Jun 2006
    Posts
    103
    how about "SELECT * FROM table where dateExample BETWEEN DateValue('" & startDate & "') and DateValue('" & endDate & "')"

    DateValue('dateString') will make a date string of "05/05/2006") cast into a number of 3#### (sorry, i cant remember the number of this date example, to make the date a number so you can use between command.

    Hope this helps

  9. #9
    Join Date
    Jul 2002
    Posts
    30
    Definately will try on that. Thanks

Posting Permissions

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