0

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.

4
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by naveedanjum
0

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

0

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 ...

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.