Filtering data in an Array

[Guest]

I have created a simple procedure to assign values form various controls on a
form to an array and then update the data into a table, however, there is a
possibility that some of the controls will not contain data and therefore I
do not want the procedure to add null values to my table. I have tried to
control this with an If..Then statement, but I can't get it to read the array
values as Null and skip it. How can this be done?

Dim rs As New ADODB.Recordset
Dim WorkAssignment(10) As Variant 'An Array that contains work assignment.
Dim Hours(10) As Variant 'An Array that contains hours.
Dim intCount As Integer

WorkAssignment(0) = cboProjectName1
WorkAssignment(1) = cboProjectName2
WorkAssignment(2) = cboProjectName3
WorkAssignment(3) = cboProjectName4
WorkAssignment(4) = cboProjectName5
WorkAssignment(5) = "BPI"
WorkAssignment(6) = "Training"
WorkAssignment(7) = "Out of Office"
WorkAssignment(8) = "Holiday"
WorkAssignment(9) = "Swiss Army"
WorkAssignment(10) = "Other"

Hours(0) = txtHours1
Hours(1) = txtHours2
Hours(2) = txtHours3
Hours(3) = txtHours4
Hours(4) = txtHours5
Hours(5) = txtBPIHours
Hours(6) = txtTrainingHours
Hours(7) = txtOutofOfficeHours
Hours(8) = txtHolidayHours
Hours(9) = txtSwissArmyHours
Hours(10) = txtOtherHours

'Opens the BPI Resource Hours table and then updates the table with the
values in the controls.

rs.Open "tblBPIResourceHours", CurrentProject.Connection, adOpenDynamic,
adLockPessimistic

With rs
Start: For intCount = 0 To 10
If WorkAssignment(intCount) = Null Then
GoTo Start
End If
.AddNew
!ResourceName = cboBPIResourceName
!WeekOf = txtWeekOf
!WorkAssignment = WorkAssignment(intCount)
!Hours = Hours(intCount)
.Update
.MoveNext
Next intCount
End With

MsgBox "Your Hours have been saved.", , "Saved BPI Resource Hours"

 

[Wei Lu]

Hi Jason,

Welcome to use MSDN Managed Newsgroup Support!

From your description, my understanding of this issue is: You can not check
your variable which is Variant DataType is null properly. If i
misunderstood your concern, please feel free to point it out.

As Null values are never known, and therefore can never equal anything, you
cannot test directly for equality (i.e. If varNull=Null Then...), as this
will always return False. Instead, you must use the IsNull function in VBA
to check if a value is a Null:

Dim varNull As Variant
If IsNull(varNull) Then
' The variant contains a Null value.
Else
' The variant does not contain a Null value.
End If

Hope this will be helpful! If you have any problems or concerns, don't
hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Wei Lu
Microsoft Online Partner Support
======================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.