Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Unanswered: Problem in Date Range - Querry

    Hi,

    I am Using MS Access. I have a problem. In my Form There are two texts and I want to Fetch the data from a Table caled "Expense Details" between the date range of those two texts. My code is like :

    Dim TotalExpense As Integer

    Set db = CurrentDb()
    strsql = "Select * From [Expense Details] where [Expense_Date] Between #" & Me.Text0 & "#" & "and #" & Me.Text2 & "#"



    Set rst = db.OpenRecordset(strsql)

    If rst.RecordCount = 0 Then

    TotalExpense = 0
    Else

    rst.MoveFirst
    Do While rst.EOF <> True
    TotalExpense = TotalExpense + rst![cash spent]
    rst.MoveNext
    Loop


    Me.[Expenses & Advances] = TotalExpense
    End If
    But I am getting error like "Too Few Parameters..."

    Can u plz help me.

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Welcome to the forum

    I am moving this thread to the MS Access topic where it is morelikely to get a targeted response.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh and could you tell us which line is throwing the error please
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    missing space here --

    strsql = "Select * From [Expense Details] where [Expense_Date] Between #" & Me.Text0 & "#" & "and #" & Me.Text2 & "#"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well spotted Rudy, I didn't look at that line based on the error message!
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    If Access still isn't handling that expression, try using the DateSerial(year, month, day) function instead of a date literal. The parts can be extracted from the date using DatePart.

    Also: are you using DAO or ADO? It's a good idea to, e.g., Dim rs as DAO.Recordset to be certain. With DAO you may need more parameters to the method call rather than the SQL string, not so sure about ADO.
    Last edited by sco08y; 04-20-08 at 20:14.

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Oh, BTW, if you use date literals and you're not parameterizing your queries, at least protect yourself against SQL injection or invalid dates.

    "BETWEEN #" & CStr(DateValue(Text1.Value)) & "# AND #" & CStr(DateValue(Text2.Value)) & "#"

    This should throw an error for non-date values.

Posting Permissions

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