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

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. 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;

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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,037
Office Version
  1. 365
Platform
  1. Windows
If you want to return a number, why do you have the FALSE argument returning "", which is a string?
Try this instead:
Code:
RPR03: IIf(Val([Upload CP1PR3])<>[Orbit CP1PR3],[Upload CP1PR3],0)
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,037
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
RPR03: IIf(Val([Upload CP1PR3])<>Val([Orbit CP1PR3]),Val([Upload CP1PR3]),0)
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,037
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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 :)
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,037
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,383
Messages
5,641,835
Members
417,240
Latest member
pjohnsonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top