Access help

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
Hi all

I know I'm on the wrong board, but noone seems to be able to help on the access board so I thought I'd give it a go here!!

I've been working on my macros in excell now for weeks getting data into the right format for access. Whenever I think I've got it sussed I notice another problem in access, so I spend another week changing macros get rid of one problem and hey presto another one pops up!!!

I have linked tables from excel to access and some of these contain mixed data, ie, numeric and text. This has been OK so far, everything is formatted as text and access seemed to be able to read it fine.

Unfortunately I'm now getting a 'numeric field overflow' error when I run a query from a linked table. I've no idea why. I;ve done the help on the microsoft website but nothign works (adding a space before the number/ letter).

Has anyone ever got round this problem??

Thanks anyway I realise its a long shot!

Cath
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
woo hoo!!!

searching on the internet I found one site that mentioned the date could be a problem, had a quick look through and I'd got a date in the wrong format, put this right, and its working!!!

way hey!! (y)
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
'Overflow Error' in Access usually means a problem with data not matching the field type.

I handle lots of text files and nowadays import everything into Access as Text and run a query to update separate number fields. So I may have a field called "AmountText" and another called "Amount". Otherwise Dates can be a special problem with a mixture of formats in the same field. When processing is done by macros the time overhead is relatively small compared with the problem of trying to check thousands of records.
 

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
Hi Brian

Just wondered how you do the query that converts texts to values? Could be useful to know?

Cath
 

MichaelRo

Well-known Member
Joined
Jun 7, 2004
Messages
549
Not sure if its the correct way to do it, But I use the C functions in update queries:

CCur(expression)
CDate(expression)
CLng(expression)
CStr(expression)

Field: Numeric Field
Table: Table 1
Update To:CLng([Table1]![TextField])

Updates a Numeric Field with Data from a Text Field
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,119
Messages
5,857,487
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

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