Currency not showing consistently from Excel spreadsheets

[sephiroth2906]

Hello!


I have a database that is drawing information from several Excel spreadsheets. All of them have the same exact fields, including a cost field. The problem I am having is that in the Query I created to put them into one sheet, some of them have the value as currency, and some do not. I have double-checked the original spreadsheets, and the individual sheets show the value as currency when opened one by one.

Any ideas how I can fix this?

[TheShabz]

Access has a tendency of doing that. In your query, use the ccur() function. It will cast any numeric field into currency. Just make sure they all have the proper form. Check it the first time around to make sure there aren't any errors.

For example:
SELECT Ccur(TotalCost) As FinalCost
FROM tblExpenses

[boblarson]

Or you can use FormatCurrency as well if you want to ensure control over the decimal places.

SELECT FormatCurrency(TotalCost, 2) As FinalCost
FROM tblExpenses

[sephiroth2906]

OK, I am a real newbie and am going to have to humble myself and ask for a little TLC here. I managed to figure out how to use SQL to union all of the spreadsheets, but I am not sure how to incorporate the solution you are giving me here.

Here is what I did in SQL to merge the spreadsheets:
Select * FROM ConferenceRooms
Union
Select * FROM 8thFloorLunchRoom
Union
Select * FROM 8thFloorSpareOfficeAndHallway
Union
Select * FROM 8thFloorStorageRoom
Union
...

Each spreadsheet has a field labeled "Cost" unnecessarily

Say I want to use Ccur. What would I change to accomplish this?

I really appreciate the hand holding. I want to learn.

[TheShabz]

Save that query as whatever you want. Now, open up a new query in design view and throw that query in there. Drag down all the fields (do not use *, you can double click the query name and it will highlight all of the fields) Find the Cost one and change it from Cost to:
TotalCost: FormatCurrency(Cost, 2)

You can change the "TotalCost" part to anything other than "Cost". Doing so will cause a circular argument and Access will get mad at you. Also, I think Bob's suggestion at using FormatCurrency instead of Ccur is probably a better option as Ccur probably wont take care of any rounding issues that may arise.

[rpeare]

Select *,Ccur(Cost) As FinalCost FROM ConferenceRooms
Union
Select *Ccur(Cost) As FinalCost FROM 8thFloorLunchRoom
Union
Select *Ccur(Cost) As FinalCost FROM 8thFloorSpareOfficeAndHallway
Union
Select *Ccur(Cost) As FinalCost FROM 8thFloorStorageRoom
Union
Select *Ccur(Cost) As FinalCost FROM Accounting
union 
...

you may also be able to do something like this (in one query instead of 2).

[sephiroth2906]

Thank you to everyone that replied here. That is going to help me a great deal in the future in understanding how to create better databases.

In this instance though, since my last post, I found a different solution, and I am hoping that this might help people in the future.

I am dealing with a couple dozen spreadsheets. Several of them were created by different people. I thought I made them all the same, but a closer look told me otherwise.

I needed to not only spend a couple hours data scrubbing (I had dates in different formats, some numbers with pound signs, etc.) but there are items on these spreadsheets that do not have costs associated with them.

To remedy this, I made sure that the first item on each spreadsheet did have a cost associated with it. Once that was accomplished, the Unioned query decided that they were all indeed currency instead of text. I guess I don't understand why the individual spreadsheets that were imported were reporting correctly and the union was not, but, regardless, that fixed the problem.

Thanks again for the help! I am sure I will be back...

Now, how do I tag this as solved?