Update Using Dlookup

[Cap Heresy]

UPDATE Matrix SET Margin =
DLOOKUP ("Margin", "Segments", "Team="+Matrix.Team AND "Opponent="+Matrix.Opponent AND "Segment="+Matrix.Segment)


;

It correctly said it was going to update 15,500 records, but I got a "type conversion error" on all records. The field Margin is defined as decimal in both tables. Any ideas?

[pbaldy]

The quotes and concatenation is off. See if this helps:

DLookup Usage Samples

[June7]

Are Team, Opponent, and Segment text fields? If so need text delimiters. The AND operators need to be within quotes. Better to use & for concatenation.

"Team='" & [Team] & "' AND Opponent='" & [Opponent] & "' AND Segment='" & [Segment] & "'"

[Cap Heresy]

Segment is numeric. So I tried this, without single quotes for Segment, and it got past the error. Thanks. I'm used to only having to use quotes for literal values.

UPDATE Matrix SET Margin =
DLOOKUP ("Margin", "Segments", "Team='" & [Team] & "' AND Opponent='" & [Opponent] & "' AND Segment=" & [Segment] & "")
;

[pbaldy]

FYI, the bit in red isn't necessary:

DLOOKUP ("Margin", "Segments", "Team='" & [Team] & "' AND Opponent='" & [Opponent] & "' AND Segment=" & [Segment] & "")

Is this query slow? I'd expect a join to run faster.

[Cap Heresy]

It's only a few seconds (39,000 records and indexed). I abandoned a join because there is some issue with updating that way. If interested, see

http://stackoverflow.com/questions/5...dateable-query

[June7]

Whether or not the text is literal or held by variable, still must be delimited. Same for dates only use # delimiter.