Storing a maximum value at runtime

[choward]

Hi,

i am trying to do this in VBA:

DoCmd.RunSQL "UPDATE [Static Data] SET [Static Data].Result = max([Courses].[COURSE ID]) WHERE ((([Static Data].Criteria)='CMAX'))"

it basically stored in a table called STATIC DATA the highest Course ID value. It doesn't work and has this error:

"You tried to execute a query that does not include the specified expression 'Result'...."

Any help is appreciated!

 

[gemma-the-husky]

have one query/expression to obtain the maximum, and store it in a variable

then use this variable in the update expression

so

DoCmd.RunSQL "UPDATE [Static Data] SET [Static Data].Result = "&myvariable

bear in mind that sql has syntax rules regarding datetime/text/numeric variable

 

[mikebaldam]

Dim iNum as Integer

'get highest number in table Courses, by Course ID
iNum =Nz(DMax("[COURSE ID]","[Courses]"),0)

DoCmd.RunSQL "UPDATE [Static Data] SET [Static Data].Result = " & iNum