Query to update table fails

I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

What I need after the update:
CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner


I posted this request a few days ago and it was suggested I try the following:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That produced a “Type Conversion Error so I changed it to:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That code updated the fields but what I now get is

CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 1 duet 5 Novice
R6 30 1 duet 6 Beginner
R7 30 1 strut 7 Novice
R8 30 1 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

"Steve S" wrote:
I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

What I need after the update:
CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner


I posted this request a few days ago and it was suggested I try the
following:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That produced a "Type Conversion Error so I changed it to:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That code updated the fields but what I now get is

CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 1 duet 5 Novice
R6 30 1 duet 6 Beginner
R7 30 1 strut 7 Novice
R8 30 1 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner



Sure looks like you wanted

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees",
"Event = '" & F.Event & "' and [CID] = 30")
WHERE F.[CID]=30;

Event is text so need to wrap value in single quotes,
you aliased table Fee as F so must reuse "F", not "Fee",
and don't want quotes around F.Event
because this is referencing the value of
the current record you are looking at
and comes from "outside" the domain function.

I would try the following.


UPDATE Fees as F
SET F.EsortKey = DMin("LSortKey","Fees","CID=" & F.CID " AND Event='" &
F.Event & "'" )
WHERE F.CID = 30


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Steve S wrote:
I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

What I need after the update:
CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner


I posted this request a few days ago and it was suggested I try the following:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That produced a “Type Conversion Error so I changed it to:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That code updated the fields but what I now get is

CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 1 duet 5 Novice
R6 30 1 duet 6 Beginner
R7 30 1 strut 7 Novice
R8 30 1 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

Thanks much guys. Gary, what you sent was exactly what I got a few days ago
that generated an error message. I think I mistyped the sequence of the
single-double quote marks then. This time I cut/pasted your string and it
worked great.

It looks like the correct sequence is ' " string " '. Is that true.

John I didn't test your responce but thanks.

steve

"Steve S" wrote:

I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Before the update:
CID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

What I need after the update:
CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner


I posted this request a few days ago and it was suggested I try the following:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That produced a “Type Conversion Error so I changed it to:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That code updated the fields but what I now get is

CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 1 duet 5 Novice
R6 30 1 duet 6 Beginner
R7 30 1 strut 7 Novice
R8 30 1 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

"Steve S" wrote:
snip

It looks like the correct sequence is ' " string " '. Is that true.

snip

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees",
"Event = '" & F.Event & "' and [CID] = 30")
WHERE F.[CID]=30;

Event is text so need to wrap value in single quotes,
you aliased table Fee as F so must reuse "F", not "Fee",
and don't want quotes around F.Event
because this is referencing the value of
the current record you are looking at
and comes from "outside" the domain function.

That is correct. Maybe this will help...

The "where clause" of the domain function must be
wrapped in double quotes. That means that any further
quotes within the clause should be single quotes (or
2 double quotes).

If the clause was not "correlating" back to the main
query to get an Event, say for some reason the Event
needed to only be "solo," the clause would be
properly constructed like:

"Event = 'solo' AND [CID] = 30"

or

"Event = ""solo"" AND [CID] = 30"

This way the parser knows the clause starts
with "Event... and ends with ...30", i.e., the
double quotes tell it where the clause starts and ends.

To introduce a specific string within the clause, we
wrapped that string in single quotes instead of
double quotes so we don't confuse the parser.

In our case we want to *correlate* back to the main
query to get the Event from the record we are dealing
with at the time (that is one reason why domain functions
can cause queries to be so slow...our function is evaluated
for every record).

So, in our clause, instead of "solo" we want the parser
to "stop a second," go get the current value for Event,
and put it where "solo" used to be, then continue.

"Event = ' stop,what is current F.Event? ' and [CID] = 30")

We temporarily "stop the clause" with a double quote.

"Event = '"

We wrap F.Event in ampersands to include the current
value of F.Event in the clause.

"Event = '" & F.Event &

We then "continue the clause" with a double quote.

"Event = '" & F.Event & "' and [CID] = 30")

Note, if CID and Event had been swapped in the clause...

"[CID] = 30 AND Event = '" & F.Event & "'")

for sake of clarity (but you must use clause above
for the query to work):

"[CID] = 30 AND Event = ' " & F.Event & " ' ")