I cannot get DLookup or DMax to work in any shape or form - I must have
some problem with my Access set-up.
This test does not work.
I have a table (called 'Test') with two fields, 'ID' and 'Score'.
On the first field of a new query I entered - Expr1:
DMax("[Score]","[Test]"), I got the error:
Compile error, in query expression 'DMax("[Score]","[Test]")'
Next I removed the square brackets - Expr1: DMax("Score","Test"), I got
the error
Compile error, in query expression 'DMax("Score","Test")'
Do I need some add-in installed to get DLookup and DMax to work???????
Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?
--
Duane Hookom
MS Access MVP
--
No good......
UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID);
Gives the error:
Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.
I apprecaite you said try something like this, but there is some
syntax problem that I cannot solve.
BTW ClubID is a number.
Try something like this. I would try it on copies of your data before
using it on production data.
UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--
Table 1
ClubID
HighScore
Query 2
ClubID
MaxScore
You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.
--
Duane Hookom
MS Access MVP
--
I have two tables, one contains the highest score each team has
ever achieved - this has been manually built loaded from the
leagues historical records.
I written a query that produces, for each team, it's highest score
of the current season.
I am to know how I would go about writing an update query that
would update my first table where the value in the second table is
greater than that in the first.
