Nothing in format drop down box, form design

[JamesMcS]

Hi! So I've got a form, bound to a table which shows monthly sales figures for each product - so product number, then -1 for last month's sales, -2 for the preceding month, right back to -24.

The weird thing is that when I go to format the -1, -2 etc. text boxes as currency, the drop down box in the property sheet is empty.... annoying! i can enter a custom format, like £#,##0, but it doesn't have any effect on the format of the box when I switch to form view.I've just tried it on product number too, same thing. Does anyone know what could cause this?

Cheers

James

 

[DCrake]

If this is in the properties of the column in the query if the query has not been run so to speak Access does not know what type of data the column is returning. Just type in the format you want, run the query and see if it works.

 

[JamesMcS]

Ah no sorry, it's the actual form design properties window that's causing the problem - so if you right click on a text box in form design, click properties, it's the format option in there. The form's source is a table rather than a query.

Entering a custom format has no effect... I've just tried creating a new form and the problem remains. There's data in the table and everything!

 

[JamesMcS]

Here's a shot of what I'm banging on about:

 

[JamesMcS]

Right, I think it's because my table is full of text fields, not numbers - the git. Strange that a make table query full of sums creates text fields... at least I know the cause of the formatting problem now... I was so sure they were numbers! Cheers for answering though David - enjoy the weekend, hope the weather's as good there as it's set to be here!

 

[DCrake]

You should not be doing make tables anyway. This will only bloat your app. Once you have ctreated your table template use deletes and appends instead.

 

[JamesMcS]

I'll try that, soon anyway - there's loads of them so that'll be a liddle project for the future! Cheers again chap

 

[JamesMcS]

Actually having said that, the make tables are mostly based on crosstabs. Do you know if the delete/append method would work with a crosstab?

 

[DCrake]

Maybe the first question is why do you need to make tables out of cross tab queries in the first place?

 

[JamesMcS]

Well - basically what I'm doing is making a sales reporting DB. So amongst other things, it cobbles together 15 million sales records in all sorts of different ways - by manufacturer, by product cat, and cuts it in different ways - financial year on year, calendar year on year, quarterly etc. So for each possible option (manufacturer FYOY, product cat RYOY and so on) there's a separate table - so that users don't have to wait donkey's for queries to run, I've made them all make tables and based the forms on them.

Time consuming to build all the tables, but the PC does that overnight and nobody has to wait for queries to run... best solution I could find to keep the DB speed up - it's massive, more suited to SQL server really (that'll be version 2)

 

[JamesMcS]

Just tried to rep ya - it didn't let me! Apparently you're the last person I added rep to....

 

[DCrake]

You can always use my feedback form on my website to provide feedback/reputation.

 

[JamesMcS]

Done and done! I managed to sort the problem out - just used ccur(sum(.... in the crosstab for the value, works fine.

Have a wild one!

 

[Daman]

@JamesMcS can you please tell me how you fixed it. i am having the same issue.

 

[Gasman]

@JamesMcS can you please tell me how you fixed it. i am having the same issue.

13 year old thread. I doubt the O/P is even around anymore?, but you never know.
He does say that the CCur() function solved his problems?