Subscript out of range error

[StarhawkGamer]

Alright, I've got two problems that I'm facing with my Access database.

The first is that it won't let me copy from Excel into Access via the clipboard (damaged clipboard error). I can work around this error via the data import system, but that has lead to a second issue.

I've got some tables that give me "subscript out of range" errors when I attempt to import them. I am currently using 5 spreadsheets, each one formatted for a specific table. When I created the tables, before problem #1 arose, I put the data into these same spreadsheets and then copied and pasted from to form the original tables.

The database is a little over 100 megabytes in size, storing data for the past 13 months.

I can have the data output into a new table and then copy it into the original table without a problem, but that is a work-around for a work-around, not exactly an efficient use of time.

 

[dhookom]

If you have the data output to a new table, how would the fields and data types match up?

Duane
Hook'D on Access
MS Access MVP

 

[StarhawkGamer]

That explains part of it....I'm converting the raw data from text to numbers so that I can average it in various reports. I convert "Yes" to 1, "No" to 2, and anything else, such as "DK" and "N/A" to a null space. There are 4 columns that "DK", "N/A", and null spaces are found in the original data. Those columns are being put as text when I put use the data import wizard.

 

[dhookom]

What do you meant by "explains part of it"? What is the part that isn't explained?

Duane
Hook'D on Access
MS Access MVP

 

[StarhawkGamer]

yesterday, 2 tables were doing it, one that was a straight data pull and the other which went through the data converter first. Now, only the later one is doing it. Sorry about the confusion there. What can be done to correct those 4 columns so that they come through as number, even with the empty records?

 

[dhookom]

You can insert a Null into a numeric field (if your table design allows nulls). Your previous statement "null spaces are found in the original data" can't be. Null is never spaces. Null isn't even a zero-length-string.

I don't know what your values are. If they are "" or " " and you want to insert them into a numeric field, you have to convert them to null.

Duane
Hook'D on Access
MS Access MVP

 

[StarhawkGamer]

Alright, my conversion is equation is =if(a2="Yes",1,if(a2="No",0,"")....how would I convert it to a null in the event of a D/K, N/A, or null in A2?

 

[dhookom]

I'm not sure why didn't just copy your equation unless it's from outside Access. If this is used in Access, it won't work unless you have created your own if() function and there is a missing ")". The Access function is IIf(). Try:

=IIf([a2] & "" = "Yes",1,IIf([a2] & "" = "No", 0, Null))

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

SQL code:
SELECT IIf([a2 field]="Yes",1,IIf([a2 field]="No",0,Null)) AS NewA2
FROM yourRawTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[StarhawkGamer]

The raw data is in excel and I'm using an excel-based converter to prepare the data to go into Access. I don't have access to the original SQL-based database to pull this data via SQL.

 

[dhookom]

StarhawkGamer,
None of what you just posted tells us much about your success or failure with our suggestions. PH and I provided basically the same solution and we don't know if you even tried them or your results.

Duane
Hook'D on Access
MS Access MVP