numberic error - repost

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a table that is linked to microsoft excel. The table is made up as
follows.

ProductCode Description Date Booked

78030006 Shoes 11/8
3950883252 Cufflinks 29/9

This table has lots of different entries and the product code could range
anything from 8-12 digits.

I have written 2 queries one with a parameter that is based on the
description which is fine and dandy.

The query i have written for the product code is where i am experiencing all
the problems. All that i want to do is enter a product code and its brings
up the description and the date.

SELECT mdasheets.F1, mdasheets.F2, mdasheets.F3
FROM mdasheets
WHERE (((mdasheets.F1) Like "*" & [ Product Code] & "*"));

It seems to work fine for the first time and then i have a numeric field
overflow. The information that i would like to bring up then serves no
further purpose as we just need to know a date for this product code and then
we close the query.

I have no idea how to get around this, can anyone help please?

Thanks in advance

Carol
 
The problem is that when you link to an Excel spreadsheet, Access inspect
the first n(?) rows and then decide the data type of the columns.

Let's say Access decides that the ProductCode is of type Long (most likely).
When JET retrieve the Cufflinks, the ProductCode *number* is too big to
store in Long storage space and you get an overflow.

Since you don't do calculations with the ProductCode, I would suggest
convert them to Text rather than numeric. (in Excel, use the spreadsheet fn
Text())
 
Hi

Thank you so much for your help. When you say "(in Excel, use the
spreadsheet fn
Text())". Could you tell me how exactly i go about this.
Click to expand...

I only know how to use the main front screens in exel and i presume that i
need to insert this as coding somewhere.

Thanks again for your help

Carol




Van T. Dinh said:
The problem is that when you link to an Excel spreadsheet, Access inspect
the first n(?) rows and then decide the data type of the columns.

Let's say Access decides that the ProductCode is of type Long (most likely).
When JET retrieve the Cufflinks, the ProductCode *number* is too big to
store in Long storage space and you get an overflow.

Since you don't do calculations with the ProductCode, I would suggest
convert them to Text rather than numeric. (in Excel, use the spreadsheet fn
Text())

--
HTH
Van T. Dinh
MVP (Access)


CarolM said:
Hi

I have a table that is linked to microsoft excel. The table is made up as
follows.

ProductCode Description Date Booked

78030006 Shoes 11/8
3950883252 Cufflinks 29/9

This table has lots of different entries and the product code could range
anything from 8-12 digits.

I have written 2 queries one with a parameter that is based on the
description which is fine and dandy.

The query i have written for the product code is where i am experiencing all
the problems. All that i want to do is enter a product code and its brings
up the description and the date.

SELECT mdasheets.F1, mdasheets.F2, mdasheets.F3
FROM mdasheets
WHERE (((mdasheets.F1) Like "*" & [ Product Code] & "*"));

It seems to work fine for the first time and then i have a numeric field
overflow. The information that i would like to bring up then serves no
further purpose as we just need to know a date for this product code and then
we close the query.

I have no idea how to get around this, can anyone help please?

Thanks in advance

Carol
Click to expand...
Click to expand...
 
It is a *spreadsheet* function so you can use it on the spreadsheet.

Let's assume that Column A is the ProductCode Column, the first row is the
heading row and the data in row 2 to 100. Add an empty Column B. Highlight
from B2 to B100 with B2 as the main cell of the selection. Type in:

= TEXT(A2, "General")

(including the equal sign)

Hold the Control Key down and hit Enter. The same formula will be used
(with relative cell reference) for B2 to B100 and these will be Text values
containing digits.

Save and link this Excel file to your database. When you use the linked
Table, use the Field corresponding to your Column B and ignore Column A.
 
Back
Top