exporting from access to excel

[jbknight]

When I export from access to excel I end up with numbers as text, that can easily be converted to a number by highlighting and selecting convert to number. Is there an a way to get the numbers to come over as numeric without having to highlight and convert? This is probably a very easy question, I just can't seem to figure it out.

Thanks,
jbknight

 

[Corticus]

Say your Access db has a table in it, with a numeric field. You're trying to export that table.

Instead of exporting the table itself, create a query with all the fields you need in it, and then put the numeric field inside the int() function, and then export this query. That should force Excel to pick it up a number.

Hope that helps,

 

[jbknight]

Thanks, Corticus that helps. I have created the query and now use it through a macro that exports it out, but I can't figure out where to put the int() function within the query. Thanks again for your help. jbknight

 

[Corticus]

Using 'MyField' to represent the field you're trying to fix.

Just add a field to the query, like so:
MyFieldFixed: Int(MyField])

You now have a new field, arbitrarily named 'MyField Fixed' that displays the value Int([MyField]).

This should force every record from this field to an integer or null value.

-Cort

 

[jbknight]

I think I am really close.

Here's what it looks like:
RFSTotal-Monday:Select Query
Field:--Last_Name--First_Name--RFS_Number--MON-----aRFS_Number: Int([RFS_Number])
Table:--RFS Detail--RFS Detail---RFS Detail----RFS Detail--Blank
Total:--Group by----Group By----Group By------Sum------Group By

I keep getting a data type mismatch error or a circular error if I take the 'a' out of aRFS_Number. I am trying to get the RFS_Number field to export out as an interger instead of text. I really appreciate your help. Thanks, jbknight

 

[Corticus]

Try this for your query:

SELECT [RFS Detail].LastName, [RFS Detail].FirstName, Sum([RFS Detail].MON) AS SumOfMON, Int([rfs_number]) AS aRFS_Number
FROM [RFS Detail]
GROUP BY [RFS Detail].LastName, [RFS Detail].FirstName, Int([rfs_number]);

Enter this as SQL, it one of the view choices (datasheet/design grid/SQL) for your query.

What type of data is this field 'rfs_number'?

I'm not sure why your getting the data mismatch, but it usually indicates a problem with criteria, or a join, such as trying to join a text field on a number field, or setting the criteria of a text field in a query to a numeric value.

I tested the above SQL with rfs_number as both a number and text, and it worked both ways, so your problem might be elsewhere...

 

[jbknight]

Thanks, for your help! I got it working using the SQL.