Sub QryReport()
    On Error Resume Next
    Dim QryDef As QueryDef
    Dim str As String
    Set QryDef = dbase.QueryDefs("balancesheet")
    If Err.Number = 3265 Then
        Set QryDef = dbase.CreateQueryDef("balancesheet")
    End If

        str = "select *  from [balancesheet]" & _
              " where Date ='" & Dtpicker.Value & "'"

    QryDef.SQL = str
    End Sub

here show the all data. but the sql command not work. I mean when I select a date the report didn't show that date's data. it show all data. what problem ?

Try the following syntax -

str = "SELECT *  from [balancesheet] WHERE Date = DateValue(" & "'" & Dtpicker.Value & ")" & "'"

You can also choose a specific date as in -

str = "SELECT *  from [balancesheet] WHERE Date => DateValue(" & "'" & Dtpicker.Value & ")" & "'"

str = "select * from [balancesheet] where Date ='" & Dtpicker.Value & "'"

Make sure that you refer to 'Date' above as a field in your database. If you have named your date field something different, then use that name syntax i.e. your date field name is 'DateOfRecord' your code will look something like -

str = "SELECT *  from balancesheet WHERE DateOfRecord=DateValue(" & "'" & Dtpicker.Value & ")" & "'"str

Also get rid of the [] tags, it is only used when there is a space in your data table name for instance balance sheet will become [balance sheet].

hope this solves your problem.

not work. more thing:
1. I use query (balancesheet)
2. in dataenvironment: data object - view , object name - balancesheet. in grouping tab - field used grouping - category. in Aggregates tab - name: total, function: sum, Aggregate on: grouping, Field: Amount.
here I do it, to see the sum of category. it work, I mean it show the total sum by category. but the above code i use to see the sum of category by date. I mean I select a date and the total sum will show by this date.

This article has been dead for over six months. Start a new discussion instead.