JazzSP8
Wellknown Member
 Joined
 Sep 30, 2005
 Messages
 1,223
 Office Version

 2013
 2010
 Platform

 Windows
Hey All
Something has me scratching my head and I can't work it out for love nor money, hoping someone here can work their magic.
Within Access I've got a linked Excel table of Products, Breaks and Prices.
I want to Compare the prices against another linked Access table to see if they've changed.
I've got a Calculated field with an IIF to see if they're the same, this works on 6 of 8 fields;
The fields all use a variation of that formula, the PR number changes with the price breaks, that's the only difference.
When fields 3 and 4 are populated I get "#Error " returned, the rest work fine.
I've managed to work out that within the 'Upload' field, Access doesn't seem to be seeing this as a number determined by looking at the Table within Access, these numbers are aligned to the left and not the right.
With that in mind I tried;
Which did solve the problem for that but then caused every other row / value in the field to be an #Error , so not really.
Taking a look at the Excel Spreadsheet in Excel and looking at the values with =ISNUMBER() they are returning as TRUE.
Is there any reason Access would be treating this field the way that is?
Thanks in advance for any help you can provide.
Something has me scratching my head and I can't work it out for love nor money, hoping someone here can work their magic.
Within Access I've got a linked Excel table of Products, Breaks and Prices.
I want to Compare the prices against another linked Access table to see if they've changed.
I've got a Calculated field with an IIF to see if they're the same, this works on 6 of 8 fields;
Code:
PRPR03: IIf([Upload PS1PR3]<>[Galaxy PS1PR3],[Upload PS1PR3],"")
The fields all use a variation of that formula, the PR number changes with the price breaks, that's the only difference.
When fields 3 and 4 are populated I get "#Error " returned, the rest work fine.
I've managed to work out that within the 'Upload' field, Access doesn't seem to be seeing this as a number determined by looking at the Table within Access, these numbers are aligned to the left and not the right.
With that in mind I tried;
Code:
PRPR03: IIf(Val([Upload CP1PR3])<>[Orbit CP1PR3],[Upload CP1PR3],"")
Which did solve the problem for that but then caused every other row / value in the field to be an #Error , so not really.
Taking a look at the Excel Spreadsheet in Excel and looking at the values with =ISNUMBER() they are returning as TRUE.
Is there any reason Access would be treating this field the way that is?
Thanks in advance for any help you can provide.