Urgent

[Shailja]

I have written a query sql = "SELECT Max(No) FROM Temp" in recordset using
rs.open sql,cn,,,adcmdT ext. How to fetch the result of a recordet. I get wrong answer when i execute following code:

Dim i as Long

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

Answer i get is 0 but it should come 4.

Plz tell me correct code

[vijaydiwakar]

I have written a query sql = "SELECT Max(No) FROM Temp" in recordset using
rs.open sql,cn,,,adcmdT ext. How to fetch the result of a recordet. I get wrong answer when i execute following code:

Dim i as Long

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

Answer i get is 0 but it should come 4.

Plz tell me correct code

Hi, Shailaja
Try this
Select nvl(max(no),0) from temp -> if ur using oracle
try to use adcmdDynamic

[Shailja]

Hi, Shailaja
Try this
Select nvl(max(no),0) from temp -> if ur using oracle
try to use adcmdDynamic

I
I am not using ORACLE, My Back End is Access.

[hariharanmca]

I
I am not using ORACLE, My Back End is Access.

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

for access qry is correct,... Ok just try this

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs.field(0)
or
i = rs.field(0).value
MsgBox(i)

[vijaydiwakar]

I
I am not using ORACLE, My Back End is Access.

Would u pls give me the tbl structure along with some data and the version of msaccess

[Shailja]

Would u pls give me the tbl structure along with some data and the version of msaccess

Table: Temp

No Name
1 A
2 B
3 C

[Shailja]

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

for access qry is correct,... Ok just try this

sql = ""SELECT Max(No) FROM Temp""
rs.open sql, cn,,,adcmdText
i = rs.field(0)
or
i = rs.field(0).val ue
MsgBox(i)

I have tried both the above code , u have given. But still getting 0 answer.

[vijaydiwakar]

Table: Temp

No Name
1 A
2 B
3 C

Shailaja there are so many problems in msaccess, ur facing one of them right now
the sol is just change the column name from no to myno or what evr u like
and run the qry
alsom change the name field name if u will use date then donot use ddate or date as column name
by

[Killer42]

Personally, I think the problem might be the extra quotes around the SQL. To quote your original code...

Dim i as Long

sql = "[U]"[/U]SELECT Max(No) FROM Temp[U]"[/U]"
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

What about trying this...

Dim i as Long

sql = "SELECT Max(No) FROM Temp;"
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

[vijaydiwakar]

Personally, I think the problem might be the extra quotes around the SQL. To quote your original code...

Dim i as Long

sql = "[U]"[/U]SELECT Max(No) FROM Temp[U]"[/U]"
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

What about trying this...

Dim i as Long

sql = "SELECT Max(No) FROM Temp;"
rs.open sql, cn,,,adcmdText
i = rs(0)
MsgBox(i)

No Boss the problem will be get solved by the suggetion i'v given just try it out
bye

[Killer42]

No Boss the problem will be get solved by the suggetion i'v given just try it out

No, pick me! :D

Seriously, though. Are you thinking fieldname "No" is being interpreted in the context of Yes/No? Have to watch those reserved words, huh.

[hariharanmca]

No, pick me! :D

Seriously, though. Are you thinking fieldname "No" is being interpreted in the context of Yes/No? Have to watch those reserved words, huh.

Yes in MS Access 'No' is reserved word, just we have to try some other

[vijaydiwakar]

No, pick me! :D

Seriously, though. Are you thinking fieldname "No" is being interpreted in the context of Yes/No? Have to watch those reserved words, huh.

Just do the thing i sugested and u will get the ans itself :D

[willakawill]

To avoid problems with reserved words in sql it is always good practise to write your statements out in full including square brackets, no shortcuts and no fingers crossed :)

Dim i As Long
Dim sql As String

sql = "SELECT Max([Temp].[No]) AS MaxNo FROM [Temp]"
rs.open sql, cn,,,adcmdText
i = rs("MaxNo")
MsgBox("The max no is: " & i, "Maximum Number")