evaluate a variable to see if it is 'empty' or if it contains just 'spaces'...

[Gary]

I would like to evaluate the variable vartext(4) to see if the variable
is a blank line, now i know a 'blank line' has a number of definitions,
so i'll outline what i'm doing quickly so you know exactly what i mean.

Im copying data from the clipboard into a control called 'copied data'.

then I'm splitting that data up into useful chunks, i'll give you an
example.

this is an example of pasted data into the control 'copied data' : -
"
COMPANY NAME
ADD1
ADD2
ADD3
POSTCODE
Company No. 1234567
' just some spaces here ...
' totally blank line'
' just some more spaces here ...
DATE OPENED
DATE CLOSED
"

I am using the following code to extract the company name, company
number and address details from this data and put it into the correct
fields in the database, Which works fine: -

Private Sub Command12_Click()
Dim companyinteger As Variant
Dim companynumber As Variant

' split the copied data field, assigning each line to its own variable.

' extract the company number into the company number field.
varText = Split(copieddata, vbCrLf)

' assign the fifth line to the variable stringcompanynumber
companynumber = varText(5)

' assign the 'number' out of stringcompanynumber to companynumber
companyinteger = Mid(companynumber, 13)

' insert companynumber into 'company reg no' field.
Me.Company_Reg_No.Value = companyinteger

' extract the company name into the companyname field.
companyname = varText(0)
Me.Company_Name.Value = companyname

' put the address data into seperate address line fields.
Me.address1.Value = varText(1)
Me.address2.Value = varText(2)
Me.address3.Value = varText(3)
Me.address4.Value = varText(4)

End Sub

... I now need to extract the 'date opened' and 'date closed' info to
and put each into it's respective field in the database in the same
way. However the problem is that there are an indefinate number of
blank lines, or lines with some spaces and nothing else between the
company number field and the date opened field.

So I want to check each vartext line after, the last address line, to
see if it actually isn't empty and doesn't just contain spaces. If it
is empty or just contains spaces I want to ignore it and move to the
next line, if however it has some data i will know that this will be
the 'date opened' data, and the next line will be 'date closed' so i
can extract the useful data from these two lines and put into the right
fields.

I hope i've made sense, please help - as i'm close but not quite there
yet...

thanks,

Gary.

 

[Douglas J Steele]

Try:

If Len(Trim(varText(intLoop) & "")) > 0 Then
' varText(intLoop) contains a value
Else
' varText(intLoop) doesn't contain a value
End If

 

[Gary]

You are an amazing help Douglas. It's easy to see why you are a MVP.

i'm having a bit of trouble i tried your advice, and i've got this
now... i'm sure there must be a better way of coding this but even in
its present state it does not quite work, can you take a look and see
what you think, many thanks again!

If Len(Trim(varText(5) & "")) > 0 Then
Me.status.Value = varText(5)
Else
If Len(Trim(varText(6) & "")) > 0 Then
Me.status.Value = varText(6)
Else
If Len(Trim(varText(7) & "")) > 0 Then
Me.status.Value = varText(7)
Else
If Len(Trim(varText(8) & "")) > 0 Then
Me.status.Value = varText(8)
Else
If Len(Trim(varText(9) & "")) > 0 Then
Me.status.Value = varText(9)
End If


Gary

 

[Gary]

just in case it isn't clear - what im trying to achieve with the above
code in english is this...

if varttext(5) contains something then it must the status of the
company, so assign the text to the status value.
otherwise check to see if the next vartext (i.e. vartext(6) contains
something if it does assign it to the status value.
otherwise check to see if the next vartext ...
etc..

 

[Gary]

'then it must' = ' then it must be'

'i wished dejanews let me edit a message after i'd sent it!

 

[Douglas J Steele]

If I'm understanding you correctly, you want something like:

For intLoop = 5 to 9
If Len(Trim(varText(intLoop) & "")) > 0 Then
Me.status.Value = varText(intLoop)
Exit For
End If
Next intLoop

Why your code didn't work is that you have 5 If statements, and only 1 End
If. You either need to use 5 End If statements, or else use IfElse instead
of the last 4 If statements:

If Len(Trim(varText(5) & "")) > 0 Then
Me.status.Value = varText(5)
Else
If Len(Trim(varText(6) & "")) > 0 Then
Me.status.Value = varText(6)
Else
If Len(Trim(varText(7) & "")) > 0 Then
Me.status.Value = varText(7)
Else
If Len(Trim(varText(8) & "")) > 0 Then
Me.status.Value = varText(8)
Else
If Len(Trim(varText(9) & "")) > 0 Then
Me.status.Value = varText(9)
End If
End If
End If
End If
End If

or

If Len(Trim(varText(5) & "")) > 0 Then
Me.status.Value = varText(5)
ElseIf Len(Trim(varText(6) & "")) > 0 Then
Me.status.Value = varText(6)
ElseIf Len(Trim(varText(7) & "")) > 0 Then
Me.status.Value = varText(7)
ElseIf Len(Trim(varText(8) & "")) > 0 Then
Me.status.Value = varText(8)
ElseIf Len(Trim(varText(9) & "")) > 0 Then
Me.status.Value = varText(9)
End If

Note that I've indented your statements: I find it's much easier to match up
Ifs and EndIfs that way.

 

[Gary]

Thankyou i have tried to use your code and added one part to it to
assign the line after the vartext that contains something to a
different field, and access is telling me that the vartext sub or
function isn't defined... any ideas?
Thanks,
Gary

Private Sub Command23_Click()
Dim plusoneloop As Integer
For intloop = 6 To 12
plusoneloop = intloop + 1

If Len(Trim(varText(intloop) & "")) > 0 Then
Me.status.Value = varText(intloop)
Me.Date_of_Incorporation.Value = Mid(varText(plusoneloop), 24)
Exit For
End If
Next intloop


End Sub

 

[Douglas J Steele]

You haven't defined varText as an array. In your original code, you did this
via the statement

varText = Split(copieddata, vbCrLf)