ExcelBanter - View Single Post - Excel vba query

Rachel Curran

(Jamie Collins) wrote in message . com...
Rachel Curran wrote ...

unfortunately i keep getting error messages with the syntax, could you
possibly look at my code and let me know whats wrong with it please?

Two points:

1) There should be no white space (e.g. space characters) between
either side of the dot that separates table name and column/field name
e.g.

MyTable.MyColumn

2) My intension was for the IIF function to return a null string,
represented by two single quotes and nothing in between i.e. Chr$(39)
& Chr$(39).

I prefer to alias the table names for brevity (I tend to write SQL
from scratch and it saves a bit of keying); you may be able to come up
with more meaningful names than T1 and T2 or prefer to use the full
table name.

Here's your query re-written with the above in mind (but untested):

SELECT
T2.GCI,

IIF(T2.DATE_OF_BIRTH=T1.DATE_OF_BIRTH,
'', T2.DATE_OF_BIRTH) AS DATE_OF_BIRTH,

IIF(T2.GRADE=T1. GRADE, '',
T2.GRADE) AS GRADE,

IIF(T2.DEPT_DESCR=T1.DEPT_DESCR, '',
T2.DEPT_DESCR) AS DEPT_DESCR,

IIF(T2.BUSINESS_SUB_AREA=T1.BUSINESS_SUB_AREA,
'', T2.BUSINESS_SUB_AREA) AS BUSINESS_SUB_AREA

FROM
6thJuly_Test T1

INNER JOIN

2ndJuly_Test T2

ON T1.GCI=T2.GCI;

could you tell me what the
first line of code actually does and the last four lines please
(curiosity more than anything)

You seem to want me to explain what a SELECT query with a JOIN is. No
offence, but I think that is beyond the scope of a newsgroup post!
It's good to be curious, though. If you have it, take a look in the MS
Access help files or Google it: there's a wealth of info out there.

HTH,
Jamie.

--

Thats a great help, thankyou - only one problem - this brings back all
the gci (primary key) even if no change occurred. So ther are the same
amount of rows in both the tables and query. ive tried "if not null"
as the criteria for the iif statements, but this still didnt work. So
majority of lines hold a gci with nothing populated in all other
columns. Is it possible to eliminate the rows with only the gci on.eg

Result

gci 12345 DOB(blank) NINO (blank) BUSINESS(blank)
gci 45678 DOB(blank) NINO (blank) BUSINESS(blank)
gci 00000 DOB(blank) NINO ab000000Z BUSINESS(blank)
etc

It does however give me all the changes that have occurred, which is
great - i suppose i could always export to excel and filter on non
blanks from that program.

Any ideas please?