Left,Mid and right function

[mohsin via AccessMonster.com]

Hi there

I have this field condition and need to used left mid and right function to
split the values, can you help me please;

Fld A
GTTKN141&GTTKN142&GTTKN151&GTTKN152&GTTKN171&GTTKN172

I need to split all those "text" without &, this is my queries, as a start;

Left([A],8) AS CGR0, for GTTKN141
Mid([A],10,8) AS CGR1, for GTTKN142
Mid([A],19,8) AS CGR2, for GTTKN151 but zero value.

-tq

 

[Guest]

Mohsin,

The easiest option would be the Access VBA Split function which returns an
array of values so for example:

ArrayA=Split([A],"&")

ArrayA will be an array containing all the values which have been separated

Hope this helps
Mark

 

[mohsin via AccessMonster.com]

I got "undefined function 'split' in expression", something wrong with my
access?!! .. i'm using 2003 version.

Mohsin,

The easiest option would be the Access VBA Split function which returns an
array of values so for example:

ArrayA=Split([A],"&")

ArrayA will be an array containing all the values which have been separated

Hope this helps
Mark

Hi there

[quoted text clipped - 11 lines]

-tq

 

[Douglas J. Steele]

Check that your References collection is okay.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, go into the VB Editor. Select
Tools | References from the menu bar. Examine all of the selected
references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I got "undefined function 'split' in expression", something wrong with my
access?!! .. i'm using 2003 version.

Mohsin,

The easiest option would be the Access VBA Split function which returns an
array of values so for example:

ArrayA=Split([A],"&")

ArrayA will be an array containing all the values which have been
separated

Hope this helps
Mark

Hi there

[quoted text clipped - 11 lines]

-tq

--
Cheers
Mohsin

Message posted via AccessMonster.com

 

[Guest]

Ok,

Here is a sample function which demonstrates the Split function, just
replace strField with the name you use.

Function SplitField()
Dim strField As String
Dim arrValues

strField = "GTTKN141&GTTKN142&GTTKN151&GTTKN152&GTTKN171&GTTKN172"
arrValues = Split(strField, "&")

For byloop = 0 To UBound(arrValues)
MsgBox arrValues(byloop)
Next

End Function

I got "undefined function 'split' in expression", something wrong with my
access?!! .. i'm using 2003 version.

Mohsin,

The easiest option would be the Access VBA Split function which returns an
array of values so for example:

ArrayA=Split([A],"&")

ArrayA will be an array containing all the values which have been separated

Hope this helps
Mark

Hi there

[quoted text clipped - 11 lines]

-tq

--
Cheers
Mohsin

Message posted via AccessMonster.com

 

[Douglas J. Steele]

No offense, Mark, but that's not a very useful example.

First of all, the value to be parsed should be passed as an argument to the
function. Second, using MsgBox doesn't help in a query.

You'd want multiple functions:

Function GetField(InputField As Variant, FieldPosition As Integer) As String
Dim arrValues As Value

