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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,089
Members
412,310
Latest member
mark884
Top