How to write an expression referring to two values from the same field

[sf827]

I'm a long-time excel user in the process of converting to access.

What's the simplest way write an expression which refers to specific values from the same field?

If I was using excel I'd use cell references. E.g.

=IF(B3>B2,"Default","Valid")

Thanks

[pbaldy]

Depends on where, but in general the equivalent would be:

=IIf([Field1]>[Field2],"Default","Valid")

Note the IIf() function rather than IF, which is only valid in VBA code.

[sf827]

Thanks for the (super) quick reply pbaldy.

Forgive me if this is a stupid question but wouldn't your expression compare two different fields?

I probably wasn't clear - I'm trying to compare two values from the same field.

For example, if we imagine a table in datasheet view, I'd like to write an IIf expression which returns "Default" if the value in row 3 of [Field 1] is greater than the value in row 2 of [Field 1].

I suppose another way of asking the question is - is it possible to identify values by describing their relationship to the "row" containing the expression?

[pbaldy]

Sorry, you did say that but I was focused on the B in the formula, thinking that meant same record. The order of records in a table has no meaning, so you wouldn't refer to it as a relationship to the current row. Typically you'd do something like this:

http://www.allenbrowne.com/subquery-...#AnotherRecord

Does that seem like it would work?

[sf827]

Yes that should give me a starting point.

I see your point about the order of values being meaningless.

Thankyou!

[pbaldy]

No problem, and welcome to the site! Sorry about heading down the wrong track.

[JustAccess]

I am trying to do something similar but I am so new to ACCESS that the link you provided earlier is still greek to me! I have combined 2 fields to create a unique (should be unique) field identifier for each row. I now need to determine if I have any duplicates listed in the newly created field (CLIN Dup).

example:

Rel # CLIN CLIN Dup
10DL 0001 10DL0001
10DL 0002 10DL0002
10DL 0003 10DL0003
10DL 0002 10DL0002

Any help would be appreciated as I am stumped

[pbaldy]

Try this query:

SELECT [CLIN Dup]
FROM TableName
GROUP BY [CLIN Dup]
HAVING Count(*) > 1