Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 30th, 2005, 05:22 PM
Authorized User
 
Join Date: Jun 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Number Format - Need Help!

In Access 2003, I created a Number field called ProvenienceNumber (Number, Single). For my client, I created a format 0.0##, which I thought would allow the user to enter in separate values 203.1 and 203.10 (these should be treated as two different values in the database). However, the field is formatting the 203.10 as 203.1, which is incorrect. How do I fix it?

Thank you in advance!


 
Old October 30th, 2005, 05:36 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Bridog39,

Since 203.1 and 203.10 are exactly the same number, even though they may be displayed differently, any numeric comparison between the two would show them as equal. The best way around this would be to change this to be a text field, and then base your comparisons.

Out of interest, why would you want these values to be treated differently? If this doesn't work for you, perhaps we could figure out a better way of doing it.

Hope that helps,

Mike

Mike
EchoVue.com
 
Old November 3rd, 2005, 12:14 AM
Authorized User
 
Join Date: Jun 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike,

Thanks for the response. I had originally created the ProvenienceNumber as a text field because it has no real mathematical value, but my client couldn't sort in numerical order (for example, if they had 10, 20, 2 and 4 as their provenience numbers, it would sort 10, 2, 20, 4 and not 2, 4, 10, 20 because it was sorting as text), so I changed it to a number.

In this case, 203.1 and 203.10 are different (the 203 represents site coordinates and the 1 and 10 represent how deep they dug (it's for an archaeology database)).

Would it be easier to change it to a text field and try to get it to sort as a number on reports and such? If so, how would I do that?

Thanks again,

Brian Tibbetts
bridog39

 
Old November 3rd, 2005, 02:38 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Brian,

I would change it back to a text field. Then, with your reports, add a field to your report - -

expr1:val(ProvenienceNumber)

Then sort on it.

It returns a numeric value from a text field.

HTH,

Kevin

dartcoach
 
Old November 12th, 2005, 01:41 PM
Authorized User
 
Join Date: Jun 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Dartcoach,

I tried your advice and changed ProvenienceNumber into a 10 character text field and use the val(ProvenienceNumber) expression to sort my provenience data entry form, but I don't know how to get Access to sort by an expression. Is that possible?

Thanks for your help,

Brian

 
Old November 12th, 2005, 07:03 PM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

Brian,

Its a silly question but if the two numbers represent two different things is there a reason why you have them as a single field. I can think of future problems with this. for example if you needed to create a query for all digs that went to a depth of 15 feet how would you do it. Wouldn't it be better to seperate the fields and then if you needed to report them as a joined figure you can conjoin field values. For instance if you had two field C_nates and Depth and you wanted these joined on a report you would use a control with a value of = C_nates & "." & Depth.

Jim
 
Old November 14th, 2005, 01:47 PM
Authorized User
 
Join Date: Jun 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jim,

Your question does have merit, and it may be the way that I have to approach this problem in the future. When I created the database, I didn't know that the numbers to the left of the decimal and to the right of the decimal meant two separate things. I may have to create two new fields in my Provenience table, run an update query to get the current values into the table, then create a textbox and some code in my form to separate the two values in the textbox and update the table. Do you think that's the way I should handle it?

Thanks a lot!

Brian

Quote:
quote:Originally posted by Figgis
 Brian,

Its a silly question but if the two numbers represent two different things is there a reason why you have them as a single field. I can think of future problems with this. for example if you needed to create a query for all digs that went to a depth of 15 feet how would you do it. Wouldn't it be better to seperate the fields and then if you needed to report them as a joined figure you can conjoin field values. For instance if you had two field C_nates and Depth and you wanted these joined on a report you would use a control with a value of = C_nates & "." & Depth.

Jim
 
Old November 14th, 2005, 02:50 PM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

If you can seperate the data it will probably be better in the long run. It seems a very strange convestion to display numbers like this but then I do not know much about Archaeology. I don't know if I'd leave the form as it is and use code. I think while I was making changes then I think I'd change the form so that the two figures naturally feed two fields within the table. After all you can lay the form out however you want and if your users are happier having the numbers side by side with a dot seperating them then you can do this on a form and still make your life easier.


Regards,


Jim





Similar Threads
Thread Thread Starter Forum Replies Last Post
Number format in the table zrtv SQL Server 2005 6 March 7th, 2008 03:18 AM
format-number dropping 0 stolte XSLT 1 January 18th, 2008 04:46 PM
Format Number Dink Classic ASP Databases 2 January 23rd, 2007 01:40 AM
Format Number hcweb Classic ASP Basics 4 October 27th, 2004 11:23 AM
format number tgopal Javascript 2 April 30th, 2004 03:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.