If IsNull(InputField) Then
GetField = vbNullString
Else
arrValue = Split(InputField, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

You could then use that function in the query along the lines of:

SELECT GetField([MyBigString], 0) As FirstPartOfField,
GetField([MyBigString], 1) As SecondPartOfField,
GetField([MyBigString], 2) As ThirdPartOfField
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok,

Here is a sample function which demonstrates the Split function, just
replace strField with the name you use.

Function SplitField()
Dim strField As String
Dim arrValues

strField = "GTTKN141&GTTKN142&GTTKN151&GTTKN152&GTTKN171&GTTKN172"
arrValues = Split(strField, "&")

For byloop = 0 To UBound(arrValues)
MsgBox arrValues(byloop)
Next

End Function

I got "undefined function 'split' in expression", something wrong with
my
access?!! .. i'm using 2003 version.

Mohsin,

The easiest option would be the Access VBA Split function which returns
an
array of values so for example:

ArrayA=Split([A],"&")

ArrayA will be an array containing all the values which have been
separated

Hope this helps
Mark

Hi there

[quoted text clipped - 11 lines]

-tq

--
Cheers
Mohsin

Message posted via AccessMonster.com

 

[mohsin via AccessMonster.com]

Hi Douglas & Mark

Thankyou very much indeed for your support,
After tried from what douglas proposed, i got an error from VBA editor
"Compile-error:User-defined not defined" which refering to arrValues, (as the
yellow line).
It's refering to my tool>reference again?.. I'm not sure

Function GetField(CGRName As Variant, FieldPosition As Integer) As String
Dim arrValues As Value

If IsNull(CGRName) Then
GetField = vbNullString
Else
arrValue = Split(CGRName, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

###########################################################
SELECT GetField(qryRouting.[CGRName], 0) As CGR0,
GetField(qryRouting.[CGRName], 1) As CGR1,
GetField(qryRouting.[CGRName], 2) As CGR2,
GetField(qryRouting.[CGRName], 3) As CGR3,
GetField(qryRouting.[CGRName], 4) As CGR4
FROM qryRouting;

No offense, Mark, but that's not a very useful example.

First of all, the value to be parsed should be passed as an argument to the
function. Second, using MsgBox doesn't help in a query.

You'd want multiple functions:

Function GetField(InputField As Variant, FieldPosition As Integer) As String
Dim arrValues As Value

If IsNull(InputField) Then
GetField = vbNullString
Else
arrValue = Split(InputField, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

You could then use that function in the query along the lines of:

SELECT GetField([MyBigString], 0) As FirstPartOfField,
GetField([MyBigString], 1) As SecondPartOfField,
GetField([MyBigString], 2) As ThirdPartOfField
FROM MyTable

Ok,

[quoted text clipped - 37 lines]

 

[mohsin via AccessMonster.com]

This is my Available references on the VBA tools;

- Visual Basic For Applications
- Microsoft Access 11.0 Object library
- Microsoft Active X Data objects 2.1 library
- Microsoft DAo 3.6 object library
- OLE function
- Microsoft ADO Ext. 2.8 for DDL and security

Hi Douglas & Mark

Thankyou very much indeed for your support,
After tried from what douglas proposed, i got an error from VBA editor
"Compile-error:User-defined not defined" which refering to arrValues, (as the
yellow line).
It's refering to my tool>reference again?.. I'm not sure

Function GetField(CGRName As Variant, FieldPosition As Integer) As String
Dim arrValues As Value

If IsNull(CGRName) Then
GetField = vbNullString
Else
arrValue = Split(CGRName, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

###########################################################
SELECT GetField(qryRouting.[CGRName], 0) As CGR0,
GetField(qryRouting.[CGRName], 1) As CGR1,
GetField(qryRouting.[CGRName], 2) As CGR2,
GetField(qryRouting.[CGRName], 3) As CGR3,
GetField(qryRouting.[CGRName], 4) As CGR4
FROM qryRouting;

No offense, Mark, but that's not a very useful example.

[quoted text clipped - 31 lines]

 

[John W. Vinson]

Dim arrValues As Value

That should be

Dim arrValues As Variant

It's objecting to the nonexistant datatype "value".

John W. Vinson [MVP]

 

[Douglas J. Steele]

Sorry about that: my typo.

As John correctly identified, it should be

Dim arrValues As Variant


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas & Mark

Thankyou very much indeed for your support,
After tried from what douglas proposed, i got an error from VBA editor
"Compile-error:User-defined not defined" which refering to arrValues, (as
the
yellow line).
It's refering to my tool>reference again?.. I'm not sure

Function GetField(CGRName As Variant, FieldPosition As Integer) As String
Dim arrValues As Value

If IsNull(CGRName) Then
GetField = vbNullString
Else
arrValue = Split(CGRName, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

###########################################################
SELECT GetField(qryRouting.[CGRName], 0) As CGR0,
GetField(qryRouting.[CGRName], 1) As CGR1,
GetField(qryRouting.[CGRName], 2) As CGR2,
GetField(qryRouting.[CGRName], 3) As CGR3,
GetField(qryRouting.[CGRName], 4) As CGR4
FROM qryRouting;

No offense, Mark, but that's not a very useful example.

First of all, the value to be parsed should be passed as an argument to
the
function. Second, using MsgBox doesn't help in a query.

You'd want multiple functions:

Function GetField(InputField As Variant, FieldPosition As Integer) As
String
Dim arrValues As Value

If IsNull(InputField) Then
GetField = vbNullString
Else
arrValue = Split(InputField, "&")
If UBound(arrValue) >= FieldPosition Then
GetField = arrValue(FieldPosition)
Else
GetField = vbNullString
End If
End If

Exit Function

You could then use that function in the query along the lines of:

SELECT GetField([MyBigString], 0) As FirstPartOfField,
GetField([MyBigString], 1) As SecondPartOfField,
GetField([MyBigString], 2) As ThirdPartOfField
FROM MyTable

Ok,

[quoted text clipped - 37 lines]

-tq

--
Cheers
Mohsin

Message posted via AccessMonster.com

 

[mohsin via AccessMonster.com]

Douglas

Thank you very much, Indeed..
Is work,,

Sorry about that: my typo.

As John correctly identified, it should be

Dim arrValues As Variant

Hi Douglas & Mark

[quoted text clipped - 66 lines]