By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Problem with conditional formatting

P: 25
I am trying to format text that represents user defined "numbers" these numbers have text on the back end sometimes.

Here is some examples of these numbers, which is how they are stored:

1, 2, 3, 12, 12B, 15, 16, 20, 100, 156L

I want to format them based on how many records there are in the table. The table is called "Parcels" the numbers are in the field "ParcelNo" as text.

If there are up to nine records, then formated like this

1, 2, 3, 3B, 4, 5, 6, 7, 8, 9, 9B, 9D, 9G, etc.

If there are between 10 and 99 records, then formated like this:

01, 02, 02B, 02D, 03, 12, 12B, 15, 16, 20, etc.

If there are between 100 or more (there won't be more than 999) records, then formated like this:

001, 002, 002B, 002D, 003, 012, 012B, 015, 020, 100, 156L, etc.

I have scrapped together something that works in VBA, but would like to do it in a query and don't seem able to find out how to do so. Is it possible? I am trying to learn access as I go and appreciate your help.
Dec 21 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry I don't really understand what you are talking about. Try posting the VBA code you are currently using and I may be better able to follow it.

Mary
Dec 21 '06 #2

P: 25
I have a query called parcelRange which formats the parcel numbers according to this SQL query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Parcels.ParcelNo, Format(Val([ParcelNo]),"000") AS Expr1
  2. FROM Parcels
  3. ORDER BY Format(Val([ParcelNo]),"000");
  4.  
The query is sorted ascending on the Expr1 field.

When I open a form I have it run this (in part) I didnít really know how to do this well so I used a record set then did the following:
Expand|Select|Wrap|Line Numbers
  1. parcelRange.MoveLast
  2.  
  3. If parcelRange.AbsolutePosition >= 10 Then
  4.     TotalNoParcels.Value = "0"
  5. End If
  6. If parcelRange.AbsolutePosition >= 100 Then
  7.     TotalNoParcels.Value = "00"
  8. End If
  9. If parcelRange.AbsolutePosition >= 1000 Then
  10.     TotalNoParcels.Value = "000"
  11. End If
  12.  
The totalNoParcels box is on my form and is used, as the leading zeros variable, when the user mashes a button control it runs (in part).
Expand|Select|Wrap|Line Numbers
  1. Dim ModifiedNo As String
  2.  
  3.     If parcelRange("Expr1").Value < 10 Then
  4.         String ModifiedNo = LeadingZeros & _
  5.             parcelQueryData("ParcelNo").Value
  6.     End If
  7.     If parcelRange ("Expr1").Value >= 10 Then
  8.         LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 1))
  9.         String ModifiedNo = LeadingZeros & _
  10.             parcelQueryData("ParcelNo").Value
  11.     End If
  12.     If parcelRange ("Expr1").Value >= 100 Then
  13.         LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 2))
  14.         String ModifiedNo = LeadingZeros & _
  15.             parcelQueryData("ParcelNo").Value
  16.     End If
  17.  
The modified No is the way I want it displayed, I am just wanting to do it all in a query if it is possible.
Dec 22 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I have a query called parcelRange which formats the parcel numbers according to this SQL query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Parcels.ParcelNo, Format(Val([ParcelNo]),"000") AS Expr1
  2. FROM Parcels
  3. ORDER BY Format(Val([ParcelNo]),"000");
  4.  
The query is sorted ascending on the Expr1 field.

When I open a form I have it run this (in part) I didnít really know how to do this well so I used a record set then did the following:
Expand|Select|Wrap|Line Numbers
  1. parcelRange.MoveLast
  2.  
  3. If parcelRange.AbsolutePosition >= 10 Then
  4.     TotalNoParcels.Value = "0"
  5. End If
  6. If parcelRange.AbsolutePosition >= 100 Then
  7.     TotalNoParcels.Value = "00"
  8. End If
  9. If parcelRange.AbsolutePosition >= 1000 Then
  10.     TotalNoParcels.Value = "000"
  11. End If
  12.  
