Search MIN and MAX

[Jayhson]

Im only newbie in VB.net i have a code here for my search FORM from my datetimepicker i just wanna add MIN and MAX of mileage WHERE VehicleNo = '2173 Hi Ace' from my code below and the min of milage will be put in textbox1 and the max of mileage will be put in textbox2 can anyone help of my problem

[FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000] Me.ExpenditureBindingSource.Filter = (String.Format("Date >= #{0:M/dd/yyyy}# AND Date <= #{1:M/dd/yyyy}#", DateTimePicker1.Value, DateTimePicker2.Value))
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

 

[jmcilhinney]

You've already got code that will filter using a minimum and maximum date so you just follow the same pattern if you want to add minimum and maximum values for other columns too.

 

[jmcilhinney]

Please don't post the same question more than once. If you think that you've posted in the wrong place then ask the moderators to move the thread. Your other thread has been deleted.

 

[Jayhson]

thanks for that how can i do that im only newbie if you dont mind can you give me a code for that

 

[Jayhson]

THis what i did but there is an error

Me.ExpenditureBindingSource.Filter = (String.Format("Date >= #{0:M/dd/yyyy}# AND Date <= #{1:M/dd/yyyy}# Min(Mileage), Max(Mileage) WHERE VehicleNo='2173 Hi Ace'", DateTimePicker1.Value, DateTimePicker2.Value))

 

[jmcilhinney]

I don't think you realise what a filter actually is. That filter will hide all the rows that don't satisfy the specified conditions. Is Min(Mileage) a condition? Please explain, fully and clearly, exactly what it is that you're trying to achieve.

 

[Jayhson]

mileage is column name coming from db that has a value..

 

[jmcilhinney]

When I said "fully and clearly", I actually meant "fully and clearly", not "in as few words as possible so that we have to guess". If you can't explain what you want then I can't help you achieve it. I could make a guess at what I think you want but, if I guess wrong, I have wasted my time. Better that you take the time and make the effort to help those who you would like to take the time and make the effort to help you.

 

[Jayhson]

sory for that this is what i need
1. The data display the result base in the datetimepicker1 and datetimepicker2 and
2. display in textbox1 the maximum value of mileage base in vehicleNo=2173 Hi Ace and
3. textbox 2 the minimum value of milage base in vehicleNo=2137 Hi Ace
This is i want base on my code above

I know the process but i cant do that in my code above

this is the process that Im thinking

QUery = SELECT Min(Mileage), Max(Mileage) FROM test1 LIKE Date Between Date Where VehicleNo='2137 Hi Ace'

 

[jmcilhinney]

You should be using the Compute method of the DataTable. You'll need to call it twice: once for the min and once for the max. Each time, you provide the same filter, i.e. the two dates and the vehicle no. Start by reading the documentation for the method. Not surprisingly, in includes code examples.

 

[Jayhson]

is this like this the one your talking
I have error about error

variable dt is used before it has been assigned a value. A null reference exception could resutl at runtime

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'
Dim tot As Integer = 0.0
　
Dim dt As DataTable
　
Me.ExpenditureBindingSource.Filter = (String.Format("Date >= #{0:M/dd/yyyy}# AND Date <= #{1:M/dd/yyyy}#", DateTimePicker1.Value, DateTimePicker2.Value))
For i As Integer = 0 To ExpenditureDataGridView.RowCount - 1
If ExpenditureDataGridView.Rows(i).Cells(12).Value = "2173 Hi Ace" Then
tot = ExpenditureDataGridView.Rows(i).Cells(11).Value
Me.txtmax.Text = dt.Compute("MAX ( Mileage ) ", "VehicleNo = '2173 Hi Ace'")
　
　
　
End If
　
Next
If tot = 0 Then
MessageBox.Show("No Records Found")
txtmax.Text = "0.00"
End If
　
End Sub

 

[Jayhson]

can you help about this problem

this is the sample screen that i want to solve..
i want to display the maximum and the minimum mileage from 2137 vehicleno where the date selected by the user

 

[jmcilhinney]

You're supposed to call Compute on the DataTable that contains the data, not one that that you just created and is therefore obviously empty. It would be the same DataTable that is bound to your BindingSource.

You are still using the wrong filter anyway. I don't understand how I can keep telling you over and over what to do and you keep doing something else. The DataTable you call Compute on contains ALL the data. It doesn;t matter whether you set the Filter of the BindingSource or not. The DataTable is not affected by that. You need to provide the ENTIRE filter when you call Compute. If you want to get the maximum value for only record that meet multiple conditions then you have to put ALL those conditions in the filter you pass to Compute. If you want to filter on the Date and the VehicleNo then you have to put all those conditions into the filter that you pass to Compute and you have to put AND operators between them. I have said that same thing over and over in many different ways.

 

[Jayhson]

can you give me the code if you dont mind and then i will understand the code.. thats the only thing that my system is missing.. the min and max
tomorrow is my deadline please help me JMCILHINNEY. I know your expert about this me im only a newbie about vb

so please help me...

 

[Jayhson]

:confusion::confusion: :confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion::confusion:
no one help me and give the code! :confusion: