Access seeing a number as text in a field, problems with calculation because of it.

[JazzSP8]

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;

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;

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.

 

[Joe4]

If you want to return a number, why do you have the FALSE argument returning "", which is a string?
Try this instead:

RPR03: IIf(Val([Upload CP1PR3])<>[Orbit CP1PR3],[Upload CP1PR3],0)

 

[JazzSP8]

Hey Joe4 - Thanks for the reply.

I only want to return a number in that field if the values between the other two are different, otherwise I'd like it blank, a zero would also work however.

I gave your Formula a go, and it did what the original VAL variation I tried did, it worked for the row that had the original error, but all the other rows then errored out.

It's that the field is being seen as a Text file that is the issue, I've been Googling and found that I could look at the Spreadsheet in Design View in Access (I'm relatively new to Access so don't know lots of things).

When I look at it in this view there are 4 fields that are being seen as "Short Text" when they're numbers.

I've tried reformatting the spreadsheet columns as "Number" instead of "General" and reimporting the it in as a linked table but they remained as Short Text.

 

[Joe4]

Unfortunately, you really have no control over the Data Type of linked files.
I think you need to convert each field with VAL, i.e.

RPR03: IIf(Val([Upload CP1PR3])<>Val([Orbit CP1PR3]),Val([Upload CP1PR3]),0)

 

[JazzSP8]

Ahhh - No, that didn't work either - Again all the other rows errored but the original error has gone :-/

Never mind - If it's something to do with the data type I'll have to think my way around it.

Thanks for your time

 

[Joe4]

Note that if there are any text entries or errors in your data, that won't work because it cannot convert them to a number.
Typically, if you see a Data Type coming across as Text when you expect it to be a Number, it is usually because there is something in your data that makes it think it cannot be a number (typically, a text entry or error).

 

[JazzSP8]

Thanks for that Joe4 - But still no luck.

I opened Excel and used =ISBLANK, =ISNUMBER, =ISTEXT to check the entries and there was nothing untoward.

Everything that looked blank was, everything that looked like a number was and asides from the Column header nothing was text (this is the same for the other columns so I don't imagine it's an issue).

I even opened the original CSV file some of the information came from to make sure there wasn't a random " in there.

I've no doubt there'll be *something* telling Access that it's a text field but I think I'm going to be better off working my way round it than looking for it at this point.

A curiosity for another day

 

[Joe4]

Note that if you have blank/empty values in numeric fields (instead of zeroes), I could see how Access might set that field to text instead of number.
Because of issues like these, I often do not linkl or import Excel files directly. You can spend hours trying to find the cause of the issue and get nowhere.
I typically export my files to a text file (CSV or tab-delimited), and then import those into Access, where you then have the ability to tell it the Data Type of each field instead of letting Access try to to decide.