Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 30th, 2005, 05:22 PM
Authorized User
 
Join Date: Jun 2004
Location: Raleigh, NC, USA.
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!


Reply With Quote
  #2 (permalink)  
Old October 30th, 2005, 05:36 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #3 (permalink)  
Old November 3rd, 2005, 12:14 AM
Authorized User
 
Join Date: Jun 2004
Location: Raleigh, NC, USA.
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

Reply With Quote
  #4 (permalink)  
Old November 3rd, 2005, 02:38 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
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
Reply With Quote
  #5 (permalink)  
Old November 12th, 2005, 01:41 PM
Authorized User
 
Join Date: Jun 2004
Location: Raleigh, NC, USA.
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

Reply With Quote
  #6 (permalink)  
Old November 12th, 2005, 07:03 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
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
Reply With Quote
  #7 (permalink)  
Old November 14th, 2005, 01:47 PM
Authorized User
 
Join Date: Jun 2004
Location: Raleigh, NC, USA.
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
Reply With Quote
  #8 (permalink)  
Old November 14th, 2005, 02:50 PM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:38 AM.


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