Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Updating a field in MS Access

I'm trying to make a form that will look at 3 fields and put the best value in a 4th.  I have the following

Private Sub Final_score_BeforeUpdate(Cancel As Integer)
max:iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))
End Sub

Which displays the correct value but how do I  store it in the field [Final score]

The [Final score] is used to calculate a place (would also like to automate this but that will be a different question)
0
MikeGinMn
Asked:
MikeGinMn
  • 4
2 Solutions
 
macarrillo1Commented:
Have you tried a direct assignment Or Setting the field to the if statement?

[Final score]=max.value

or you could create a function:

Function FinValue(firstval as  integer, secondval as integer, thirdval as integer)as integer
dim FinVal as integer

if firstval>secondval then
   if firstval > thirdval then
      FinVal=firstval
   else
      FinVal=thirdval
   end if
else
   if Secondval > thirdval then
         FinVal = Secondval
   else
         FinVal=thirdval
   end if
end if
return FinVal
end function

Then you would set the default value of [Final Score]=FinValue([fin First Try], [fin Second Try],[fin ThirdTry])
0
 
dqmqCommented:
To assign the result of that expression to another field, do like this in your procedure.

[Final Score]=iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))


Then call the procedure in the after_update event of each of the three fields.
0
 
MikeGinMnAuthor Commented:
Ok when I try

[Final Score]=iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))

in the after update as a event procedure I get a compile error; Syntax error with it highlighting the [Final Score]=iif([fin First Try]>[Fin Second Try], part of the procedure.

So I tried

If [Fin First Try]>[Fin Second Try] and [Fin First Try]>[Fin Third Try] then [Final score] =[Fin First Try]
If [Fin Second Try]>[Fin Third Try] and [Fin Second Try]>[Fin First Try] then [Final score] =[Fin Second Try]
If [Fin Third Try]>[Fin Second Try] and [Fin Third Try]>[Fin First Try] then [Final score] =[Fin Third Try]

as the after updat event procedure and I get a run time error of 30016  The field 'Final score' is read only
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
MikeGinMnAuthor Commented:
Also I have tried the function - depending on where I put it under events I get a wide array of errors - on google it says to put under Modules which I don't see in Access 2010 - so where would it go
0
 
MikeGinMnAuthor Commented:
Ok I looked at the original query and copied the sql and pasted to a new one and renamed it to original one.  I then used

If [Fin First Try]>[Fin Second Try] and [Fin First Try]>[Fin Third Try] then [Final score] =[Fin First Try]
If [Fin Second Try]>[Fin Third Try] and [Fin Second Try]>[Fin First Try] then [Final score] =[Fin Second Try]
If [Fin Third Try]>[Fin Second Try] and [Fin Third Try]>[Fin First Try] then [Final score] =[Fin Third Try]

under after update for each try and now it works - go figure - so since both suggestions lead me to the right conclusion how do I give each equal points
0
 
MikeGinMnAuthor Commented:
Hope I did that right
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now