empty field

  • Thread starter Thread starter RML
  • Start date Start date
R

RML

Here is my code that works fine. However, there may be
times when nothing is in those fields and I get an error
about [Compress Model] = strFinal can't be empty. Would
like to be able to make it function if the fields are
empty thus producing a blank results field.

Thanks.

Dim strConcatenate As String
Dim strFinal As String
Dim strcheck As String
strConcatenate = IIf(IsNull([39]), "", [39] & ", ") & IIf
(IsNull([1]), "", [1] & ", ") & IIf(IsNull([2]), "", [2]
& ", ") & IIf(IsNull([3]), "", [3] & ", ") & IIf(IsNull
([4]), "", [4] & ", ") & IIf(IsNull([5]), "", [5] & ", ")
& IIf(IsNull([6]), "", [6] & ", ") & IIf(IsNull([7]), "",
[7] & ", ") & IIf(IsNull([8]), "", [8] & ", ") & IIf
(IsNull([9]), "", [9] & ", ") & IIf(IsNull([10]), "",
[10] & ", ") & IIf(IsNull([11]), "", [11] & ", ") & IIf
(IsNull([12]), "", [12] & ", ") & IIf(IsNull([13]), "",
[13] & ", ") & IIf(IsNull([14]), "", [14] & ", ") & IIf
(IsNull([15]), "", [15] & ", ") & IIf(IsNull([16]), "",
[16] & ", ") & IIf(IsNull([17]), "", [17] & ", ") & IIf
(IsNull([18]), "", [18] & ", ") & IIf(IsNull([19]), "",
[19] & ", ") & IIf(IsNull([20]), "", [20] & ", ") & IIf
(IsNull([21]), "", [21] & ", ") _
& IIf(IsNull([22]), "", [22] & ", ") & IIf(IsNull
([23]), "", [23] & ", ") & IIf(IsNull([24]), "", [24]
& ", ") & IIf(IsNull([25]), "", [25] & ", ") & IIf(IsNull
([26]), "", [26] & ", ") & IIf(IsNull([27]), "", [27]
& ", ") & IIf(IsNull([28]), "", [28] & ", ") & IIf(IsNull
([29]), "", [29] & ", ") & IIf(IsNull([30]), "", [30]
& ", ") & IIf(IsNull([31]), "", [31] & ", ") & IIf(IsNull
([32]), "", [32] & ", ") & IIf(IsNull([33]), "", [33]
& ", ") & IIf(IsNull([34]), "", [34] & ", ") & IIf(IsNull
([35]), "", [35] & ", ") & IIf(IsNull([36]), "", [36]
& ", ") & IIf(IsNull([37]), "", [37] & ", ") & IIf(IsNull
([40]), "", [40] & ", ") & IIf(IsNull([41]), "", [41]
& ", ") & IIf(IsNull([42]), "", [42] & ", ") & [38]
strcheck = Right(Trim(strConcatenate), 1)
If strcheck = "," Then
strFinal = Left(strConcatenate, Len(Trim
(strConcatenate)) - 1)
Else
strFinal = strConcatenate
End If
[Compress Model] = strFinal
 
hmmm, you could try replacing the last 8 lines of your code with the
following:

If strConcatenate = "" Or strConcatenate = ", " Then
[Compress Model] = Null
Else
strcheck = Trim(strConcatenate)
If Right(strcheck, 1) = "," Then
[Compress Model] = Left(strcheck, Len(strcheck) - 1)
Else
[Compress Model] = strcheck
End If
End If

if the [Compress Model] field in the underlying table is the primary key, or
if the field's Required property is set to Yes, then you won't be able to
set the value to Null. In that case, you'll have to come up with a value to
insert, or change the field settings in the table, in order to save the
record.

hth


RML said:
Here is my code that works fine. However, there may be
times when nothing is in those fields and I get an error
about [Compress Model] = strFinal can't be empty. Would
like to be able to make it function if the fields are
empty thus producing a blank results field.

Thanks.

Dim strConcatenate As String
Dim strFinal As String
Dim strcheck As String
strConcatenate = IIf(IsNull([39]), "", [39] & ", ") & IIf
(IsNull([1]), "", [1] & ", ") & IIf(IsNull([2]), "", [2]
& ", ") & IIf(IsNull([3]), "", [3] & ", ") & IIf(IsNull
([4]), "", [4] & ", ") & IIf(IsNull([5]), "", [5] & ", ")
& IIf(IsNull([6]), "", [6] & ", ") & IIf(IsNull([7]), "",
[7] & ", ") & IIf(IsNull([8]), "", [8] & ", ") & IIf
(IsNull([9]), "", [9] & ", ") & IIf(IsNull([10]), "",
[10] & ", ") & IIf(IsNull([11]), "", [11] & ", ") & IIf
(IsNull([12]), "", [12] & ", ") & IIf(IsNull([13]), "",
[13] & ", ") & IIf(IsNull([14]), "", [14] & ", ") & IIf
(IsNull([15]), "", [15] & ", ") & IIf(IsNull([16]), "",
[16] & ", ") & IIf(IsNull([17]), "", [17] & ", ") & IIf
(IsNull([18]), "", [18] & ", ") & IIf(IsNull([19]), "",
[19] & ", ") & IIf(IsNull([20]), "", [20] & ", ") & IIf
(IsNull([21]), "", [21] & ", ") _
& IIf(IsNull([22]), "", [22] & ", ") & IIf(IsNull
([23]), "", [23] & ", ") & IIf(IsNull([24]), "", [24]
& ", ") & IIf(IsNull([25]), "", [25] & ", ") & IIf(IsNull
([26]), "", [26] & ", ") & IIf(IsNull([27]), "", [27]
& ", ") & IIf(IsNull([28]), "", [28] & ", ") & IIf(IsNull
([29]), "", [29] & ", ") & IIf(IsNull([30]), "", [30]
& ", ") & IIf(IsNull([31]), "", [31] & ", ") & IIf(IsNull
([32]), "", [32] & ", ") & IIf(IsNull([33]), "", [33]
& ", ") & IIf(IsNull([34]), "", [34] & ", ") & IIf(IsNull
([35]), "", [35] & ", ") & IIf(IsNull([36]), "", [36]
& ", ") & IIf(IsNull([37]), "", [37] & ", ") & IIf(IsNull
([40]), "", [40] & ", ") & IIf(IsNull([41]), "", [41]
& ", ") & IIf(IsNull([42]), "", [42] & ", ") & [38]
strcheck = Right(Trim(strConcatenate), 1)
If strcheck = "," Then
strFinal = Left(strConcatenate, Len(Trim
(strConcatenate)) - 1)
Else
strFinal = strConcatenate
End If
[Compress Model] = strFinal
Click to expand...
 
It seems to me that you need to make a change to the table
or form design to allow a [Compress Model] to be a zero
length string.

Hope This Helps
Gerald Stanley MCSD
 
You have table fields named [1], [2], [3] etc?

Tell me it ain't so! :-(

RML, that is not a very good naming convention! And it probably means that
your database design has not been "normalized" properly. This will make your
database structure & code unnecessarily complicated, hard to understand, and
difficult for you or others to enhance in future.

Start here for a dry but instructive read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC
 
Back
Top