Excel VBA SQL problem. 1

[kodr]

Okay, I'm stumped. It's probably something simple.

sSQL = "SELECT MAX[INTERVAL_ARRIVALS] AS lMaxIA, [Time] as sTime FROM tblCONG WHERE iIndex = 40 GROUP BY lMaxIA, sTime"
rs2.open sSQL, Conn, adOpenStatic, adLockOptimistic
lMaxIA = rs2.Fields(0).value
sPeriod = rs2.Fields(1).value

The above code errors out at the rs2.open line with "No value given for one or more required parameters."

Anyone have any ideas?

Thanks.

 

[SkipVought]

Hi,

Don't use the aggregated field in your Group By...

sSQL = "SELECT MAX[INTERVAL_ARRIVALS] AS lMaxIA, [Time] as sTime "
sSQL = sSQL & "FROM tblCONG "
sSQL = sSQL & "WHERE iIndex = 40 "
sSQL = sSQL & "GROUP BY sTime"

rs2.open sSQL, Conn, adOpenStatic, adLockOptimistic
lMaxIA = rs2.Fields(0).value
sPeriod = rs2.Fields(1).value

Skip,

_[red][/red]

 

[kodr]

Thanks Skip.