trouble with a user-written function...

[Bryan]

Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String) As String
Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator character. We chose the tilde
because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it to a real database and
I can't
get it to work. I get a "data type mismatch in criteria expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan

 

[Douglas J. Steele]

Why are you adding that extra separator at the end of the string in the
function? That's going to give you back an extraneous field.

And how are you actually using this? Where have you put
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'?

From the message, it sounds as though you're using it as a condition in a
WHERE clause.

Try putting double-quotes around True:

separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = "TRUE"

 

[Bryan]

Hi, Doug!

I put the extra separator on the field so there will always be at least one
separator.
And I am trying to use this as a condition in a "WHERE" clause -
For instance, I might want to select or count records where the 5th field is
equal
to a "CC" or for some other selection and reporting purposes.

There was supposed to be double quotes around "TRUE"

Thanks for trying to help, I'll keep working but am still looking for help
here.

Bryan

Why are you adding that extra separator at the end of the string in the
function? That's going to give you back an extraneous field.

And how are you actually using this? Where have you put
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'?

From the message, it sounds as though you're using it as a condition in a
WHERE clause.

Try putting double-quotes around True:

separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = "TRUE"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String) As String
Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator character. We chose the tilde
because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it to a real database and
I can't
get it to work. I get a "data type mismatch in criteria expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan

 

[Bryan]

Here's the SQL from my query (built in access' native query builder mode,
but this
is what it gives)


SELECT Count(BJ_SaleSumm.EXTRA1) AS CountOfEXTRA1
FROM BJ_SaleSumm
WHERE (((separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~"))="TRUE"));


And here's a typical-looking "EXTRA1" field:

BJ_SaleSumm EXTRA1
CC~2264 5007 8321 52100~0106~~MC~FALSE~andrea smith~N~


I'm trying to get a count of the number of these fields where the 6th
segment is "TRUE"


Bryan

 

[Ted Allen]

Hi Bryan,

I didn't go through the code in detail, but is it
possible that you could be passing null's to the
function? I think you will get an error if you try to
pass null to a string. If so, you could try passing your
field values to variant data types and then use the nz()
function to convert to a string variable if you wish.
Or, you could use the nz() function around your field
name when calling the function to avoid passing nulls.

Not sure if this is your problem, but thought I would
offer the idea.

-Ted Allen

 

[Ted Allen]

P.s. I have a function with a similar purpose that I use
to parse Oracle array fields. Following is the code if
you are interested. In your case you would want to
change the default ASCII character code to whatever the
tilde code is. I had to use character codes because
oracle uses chr(10) to delimit the arrays, and that
cannot be represented via a string. The last optional
parameter will insert the field number that you extract
followed by a hyphen in front of the extracted text.

Following is the function (watch the wrapping):

Public Function ParseArray(InputArrayField, OutputFieldNo
As Integer, Optional DelimiterCharCode As Integer = 10,
Optional InclFieldNo_Y_N As String = "N")

Dim ArrayBreakPos() As Integer, strPrefix As String

On Error GoTo ErrorHandler

If IsNull(InputArrayField) = True Then
ParseArray = Null
Exit Function
End If

If UCase(Left(InclFieldNo_Y_N, 1)) = "Y" Then
strPrefix = OutputFieldNo & " - "
Else
strPrefix = ""
End If

ReDim ArrayBreakPos(OutputFieldNo) As Integer
'Set the Break Position 0 (which is not a real break) as
0 to start searching the string initially
'At the start point of the string (1 character after the
previous break pos)
ArrayBreakPos(0) = 0

For i = 1 To OutputFieldNo
ArrayBreakPos(i) = InStr(ArrayBreakPos(i - 1) + 1,
InputArrayField, Chr(DelimiterCharCode), vbTextCompare)
If ArrayBreakPos(i) = 0 Then
If i < OutputFieldNo Then
ParseArray = Null
Else
ParseArray = strPrefix & Right
(InputArrayField, Len(InputArrayField) - ArrayBreakPos
(OutputFieldNo - 1))
End If
Exit Function
End If
Next

ParseArray = strPrefix & Mid(InputArrayField,
ArrayBreakPos(OutputFieldNo - 1) + 1, ArrayBreakPos
(OutputFieldNo) - ArrayBreakPos(OutputFieldNo - 1) - 1)

Exit Function

ErrorHandler:
ParseArray = "Error - " & Err.Number & " " &
Err.Description

End Function

HTH

-Ted Allen

-----Original Message-----
Hi Bryan,

I didn't go through the code in detail, but is it
possible that you could be passing null's to the
function? I think you will get an error if you try to
pass null to a string. If so, you could try passing your
field values to variant data types and then use the nz()
function to convert to a string variable if you wish.
Or, you could use the nz() function around your field
name when calling the function to avoid passing nulls.

Not sure if this is your problem, but thought I would
offer the idea.

-Ted Allen

-----Original Message-----
Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String) As String
Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator

character.
We chose the tilde

because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it to a real database and
I can't
get it to work. I get a "data type mismatch in

criteria
expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan


.

.

 

[Douglas J. Steele]

No reason for that. Split("ABC", "~") will give you an array with one
element in it.

varList = Split("ABC", "~")

UBound(varList) will be 0, and varList(0) will be ABC.

Ted's suggestion that you might be encountering Nulls might be correct,
although I'd expect a different error message (Invalid use of Null)

BTW, there was no need to post your additional information as another
thread. Putting all the information into a single thread is the preferred
approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Doug!

I put the extra separator on the field so there will always be at least one
separator.
And I am trying to use this as a condition in a "WHERE" clause -
For instance, I might want to select or count records where the 5th field is
equal
to a "CC" or for some other selection and reporting purposes.

There was supposed to be double quotes around "TRUE"

Thanks for trying to help, I'll keep working but am still looking for help
here.

Bryan

Why are you adding that extra separator at the end of the string in the
function? That's going to give you back an extraneous field.

And how are you actually using this? Where have you put
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'?

From the message, it sounds as though you're using it as a condition in a
WHERE clause.

Try putting double-quotes around True:

separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = "TRUE"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String) As String
Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator character. We chose the tilde
because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it to a real

