Sorting Dates

[Michelle Williams]

Hi,

I am wondering if anyone could help me.

I am using a DataGrid to display employee expenses. Each Expense has an "Incurred_Date". A the moment my grid shows all the expenses for a particular employee.

How do I go about showing an employee's expenses just for one month??

I have a dropdown list with the 12 months of the year and was thinking that if i click on a month, it would compare it to the database and then display the necessary data.

If someone could show me some sample code I would really appreciate it.

Thank You!

[vb_dba]

How are you populating the datagrid now?

[Michelle Williams]

Not sure if this is what you mean but this is my code!


strSQL = "SELECT Expenses.EmpID,Expenses.ExpID, Expenses.Date_Incurred, Expenses.RateID, Rates.Description, Expenses.Mileage, Expenses.Total_Cost FROM Expenses INNER JOIN Rates ON Expenses.RateID = Rates.RateID WHERE EmpID = " & empExp & " "
cmdComm.ActiveConnection = conConnect
cmdComm.CommandText = strSQL

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, conConnect

ident = "" & rs!EmpID
If ident Is Nothing Then
MsgBox "There is no such Record! Enter another I.D.", vbInformation, "No Record"
Else
Set dgdExpAd.DataSource = rs 'was rstRec
dgdExpAd.ReBind
'Display the results on the grid
dgdExpAd.Visible = True
End If

[Hack]

Ok. Then unless I'm missing something it would seem that you could get what you wanted but adding an AND to your SQL. Something like

strSQL = "SELECT Expenses.EmpID,Expenses.ExpID, Expenses.Date_Incurred, "
strSQL = strSQL & "Expenses.RateID, Rates.Description, Expenses.Mileage, "
strSQL = strSQL & "Expenses.Total_Cost "
strSQL = strSQL & "FROM Expenses INNER JOIN Rates ON Expenses.RateID = Rates.RateID "
strSQL = strSQL & "WHERE EmpID = " & empExp & " 
strSQL = strSQL & "AND datefield BETWEEN #startdate# AND #enddate# "
cmdComm.ActiveConnection = conConnect
cmdComm.CommandText = strSQL'etc

How you would encapsulate your two dates would depend on the DB field types (i.e., depending on whether your date field is text or date/time)

PS: I broke your sql statement into different lines simply to make it easier to read in this forum thread. That isn't something you necessarily have to do in your code.

[Michelle Williams]

Thanks for your help..

I am nit getting an error but my grid is just not showing any detail! This is my SQL statement.

strSQL = "SELECT Expenses.EmpID,Expenses.ExpID, Expenses.Date_Incurred, Expenses.RateID, Rates.Description, Expenses.Mileage, Expenses.Total_Cost FROM Expenses INNER JOIN Rates ON Expenses.RateID = Rates.RateID WHERE EmpID = " & empExp & ""
strSQL = strSQL & " AND Expenses.Date_Incurred BETWEEN " & DTPicker2.Value & " AND " & DTPicker1.Value & " "

As I am usind date pickers, do i need to change the way the date puts its values into the data base..I am using date/time types

[Hack]

I went back through everything that had been posted and couldn't see where what kind of database you are using was mentioned. How you deal with dates if different between Microsoft product (SQL Server and Access) and Oracle.

I'm going to make the assumption you are using a Microsoft product when I make the suggestion to encapsulate your datepicker values in # signs. See if that works.

[Psyrus]

Try this:

strSQL = strSQL & " AND Expenses.Date_Incurred BETWEEN " & "#" & DTPicker2.Value & "# AND #" & DTPicker1.Value & "#"

[Michelle Williams]

Thank you so much..that worked a charm. Legends!

[Hack]

Cool!

Please edit your first post, and add {RESOLVED} to the original text of the subject line, and then add the green checkmark icon.

By the way: Welcome to the forums.