Max query help

[usman6062]

Hello guys,
I am having a weird problem. I cant understand and i have spent almost 2 hours in finding the problem.
Well,Actually I have a table of id,date,comment.
id and date are composite primary key. so id always starts with 1 everyday.
now i want to find the max id where date = particular.

I am writing this Sql Query for this

"Select MAX(Acc_id) from AccDtl where Acc_Date='" & Me.DTPCollDate.Text & "' and CabRego='" & Me.cmbcabs.Text & "'"

Now this query gives error that index out of range exception. Even when there is 1 record and its ID is 1. it should return 1.

when i use same query without max. like
"Select Acc_id from AccDtl where Acc_Date='" & Me.DTPCollDate.Text & "' and CabRego='" & Me.cmbcabs.Text & "'"
it returns data reader as true until data ends and then it returns false.

where i am wrong and what i can do to correct my mistake.

I thank you in anticipation.

[tesuji]

hi

what will happen if you omit "and CabRego='" & Me.cmbcabs.Text & "'" ?

what should be the meaning of & ?

Can you give all columns and their datatypes of table AccDtl?

-- tesu

[hielo]

When you execute Select Acc_id FROM ... then your RESULT set should have a "field" named Acc_id. So: rs.Fields("Acc_id") should give you the value of Acc_id in the db table.

However, if you are executing Select Max(Acc_id) FROM ... then your RESULT will NOT have a field name "Acc_id". So this will give you an error: rs.Fields("Acc_id") What you need to do is give your Max() an "alias": Select Max(Acc_id) as Acc_id FROM ...

[naveedanjum]

Kindly try this
If CabRego is numeric

"Select MAX(Acc_id) as MAX_ID from AccDtl where Acc_Date='" & Me.DTPCollDate.Text & "' and CabRego =" & Me.cmbcabs.Text

Else

"Select MAX(Acc_id) as MAX_ID from AccDtl where Acc_Date='" & Me.DTPCollDate.Text & "' and CabRego =" & Me.cmbcabs.Text & "'"

now get MAX_ID column.

Hopefully this will help

With Regards
Naveed