Right, i've pretty much never ever used Access. I've used MYSQL a ton, and know my shizzle in Excel, however I'm having much difficulty translating what I know into how Access wants me to do things.
I'm trying to get a VERY BASIC query to run, and it's having none of it.
Basically I have got a linked Excel table, with a ton of info regarding sickness in it.
Within this table are a couple of columns that I'm interested in:
Absence End Date
Absence Start Date
Now all I want to do is have Access process the table I've linked, and give me a number of days between Start and End dates. Simples. Managed that no problem.
HOWEVER, here's where my problem comes in.
The Excel report that gets kicked out from our system has some of the Absence End Date's listed as "NULL", not empty cells, it actually writes NULL in there.
So you'll have like a list going:
Absence End Date
20/04/2011
03/04/2011
15/04/2011
NULL
06/04/2011
NULL
etc etc
This obviously kills the 'number of days' calculation when it hits a "NULL".
Ha ha! I think, easy fix, I'll set up a 'FixedEndDate' function to replace anything with the word NULL to a certain date (happens to be 30/04/2011).
So off I go and come up with:
FixedEndDate: IIf([Absence Record]![Absence End Date]="NULL",#30/04/2011#)
Which as far as I can tell, should look in the Absence Record table, go down the Absence End Date column, and every row that reads back "NULL" should be replaced with 30/04/2011, with the rest being left as they are.
The query saves fine, but when I run it all I get is Error! all the way down 
Now I'm obviously making some glaringly obvious Access n00b mistake, and I'd be terribly grateful if someone could point me in the right direction, as i've been pulling my hair out for about an hour now
I'm trying to get a VERY BASIC query to run, and it's having none of it.
Basically I have got a linked Excel table, with a ton of info regarding sickness in it.
Within this table are a couple of columns that I'm interested in:
Absence End Date
Absence Start Date
Now all I want to do is have Access process the table I've linked, and give me a number of days between Start and End dates. Simples. Managed that no problem.
HOWEVER, here's where my problem comes in.
The Excel report that gets kicked out from our system has some of the Absence End Date's listed as "NULL", not empty cells, it actually writes NULL in there.
So you'll have like a list going:
Absence End Date
20/04/2011
03/04/2011
15/04/2011
NULL
06/04/2011
NULL
etc etc
This obviously kills the 'number of days' calculation when it hits a "NULL".
Ha ha! I think, easy fix, I'll set up a 'FixedEndDate' function to replace anything with the word NULL to a certain date (happens to be 30/04/2011).
So off I go and come up with:
FixedEndDate: IIf([Absence Record]![Absence End Date]="NULL",#30/04/2011#)
Which as far as I can tell, should look in the Absence Record table, go down the Absence End Date column, and every row that reads back "NULL" should be replaced with 30/04/2011, with the rest being left as they are.
The query saves fine, but when I run it all I get is Error! all the way down
Now I'm obviously making some glaringly obvious Access n00b mistake, and I'd be terribly grateful if someone could point me in the right direction, as i've been pulling my hair out for about an hour now