database
and

I can't
get it to work. I get a "data type mismatch in criteria expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan

 

[Bryan]

Bingo!

I thought about this also on the way into work this morning.
I'm a Pascal programmer, not a VB programmer, so I didn't realize
that NULL was handled differently than a "null string".

I changed the parameter type to variant and used the Nz() function
when I assigned it to my tempString variable, and Voila!

Apparently the invalid parameter type error message comes from passing
a NULL to a String variable parameter.

Thanks, Ted and Douglas!

Bryan

(PS, I didn't mean to start a new thread when I added addtl info - I thought
I just
did a reply to the group. Doh!)

Hi Bryan,

I didn't go through the code in detail, but is it
possible that you could be passing null's to the
function? I think you will get an error if you try to
pass null to a string. If so, you could try passing your
field values to variant data types and then use the nz()
function to convert to a string variable if you wish.
Or, you could use the nz() function around your field
name when calling the function to avoid passing nulls.

Not sure if this is your problem, but thought I would
offer the idea.

-Ted Allen

-----Original Message-----
Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String) As String
Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator character. We chose the tilde
because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it to a real database and
I can't
get it to work. I get a "data type mismatch in criteria expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan


.

 

[Ted Allen]

Hi Bryan,

Glad you got it to work.

-Ted Allen

-----Original Message-----
Bingo!

I thought about this also on the way into work this morning.
I'm a Pascal programmer, not a VB programmer, so I didn't realize
that NULL was handled differently than a "null string".

I changed the parameter type to variant and used the Nz () function
when I assigned it to my tempString variable, and Voila!

Apparently the invalid parameter type error message comes from passing
a NULL to a String variable parameter.

Thanks, Ted and Douglas!

Bryan

(PS, I didn't mean to start a new thread when I added addtl info - I thought
I just
did a reply to the group. Doh!)

Hi Bryan,

I didn't go through the code in detail, but is it
possible that you could be passing null's to the
function? I think you will get an error if you try to
pass null to a string. If so, you could try passing your
field values to variant data types and then use the nz ()
function to convert to a string variable if you wish.
Or, you could use the nz() function around your field
name when calling the function to avoid passing nulls.

Not sure if this is your problem, but thought I would
offer the idea.

-Ted Allen

-----Original Message-----
Hi.

I've written a function to help me obtain a certain section
of a field, as separated by the tilde character '~', although
it should work for any character.

Here's the VB code for my access function:

Attribute VB_Name = "vb_library"
Option Compare Database
Option Explicit


Public Function separated_field(inString As String, _
field_no As Integer, _
separator As String)

As
String

Dim list As Variant
Dim tempstr As String
Dim counter As Integer
If field_no < 1 Then field_no = 1

tempstr = Trim(inString) + separator
list = Split(tempstr, separator)

If field_no > UBound(list) Then
separated_field = ""
Else
separated_field = Trim(list(field_no - 1))
End If
End Function



And here's a typical function call -
separated_field(Trim([bj_salesumm].[EXTRA1]),6,"~") = 'TRUE'

What we do to save changing structure a lot in our system is pack the data
into a large field, separated by a separator

character.
We chose the tilde

because
it is rare to non-existent in our natural data.

This worked in testing, but now I'm trying to add it

to
a real database and

I can't
get it to work. I get a "data type mismatch in

criteria
expression" error.

Does anyone have any idea where I'm going wrong here?

Thanks in advance!

Bryan


.

.

 

[Tim Ferguson]

I'm a Pascal programmer, not a VB programmer, so I didn't realize
that NULL was handled differently than a "null string".

Not like a Pascal man to get caught out by weak datatyping... <g>


Tim F