By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Expression Builder - Access 2003

P: 2
I have a 2003 Access Database with several tables related in a one to many relationship with a parent I am looking to build a report which evaluates if different users of the table have all entered data properly.

As an example - the parent table creates the customer account number, customer last name and customer ssn. A related table creates all other customer information including address, phone etc., another related table contains information researched on the customer

I am new to expressions but have determined if I wish to conpare two fields from 2 separate tables that should contain the same information, (however the field names are different), I think I would use the if-then-else expression below.

I have built a query with the parent and both tables and used the criteria line in the table A field 1 column to place the expression. I have also tried to move the expression to a new column which creates Expr1: - both have not returned the appropriate responses.

I have also tried building the expression into a control on the report itself separate from the query with no success.

IIf([tableA.field1]=[tableB.field2], "field match", "field input error")

(I am using the expression builder - not skilled in code yet) - any suggestions?
Nov 1 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT ParentTable.Field1,
IIf([ParentTable.field1]=[tableA.field2], "field match", "field input error") As TableAMatch, IIf([ParentTable.field1]=[tableB.field2], "field match", "field input error") As TableBMatch
FROM ((ParentTable LEFT JOIN TableA
ON ParentTable.Field1=TableA.Field2)
LEFT JOIN TableB ON ParentTable.Field1=TableB.Field2)


Try something like this ....
Nov 1 '06 #2

P: 2
SELECT ParentTable.Field1,
IIf([ParentTable.field1]=[tableA.field2], "field match", "field input error") As TableAMatch, IIf([ParentTable.field1]=[tableB.field2], "field match", "field input error") As TableBMatch
FROM ((ParentTable LEFT JOIN TableA
ON ParentTable.Field1=TableA.Field2)
LEFT JOIN TableB ON ParentTable.Field1=TableB.Field2)


Try something like this ....
Thanks - where would I attach this statement - to a text box control in a report or to a new column in a query or to a criteria line in a column - trying to stay within boundaries of my knowledge of access expression builder feature/wizard

again - thanks
Nov 2 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks - where would I attach this statement - to a text box control in a report or to a new column in a query or to a criteria line in a column - trying to stay within boundaries of my knowledge of access expression builder feature/wizard

again - thanks
I meant for you to use this as the Record Source for a new report which is what you said you wanted.
Nov 2 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.