Access help

[Cath]

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

 

[Cath]

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!!

 

[BrianB]

'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]

Hi Brian

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

Cath

 

[MichaelRo]

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