Format Issues with Linked Tables

Smitty

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
I have a Sales Rep table I'm linking in Access from Excel. In Excel there are a few sales ID fields that are text formatted (Sales Rep #, Territory #), but when linked, Access sees the Territory # as number, not text. I don't have the same problem with the Sales Rep #, both of which are 5 digits.

This creates a type mismatch error in queries as the related data in my Access table is text.

Any idea on how I can retain the Excel formatting? I've run out of ideas, including forcing text format with Text to Columns.

Unfortunately I've tried to reformat the Access data a Number, but run out of memory on 300K records.

I could try to rebuild the Access master table but it's 2 years worth of individual weekly data files from our Data Warehouse.

Thanks,
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Hi Smitty, here's 2 ideas.

1. Try creating a dummy record in row 2 of the Excel workbook, and use text where you want text formatting. Nasty, I know, but Access looks at the first data row and determines the data type from that. If you're careful with your ID or date value, you can easily exclude the item from any analysis.

2. Another option on the reformatting -- you should be able to achieve this by creating a temp table with the correct data types, and then appending the existing tables to it. Use CLng or CDbl to coerce the values during the Append.
Or maybe, create a new Number field in the resident Access table and run an Update query to populate it with the number version of your Territory #. Again, CLng should do it.

Denis
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Modifying templates and linking to new tables
2
Replies
13
Views
771
gshock
G
Anigito
  • Solved
MS Access Union Query Changes Numeric values to Text values
2
Replies
11
Views
954
Anigito
Anigito
K
  • Solved
Append Query with Criteria from Linked Excel Tables
Replies
7
Views
894
Micron
M
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
3K
roelandwatteeuw
R
S
  • Question
Access DB Importing excel file over SharePoint List table in Access Issue
Replies
2
Views
630
Joe4
Joe4

Forum statistics

Threads
1,216,116
Messages
6,128,934
Members
449,480
Latest member
yesitisasport

Share this page

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
Back
Top