The totalNoParcels box is on my form and is used, as the leading zeros variable, when the user mashes a button control it runs (in part).
Expand|Select|Wrap|Line Numbers
  1. Dim ModifiedNo As String
  2.  
  3.     If parcelRange("Expr1").Value < 10 Then
  4.         String ModifiedNo = LeadingZeros & _
  5.             parcelQueryData("ParcelNo").Value
  6.     End If
  7.     If parcelRange ("Expr1").Value >= 10 Then
  8.         LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 1))
  9.         String ModifiedNo = LeadingZeros & _
  10.             parcelQueryData("ParcelNo").Value
  11.     End If
  12.     If parcelRange ("Expr1").Value >= 100 Then
  13.         LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 2))
  14.         String ModifiedNo = LeadingZeros & _
  15.             parcelQueryData("ParcelNo").Value
  16.     End If
  17.  
The modified No is the way I want it displayed, I am just wanting to do it all in a query if it is possible.

Can you post a reply to this thread on Wednesday/Thursday to remind me to have a look at it.

Happy Holidays.

Mary
Dec 25 '06 #4

Expert 5K+
P: 8,434
Can you post a reply to this thread on Wednesday/Thursday to remind me to have a look at it.
As a quick workaround, what about making the relevant VBA code into a public Function (if it isn't already) and using that in the query? Something like...
Expand|Select|Wrap|Line Numbers
  1. SELECT Parcels.ParcelNo, ModifiedNo([ParcelNo]) AS Formatted
  2. FROM Parcels
  3. ORDER BY ModifiedNo([ParcelNo]);
Even though you seem to be talking about a small number of records, it may require some tweaking to avoid having to count all of the records every time the function is invoked.

Mary, you can consider this your reminder. :)
Dec 27 '06 #5

P: 25
As a quick workaround, what about making the relevant VBA code into a public Function (if it isn't already) and using that in the query? Something like...
Expand|Select|Wrap|Line Numbers
  1. SELECT Parcels.ParcelNo, ModifiedNo([ParcelNo]) AS Formatted
  2. FROM Parcels
  3. ORDER BY ModifiedNo([ParcelNo]);
Even though you seem to be talking about a small number of records, it may require some tweaking to avoid having to count all of the records every time the function is invoked.

Mary, you can consider this your reminder. :)
I am rather new at using access and VBA, and don't quite understand. In the query you used, is the name of the function "ModifiedNo" ? Do I store it under the macros or modules objects or somewhere else?

Since the number of leading zeros is dependant upon the number of records, how could I tweak it to not count all the records?
Dec 28 '06 #6

Expert 5K+
P: 8,434
I am rather new at using access and VBA, and don't quite understand. In the query you used, is the name of the function "ModifiedNo" ? Do I store it under the macros or modules objects or somewhere else?
Yes, ModifiedNo is a function name I made up, based on a variable name you used earlier. You would place the function in a module, defined something like this...
Expand|Select|Wrap|Line Numbers
  1. Public function ModifiedNo(ByVal Num As String) As String
  2.  
  3. Dim RecCount As Long
  4. ' First, count the records in Parcels table.
  5. ' I forget how to do that, just at the moment.
  6. ' Let's assume we now have that number in RecCount...
  7.  
  8. ' Determine the number of digits to be displayed.
  9. Dim Digits As Long
  10. Digits = Len(Format(RecCount))
  11.  
  12. ' Separate the value into its numeric and non-numeric parts
  13. Dim Part1 As Long, Part2 As String
  14. Part1 = Val(Num)
  15. Part2 = mid$(Num, len(Format$(Part1)) + 1)
  16.  
  17. ' Set the format mask for Part1.
  18. Dim Mask As String
  19. Mask = String$(Digits, "0")
  20.  
  21. ' Build the final output string.
  22. ModifiedNo = Format$(Part1, Mask) & Part2
There are a number of things which could have been done better (or at least more briefly) in this code. for instance, I normally would have combined a number of steps, but I was trying to keep the individual statements relatively simple.


Since the number of leading zeros is dependant upon the number of records, how could I tweak it to not count all the records?
Excellent question. :)

What I meant was that you might be able to count the records once at the start, then reuse that result rather than reading the entire table twice for every record processed. We can discuss this later, if you decide to go this way, and if the performance is an issue. So far I'm just throwing ideas at you. You might use them, change them, or go an entirely different way.
Dec 29 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.