Recent content by wynstonh

absolutely perfect, thanks Minty! BTW "Month" was just shorthand for my actual field name, but thanks for the advice

If I was to do it in excel & my data was in columns A-C my formula in D2 would be: =SUMIFS(C:C,A:A,$A2,B:B,$B2) that help?

Hi, I am trying to replicate a simple sumifs from excel to access & struggling to make it work. The data I have is: Ref------ Month---------- Days 1 ----------May 2018----- 12 1 ----------May 2018----- 10 2 ----------May 2018----- 11 2 ----------June 2018----- 30 3 ----------May 2018------ 13...

yes, definitely that date. I checked for Null values 1st and found none and iif(IsDate( ---- returned TRUE for 31/12/9999 thanks for your advice though, it got me moving in the right direction

it'll last 200 yrs and that's good enough for me

messy hack but this worked for me Months: IIf([Installation End date]>(Now()+100000),"0",DateDiff("m",[Installation Start Date],[Installation End date]+1))

Problem with this is 31/12/9999 is a date according to IsDate function

Nope, 5 queries on 5 different tables.

As more background. I have 5 queries which I am then putting together in a union query to display all the results on one tab. I'm getting a data type mismatch due to the #error results.

It means an infinite end date.

Hi, I am using the DateDiff function and getting #error when the 2nd date is 31/12/9999 (I know this is the problem but that is how my source data is presented to me). What I'd like to do is replace the #error with 0. The formula I have is: Months: DateDiff("m",[Installation Start...

sorted - my references weren't quite right

Hi Minty, Sorry to drag up an old thread but I have tried copying this exact code into another database and I'm getting a "compile error: user-defined type not defined". It's not the 1st time I've used this code but one of the databases I've copied it into was corrupted so I had to revert...

Great!! got it to work with a bit of tinkering around the dates. Thanks so much for your assistance

looked at the data & it's definitely not the date format - the absolute values are what I'd expect them to be but still causing more data returned than I need. I've tried adding another condition to the WHERE statement but it just returns the same data so I'm a bit stuck again. Where...