Help I'm melting!!!

[Bebe]

Okay I have a cboMonth and a cboYear

My database has records from 10/4/94-present (in x/x/xx format) the field in my database is DATE_OPENED and the table is called MASTER. If my user selects January from the cbo box and "1994" from the other user box; what code would return the value of all records in for DATE_OPENED IN THAT TIME FRAME. I want a total of all the records in putted for January 1994. How do I do this when the format in the database is x/x/xx. I have asked for help with this before and got some date part type examples, but they did not work. How would I go about doing this anyone have sample code that could help me out. I also need to get totals such as all the Telephone calls made in a certain date range, etc. I know how to return the value of all the records for TELEPHONE, but I don't know how to extract them by date. I could really use some good sample code!!!

[svskumar]

I dont know which database ur using if ur using
SQL Server 7.0 see
DATEPART(datepart, date)
this should be good help

[Bebe]

im using a databound control data2 and an access 97 database thnks

[Pactor1]

Assuming you have some button to call the change of RecordSource for Data2 (in this example the button is called, cmdShowDate) you can use this code. You don't need to worry about date formating. If Access receives this query it will still understand the correct date range (infact using a three character code for the date assures you of getting the correct date, with no dd/mm confusion).

Private Sub cmdShowDate_Click()
    
    Dim strStartDate As String
    Dim strEndDate As String
    
    strStartDate = Format("10/4/94", "dd mmm yyyy")
    strEndDate = Format("01 " & cboMonth & " " & cboYear, "dd mmm yyyy")
    If CDate(strStartDate) < CDate(strEndDate) Then
        txtDate.Text = "SELECT * " _
            & "FROM MASTER " _
            & "WHERE DATE_OPENED BETWEEN '" & strStartDate & "' AND '" & strEndDate & "'"
    
        Data2.RecordSource = txtDate.Text
        Data2.Recordset.MoveFirst
    Else
        txtDate.Text = "Records do not go back to: " & strEndDate
    End If

        
End Sub

You may have to play around with the quote characters surrounding the dates in the query. Access doesn't use standard SQL for all things. To run this with Access you may have to try things like: the # character. eg:

txtDate.Text = "SELECT * " _
            & "FROM MASTER " _
            & "WHERE DATE_OPENED BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

Experiment a bit. This code does work I've used it myself many times before.

[Edited by Pactor1 on 04-12-2000 at 10:07 PM]

[LG]

Hi, Bebe.

You can try this code. I've used NWind database:

Private Sub Form_Load()
Dim i As Integer
Dim j As Integer
For i = 1 To 12
    cboMonth.AddItem i
Next i
For j = 1994 To 1996
    cboYear.AddItem j
Next j
End Sub

Private Sub Command1_Click()
Dim sql As String
Dim A As String
A = Mid(cboYear.Text, 3, 2) 'get 94 from 1994
sql = "SELECT OrderId, CustomerId, OrderDate FROM Orders WHERE OrderDate LIKE " _
    & Chr(34) & cboMonth.Text & "/*/" & A & Chr(34)
Data1.RecordSource = sql

Data1.Refresh
End Sub

Larisa

[LG]

OK, I've modified code a little:

Private Sub Form_Load()
Dim i As Integer

For i = 12 To 1 Step -1
    cboMonth.AddItem Format(CDate(i & "/01/00"), "MMMM"), n
    cboMonth.ItemData(n) = i
Next i
cboMonth.Text = cboMonth.List(0)

Dim j As Integer
For j = 1994 To 1996
    cboYear.AddItem j
Next j
cboYear.Text = cboYear.List(0)

End Sub
Private Sub Command1_Click()
Dim sql As String
Dim A As String

A = Mid(cboYear.Text, 3, 2) 'get 94 from 1994
sql = "SELECT OrderId, CustomerId, OrderDate FROM Orders WHERE OrderDate LIKE " _
    & Chr(34) & cboMonth.ItemData(cboMonth.ListIndex) & "/*/" & A & Chr(34)
Data1.RecordSource = sql

Data1.Refresh
End Sub

Larisa