Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    277

    Unanswered: VBA - Using Between in a WHERE Statement

    I have asked this before but the "answer" did not work

    Doing a search looking for a range of dates based off company name

    where1 = ??

    Main.ShipDate Between Date1 And Date2
    and = Company.Value

    please help

  2. #2
    Join Date
    Dec 2002
    Posts
    97

    Date values

    Are your dates in the code variables or field names?

    If they are field names you might have to a) bracket them and b) put them between # signs for the date value to work.

    Chris

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Why not go ahead anp post your SQL and we can take a look.
    Darasen

  4. #4
    Join Date
    Dec 2004
    Posts
    277
    thank you the # signs are what I was missing

  5. #5
    Join Date
    Dec 2004
    Posts
    277
    Quote Originally Posted by Darasen
    Why not go ahead anp post your SQL and we can take a look.
    Me.Refresh
    Dim where1
    Dim where2
    where1 = "[Main]![ShipDate] " & " Between " & "#" & range1 & "#" & " AND " & "#" & range2 & "#"
    where2 = " And [Main]![ShipperID]" & "=" & Shipper.Value & " And [Main]![ConsigneeID]" & "=" & Consignee.Value
    DoCmd.OpenReport "pickupsheet", acViewPreview, , where1 & where2
    range1.Value = Null
    range2.Value = Null
    Shipper.Value = Null
    Consignee.Value = Null

  6. #6
    Join Date
    Dec 2002
    Posts
    97

    Yep

    Those darn # signs....get you everytime!

  7. #7
    Join Date
    Feb 2005
    Posts
    19
    Don't forget the date has to be in the US format 'mm/dd/yyyy' So you may have to use format(range,"mm/dd/yyyy")

Posting Permissions

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