New to the forums. So good to meet ya'll. I have a problem with a database that I built years ago when I REALLY didn't know what I was doing. We are logging reports in to a system. 2013.001, 2013.002, etc. The table field has a mask of #.0000. Everything went well until this year we have a bunch more reports. Our staff started by entering 2014.001 and are now at about 2014.900. Since they didn't start correctly by putting 2014.0001, when we get to the 1000th record for that fiscal year, the sorting on the form is going to go out of whack. Example: 2014.1000 will go way up in front of 2014.200. Is there a way to easily resolve this issue? Thanks so much.
Maybe not easily fixable?
- Thread starter avalve
- Start date
David R
I know a few things...
- Local time
- Today, 05:56
- Joined
- Oct 23, 2001
- Messages
- 2,633
If you can take your system down/out of service for about five minutes you can fix this.
Split the field into two fields - ReportYear and ReportNumber. Combine them in a query for display, though you'll have to change your forms a bit if your users really are manually entering the number... they will still need to have access to the ReportNumber field itself.
You can format ReportNumber "0000" and have it fill in the leading zeroes if they leave them off.
Split the field into two fields - ReportYear and ReportNumber. Combine them in a query for display, though you'll have to change your forms a bit if your users really are manually entering the number... they will still need to have access to the ReportNumber field itself.
You can format ReportNumber "0000" and have it fill in the leading zeroes if they leave them off.
- Local time
- Today, 11:56
- Joined
- Feb 19, 2013
- Messages
- 17,563
two suggestions:
Split the field into two fields and make them both numeric (the 'proper' way to do it)
or modify your code struture to allow for 9999 reports - you'll need to write some queries to change 2014.099 to 2014.0099
Split the field into two fields and make them both numeric (the 'proper' way to do it)
or modify your code struture to allow for 9999 reports - you'll need to write some queries to change 2014.099 to 2014.0099
nanscombe
Registered User.
- Local time
- Today, 11:56
- Joined
- Nov 12, 2011
- Messages
- 1,081
As you are using an Input Mask is the underlying field type, Number (Single) or Number(Long)?
It may be that the numbers have been stored as 2014.0010, 2014.0020 - 2004.9000.
Is this field used elsewhere or is it just in the one table?
If it's only in one place it may be easier to fix.
Are the existing (wrong) numbers significant, what would be the impact if they were changed?
It may be that the numbers have been stored as 2014.0010, 2014.0020 - 2004.9000.
Is this field used elsewhere or is it just in the one table?
If it's only in one place it may be easier to fix.
Are the existing (wrong) numbers significant, what would be the impact if they were changed?
