Setting a Default Value in a Table Based on Another Field in the T

[Amy Baggott]

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import the data?

 

[Sprinks]

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks

 

[Amy Baggott]

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import the data?

 

[Douglas J Steele]

Why not store the booth number and the prefix as two separate fields?

Then, you can create a query that concatenates them in both ways and use
that query wherever you would otherwise have used the table.

To concatenate the two fields and have the booth number have leading zeroes,
you'd use something like:

[Prefix] & Format([BoothNumber], "0000")

To concatenate them without the leading zeroes, you'd use

[Prefix] & [BoothNumber]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import

the data?

 

[Sprinks]

Hi, Amy.

This is more generic to handle both conditions:

Public Function MyNewNumber(strOrig As String) As String
If IsNumeric(Left(strOrig,1)) Then
If Left(strOrig,1) = "0" Then
MyNewNumber = Trim(Right(strOrig,len(strOrig-1))
Else
MyNewNumber = strOrig
End If
Else
MyNewNumber = Trim(Mid(strOrig,1) & Right(strOrig, len(strOrig)-2))
End If

Sprinks

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import the data?

 

[Amy Baggott]

Would it be advisable to simply set up an import procedure that imports the
data, then runs a code to put this field in. The reason I want to store it,
rather than compute it on the fly, is because the exceptions go up to the web
site with descriptive names such as "Parking Lot D, #1" or "Lobby B, Level
2", which is hard to set up on the fly, as it depends on what kind of space
the sales guys decide to sell next (a trade show is an unpredictable thing
<G>).

Hi, Amy.

This is more generic to handle both conditions:

Public Function MyNewNumber(strOrig As String) As String
If IsNumeric(Left(strOrig,1)) Then
If Left(strOrig,1) = "0" Then
MyNewNumber = Trim(Right(strOrig,len(strOrig-1))
Else
MyNewNumber = strOrig
End If
Else
MyNewNumber = Trim(Mid(strOrig,1) & Right(strOrig, len(strOrig)-2))
End If

Sprinks

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks


:

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import the data?

 

[Douglas J Steele]

I don't see how that addresses her requirements!

First, you seem to have a typo in your code:

Trim(Right(strOrig,len(strOrig-1))

should probably be

Trim(Right(strOrig,len(strOrig)-1))

As well, Trim(Mid(strOrig,1) is going to return the entire string, not just
the alpha characters. Amy's implied that the prefix may not always be a
single letter, though, so you can't simply change that to
Trim(Mid(strOrig,1,1)

However, the bigger problem is that It seems to be limited to removing the
first leading zero only. Even if we correct the second error mentioned
above, if the number is, say, W0032, you'll return W032, not W32.

A more generic approach would be:

Public Function MyNewNumber(strOrig As String) As String
Dim intLoop As Integer

' Find the first numeric value in the string

For intLoop = 1 To Len(strOrig)
If IsNumeric(Mid$(strOrig, intLoop, 1)) Then
Exit For
End If
Next intLoop

' If the first character is numeric, intLoop will be 1
' If there are no numeric characters, intLoop will be
' one more than the length of the string.

If intLoop = 1 Then
MyNewNumber = CStr(Val(strOrig))
Else
If intLoop > Len(strOrig) Then
MyNewNumber = Trim$(strOrig)
Else
MyNewNumber = Trim$(Mid$(strOrig, 1, intLoop - 1)) & _
CStr(Val(Mid$(strOrig, intLoop)))
End If
End If

End Function



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Amy.

This is more generic to handle both conditions:

Public Function MyNewNumber(strOrig As String) As String
If IsNumeric(Left(strOrig,1)) Then
If Left(strOrig,1) = "0" Then
MyNewNumber = Trim(Right(strOrig,len(strOrig-1))
Else
MyNewNumber = strOrig
End If
Else
MyNewNumber = Trim(Mid(strOrig,1) & Right(strOrig, len(strOrig)-2))
End If

Sprinks

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks


:

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import

the data?

 

[Amy Baggott]

Partly because the data for this table come from an outside source as a
concatenated field (they pull it directly from the floor plan), and the data
in the tables it has to link to come from a different outside source via a
complex query, any change to which seems to confuse the whole system for
weeks at a time. Also, it would not address the problem of the exceptions I
mentioned earlier, which require a description of where the booth is since
they are not actually within the exhibit hall. Your theory would work
beautifully if my exhibitors and my bosses didn't like to complicate things
so much

Why not store the booth number and the prefix as two separate fields?

Then, you can create a query that concatenates them in both ways and use
that query wherever you would otherwise have used the table.

To concatenate the two fields and have the booth number have leading zeroes,
you'd use something like:

[Prefix] & Format([BoothNumber], "0000")

To concatenate them without the leading zeroes, you'd use

[Prefix] & [BoothNumber]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks


:

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import

the data?

 

[Sprinks]

Yes, Doug. There were indeed two typos in the code provided. My apologies
to you, Amy.

I didn't pick up, however, that Amy had implied there could be more than one
letter, and so tailored the code to what I understood to be the specific
possible conditions.

Sprinks

I don't see how that addresses her requirements!

First, you seem to have a typo in your code:

Trim(Right(strOrig,len(strOrig-1))

should probably be

Trim(Right(strOrig,len(strOrig)-1))

As well, Trim(Mid(strOrig,1) is going to return the entire string, not just
the alpha characters. Amy's implied that the prefix may not always be a
single letter, though, so you can't simply change that to
Trim(Mid(strOrig,1,1)

However, the bigger problem is that It seems to be limited to removing the
first leading zero only. Even if we correct the second error mentioned
above, if the number is, say, W0032, you'll return W032, not W32.

A more generic approach would be:

Public Function MyNewNumber(strOrig As String) As String
Dim intLoop As Integer

' Find the first numeric value in the string

For intLoop = 1 To Len(strOrig)
If IsNumeric(Mid$(strOrig, intLoop, 1)) Then
Exit For
End If
Next intLoop

' If the first character is numeric, intLoop will be 1
' If there are no numeric characters, intLoop will be
' one more than the length of the string.

If intLoop = 1 Then
MyNewNumber = CStr(Val(strOrig))
Else
If intLoop > Len(strOrig) Then
MyNewNumber = Trim$(strOrig)
Else
MyNewNumber = Trim$(Mid$(strOrig, 1, intLoop - 1)) & _
CStr(Val(Mid$(strOrig, intLoop)))
End If
End If

End Function



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Amy.

This is more generic to handle both conditions:

Public Function MyNewNumber(strOrig As String) As String
If IsNumeric(Left(strOrig,1)) Then
If Left(strOrig,1) = "0" Then
MyNewNumber = Trim(Right(strOrig,len(strOrig-1))
Else
MyNewNumber = strOrig
End If
Else
MyNewNumber = Trim(Mid(strOrig,1) & Right(strOrig, len(strOrig)-2))
End If

Sprinks

Unfortunately, they are not always the same length. The booth number is
usually one letter followed by 4 or 5 numbers, although there are some
exceptions that can be filtered out with a fairly straightforward If/Then
statement. I only want it to shorten the booth number if the first number is
a zero.

:

Hi, Amy.

The phrase "have this field automatically populate" is a red flag that a
field probably is not required at all. Since the value will be calculated
from another field, simply calculate it on-the-fly as needed.

You don't say whether the booths are always four characters long, have one
letter followed by three numbers, etc., but assuming that they are, a custom
function placed in a global module could return the value you seek:

Public Function MyNewNumber(strOrig As String) As String
MyNewNumber = Trim(Mid(strOrig,1,1)& Mid(strOrig,3,3))
End Function

Whenever you need the number, pass the value of your field to the function:

=MyNewNumber([BoothNumber])

Hope that helps.
Sprinks


:

I have a table of booth numbers and move-in dates that is populated by
importing data from an outside source. However, I need to be able to use the
booth number in two separate ways. Internally, the booth numbers are stored
using leading zeros for the low-numbered booths (e.g. W0432), but in the
lists I export to our web site, these zeros are dropped for aesthetic reasons
(e.g. W432). This table has to link internally with the booth numbers having
the leading zeros (which is how they are imported), but when I export it to
the web site for the online Service Kit, it has to link with the booths
without any leading zeros. So I have added a field to the table called
"ListingBooth" which contains the booth numbers without listing booths. What
I'd like to do is to have this field automatically populate, dropping leading
zeros where appropriate. Any ideas how I can do this when I import

the data?