Access 2010 Question

I've a feeling this is one of those "shouldn't need to ask this 'cos it's obvious" but... So here goes.

I have an Access 2010 database obtained from elsewhere. One of the summation fields on a report shows as $xxxx. I want to change it to £xxxx.

In the Properties for the field the Format is $#,##0.00;($#,##0.00) .

If I try to change the two $ to £ it rejects it saying The text you entered isn't an item in the list. Indeed it comes up with this even trying to change it what it currently says. This clearly happens because there's a drop down which is limiting the input values.

So firstly how did this value get there in the first place?

And how can I change it to show £ ? Changing format to Currency results is slightly different alignment, and anyway I want to understand rather than just get round it.

Edit to correct title

Edited by cheshire_man (Thu 23-Aug-12 18:18:24)

Can't you just delete the 'Format' property and set the field properties to 'Currency' so that Access picks up the correct symbol from your Locale settings?

Oops - just read your post again and you say you don't want to do this. However I would have thought that you could re-define the Format statement.

Edited by longedge (Thu 23-Aug-12 19:43:00)

I can't see how to redefine the Format statement apart from via the Properties dialog and it won't let me change it to anything that is not in the drop-down list.

As I can't get to Design View I'm wondering if it's a web database.

Is that a reasonable hypothesis? And if so how can I tell?

What's the filename extension of the database file?

In reply to a post by longedge:

What's the filename extension of the database file?

.accdb

You should be able to view anything in design mode and change the underlying properties then unless there something going on when you open the database. Hold the shift key down when you open it to bypass any Macros or opening options.

IIRC you are talking about a report here so I don't quite see how a combobox (drop down) is involved but I have to put my hand up and say that I'm a bit lost with Access 2010 (I could find my way around Access 2003 blindfolded!).

Edited by longedge (Sat 25-Aug-12 18:15:33)

Design Mode is not an option in the View area, nor in the buttons on the bottom right of the Access window, nor when right-clicking on the Report name. Only Report view, Print preview, Layout view.

The drop-down is when I go into Layout view, select the filed in question and look at the Format entry in the Properties.

The actual database is one that is a template in Access 2010 and is downloaded from here. I'm using it as a learning experience to get my head round Access for some trivial domestic applications I want. I've used Access before but in a very simple way, my difficulty has been in getting good looking (to me) forms and reports, I quite like some of the feature of this DB so am trying to understand it more.

Thanks for your comments

As I said, I'm new to Access 2010 myself (and I hate it) anyway. I downloaded the Home Inventory template and created a test db with it.

With the new database open, I opened the "Items by Room" report and on the "File" tab the leftmost icon is "View". Clicking on the Icon toggles between edit and form view mode. In edit mode you see a property sheet on the right side of the screen. The sheet is contextual so as you select different components of the report it changes.

Select the "Total Replacement Price" field and then in the property sheet select the "Format" tab. The top item on that tab is "Format" and changing the selection in there to "Currency" does the trick.

Oh did I mention - I hate Access 2010!

Edited by longedge (Sat 25-Aug-12 21:21:28)

Yep, in the OP I said I could change it to Currency (and I have). Nevertheless I'm still trying to understand why Design View is not available. And also how the Format field of that form got there.

Maybe there are some things that man is not meant to understand (cheshire_man that is)

Well clicking on the "View" icon appears to toggle between the two modes.

The template from which the database was created would have been the origin of the formatting.

That's my (limited) understanding of what's happening.

Thank goodness I'm retired now and don't have to struggle with the latest complications of Office 2010 :^)

While I know nowt about Access 2010.
Whilst working I had to use excel and word. Just learning about an individual point did not cut ice when posed with a slightly different problem. I had to learn the basics from which expansion grew.
There are a number of free Tutorial websites Indeed I learnt about the windows 7 vagaries from http://www.gcflearnfree.org. Which I would never have found from help menu
It appears to have a lot of information about Access 2010. if you have not already been there.

I'm thinking that it is a predefined template that can't be altered in the traditional way via design view.

Presumably it's not possible to see the template or change some its settings?

Have a read through this which describes what is happening quite well.

The original locale of the template was US. When you create a new database using the template, the locale changes and rather than seeing "Currency" you see the hard coded version. You can only change this back to "Currency" which then picks up the new locale. What you don't appear to be able to do is define the "Currency" format forcing leading 0's with place holders in an input mask.

The currency format should always align correctly as far as I know (right justified with 2 decimal positions).

Interesting...

Wow, thanks for that! At least it provides an explanation, which is what I sought.

Whether it's the correct thing for Access to do in the more general sense is another issue, but as I'm only doing domestic personal apps it's fine for me

I've now found that database is a web database - the clue (doh!) was the tiny globe on the tables, forms, queries and reports icons on the Navigation Pane on the left.

I've also found that Design View is NOT available for a web database. It seems that MS inhibited it as it gave too many facilities that couldn't be handled by Sharepoint Server (the host for publishing a web database). Create a client (or desktop) database and Design View is available.

As an educational exercise I'm moving the Home Inventory database to a client database. Though the tables can be exported from web and imported to client, queries, forms and reports can't be, so as that's where much of my education is needed it's a useful experience.