Using dmax on one column and then placing action on respectivly record next to it

[huk250]

A good afternoon,

I have a table CDNUM with the following columns

ID Random
1 50
4 60
6 30
3 20
5 20
2 10

I want in a form in Visual Basic a event procedure so that he looks in the column of ID for the highest value and then in the Column Random (in the corresponding record) add 5
when I do this



[Random] = DMAX ("RANDOM", "CDNUM") + 5

then it will look in the second column for the highest part and add 5 : so here it will be 65

I want that the highest part in column ID and then that it gives me 35
kindest regards

[Welshgasman]

In my world 6 is higher than 4, so I would expect 35 ?

So your ctriteria would be a DMax() of ID not of Random?

[huk250]

this is correct this was an example
I want the dmax of column ID and count up the Random Column +5

[huk250]

I want to look up the Dmax in column of the ID but I don't want the ID number, I want the corresponding record in the column of Random so that I can add 5

[Welshgasman]

So as I mentioned use Dmax() for the ID as criteria for the Dlookup() for Random.

BTW 'I would like' is much more polite than 'I want'

[orange]

Try this SQL in your event

select random +5  as newRandom from cdnum
where id =(select top 1 dmax("id","CDNUM") from cdnum )

newRandom
35

[huk250]

A good afternoon, I made the following line in Visual Basic but this doesn't give me the correct result

[RANDOM] = DLookup("RANDOM", "CDNUM", DMax("ID", "CDNUM")) + 5

Can you help me to resolve my problem
Kindest regards

[Welshgasman]

You need to say what field is equal to the criteria supplied?
How else is Access meant to guess?

If you do not know the syntax then Google it.