Hi all

I've got this little problem and i'm sure there is a simple answer.

I have a table with 2 text fields i need to compare and add a third (probably in a query) which states whether they match or not:

Field1 Field2 Result
aa aa matched
aa xx unmatched
aa aa matched

How do i go about adding the result to my query? I cant dump into excel as I'm typically dealing with 500000+ lines.

Any help appreciated greatly.

switch function

Syntax

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

SELECT mytable.field1, mytable.field2, IIf([field1]=[field2],"Match","NoMatch") AS result
FROM mytable;

Thanks to both of you. I didn't really understand the switch function but I found the Iif one from reasearching it and it does exactly what I need. I didn't realise there were so many functions available in Access. I'm an Excel person facing a harsh introduction to Access. Getting there…

I use the switch function to group items usually. For example, I group casework files together by the amount of days since last action:

Switch(DateDiff('d',[comment_date],Now())-DateDiff('ww',[comment_date],Now())*2<=5,"00 to 05",…

This calculates the workdays between the comment date and today and assigns it a string of "0 to 5" if it's in that region.

I also use it to group dates into months i.e. 01/02/08 to 2008 February…

HTH