Problem with conditional formatting

[Genalube]

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.

[MMcCarthy]

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

[Genalube]

I have a query called parcelRange which formats the parcel numbers according to this SQL query:

SELECT Parcels.ParcelNo, Format(Val([ParcelNo]),"000") AS Expr1
FROM Parcels
ORDER BY Format(Val([ParcelNo]),"000");

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:

parcelRange.MoveLast

If parcelRange.AbsolutePosition >= 10 Then
    TotalNoParcels.Value = "0"
End If
If parcelRange.AbsolutePosition >= 100 Then
    TotalNoParcels.Value = "00"
End If
If parcelRange.AbsolutePosition >= 1000 Then
    TotalNoParcels.Value = "000"
End If

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).

Dim ModifiedNo As String

    If parcelRange("Expr1").Value < 10 Then
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If
    If parcelRange ("Expr1").Value >= 10 Then
        LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 1))
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If
    If parcelRange ("Expr1").Value >= 100 Then
        LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 2))
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If

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.

[MMcCarthy]

I have a query called parcelRange which formats the parcel numbers according to this SQL query:

SELECT Parcels.ParcelNo, Format(Val([ParcelNo]),"000") AS Expr1
FROM Parcels
ORDER BY Format(Val([ParcelNo]),"000");

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:

parcelRange.MoveLast

If parcelRange.AbsolutePosition >= 10 Then
    TotalNoParcels.Value = "0"
End If
If parcelRange.AbsolutePosition >= 100 Then
    TotalNoParcels.Value = "00"
End If
If parcelRange.AbsolutePosition >= 1000 Then
    TotalNoParcels.Value = "000"
End If

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).

Dim ModifiedNo As String

    If parcelRange("Expr1").Value < 10 Then
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If
    If parcelRange ("Expr1").Value >= 10 Then
        LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 1))
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If
    If parcelRange ("Expr1").Value >= 100 Then
        LeadingZeros = Left(LeadingZeros, (Len(LeadingZeros) - 2))
        String ModifiedNo = LeadingZeros & _
            parcelQueryData("ParcelNo").Value
    End If

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

[Killer42]

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...

SELECT Parcels.ParcelNo, [B]ModifiedNo([ParcelNo])[/B] AS Formatted
FROM Parcels
ORDER BY [B]ModifiedNo([ParcelNo])[/B];

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. :)

[Genalube]

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...

SELECT Parcels.ParcelNo, [B]ModifiedNo([ParcelNo])[/B] AS Formatted
FROM Parcels
ORDER BY [B]ModifiedNo([ParcelNo])[/B];

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?

[Killer42]

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...

Public function ModifiedNo(ByVal Num As String) As String

Dim RecCount As Long
' First, count the records in [B]Parcels[/B] table.
' I forget how to do that, just at the moment.
' Let's assume we now have that number in RecCount...

' Determine the number of digits to be displayed.
Dim Digits As Long
Digits = Len(Format(RecCount))

' Separate the value into its numeric and non-numeric parts
Dim Part1 As Long, Part2 As String
Part1 = Val(Num)
Part2 = mid$(Num, len(Format$(Part1)) + 1)

' Set the format mask for Part1.
Dim Mask As String
Mask = String$(Digits, "0")

' Build the final output string.
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.