Modification of LEN and LEFT functionality

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I got some help from John Vinson in another thread. I was wondering if I
could get some addtional information.

The query below requires some modification to ensure that the comma of the
last value is removed.

Does anyone know how to modify it. Please see examples below for more
background.

Thanks so much in advance,
Tom



***********

Expr: IIf(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
") Is Not Null,Left(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", "),Len(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", ")-2)))


Below is the query how it pulls the records (I abbreviated "Field" with
"F").

F1 F2 F3 F4
Record #1: 1 2 3
Record #2: 21 22
Record #3: 31 34
Record #4: 100



The result in the expression field are now:

Expr
1, 2, 3,
21, 22,
31, 34
100,

As shown in the example above, the 3rd record is the only record that is
displayed properly (no comma at the end) since it is the only record which
has a value in the Field4. The 1st, 2nd, and 3rd record are followed by a
comma for the last character.

Do you know of a way to drop that "last comma" as well... please keep in
mind that maybe there won't be a value for the 2nd or 3rd record either.
 
Check the number of spaces in your concatenation. It would seem that you have a
comma followed by more than one space. You could try trimming the statement and
then removing the last character which should be a comma.

And Hopefully I get all the left and right parentheses in the correct places.
The basic statement is

IIF(X is Not Null,
Left(Trim(X),
Len(Trim(X))-1))

So substituting

IIF(([Field1] & [Field2] & [Field3] & [Field4]) is Not Null,
Left(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
")),
Len(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ")))-1))

Since we don't need to add the commas and spaces in the criterion statement, we
can trim it slightly.
 
John:

Thanks... that query works for me (for the sample data).

I now translated the expression query for the actual fields I'm using.
This query is super long and the field in the query design does not even
except it.

Is there a way to save this expression "somewhere" else and then call a
function in the design view of the query?

Thanks,
Tom

Here's the lengthy query:

Concatenate:IIf(([C4_CONVERT] & [CG_CONVERT] & DEFPLAN_CONVERT] &
[FCRT_CONVERT] & [HQSPT_CONVERT] & [INTEL_CONVERT]& [JEEA_CONVERT]&
[JET_CONVERT] & [LOG_CONVERT] & [RESOURCES_CONVERT] & [SCPI_CONVERT] &
[C4I_CONVERT] & [CAPABILITIES_CONVERT] & [CGMGMT_CONVERT]
& [IMPLEMENTATION_CONVERT] & [RESLOG_CONVERT] )Is Not
Null,Left(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") &([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") & ([SCPI_CONVERT]+", ") &
([C4I_CONVERT]+", ") &([CAPABILITIES_CONVERT]+", ") & ([CGMGMT_CONVERT]+",
") & ([IMPLEMENTATION_CONVERT]+", ") &
([RESLOG_CONVERT]+","))Len(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") & ([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") &
([SCPI_CONVERT]+", ") & ([C4I_CONVERT]+", ") & ([CAPABILITIES_CONVERT]+", ")
& ([CGMGMT_CONVERT]+", ") & ([IMPLEMENTATION_CONVERT]+", ")
&([RESLOG_CONVERT]+", ")))-1))

--
Thanks,
Tom


John Spencer (MVP) said:
Check the number of spaces in your concatenation. It would seem that you have a
comma followed by more than one space. You could try trimming the statement and
then removing the last character which should be a comma.

And Hopefully I get all the left and right parentheses in the correct places.
The basic statement is

IIF(X is Not Null,
Left(Trim(X),
Len(Trim(X))-1))

So substituting

IIF(([Field1] & [Field2] & [Field3] & [Field4]) is Not Null,
Left(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
")),
Len(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ")))-1))

Since we don't need to add the commas and spaces in the criterion statement, we
can trim it slightly.
I got some help from John Vinson in another thread. I was wondering if I
could get some addtional information.

The query below requires some modification to ensure that the comma of the
last value is removed.

Does anyone know how to modify it. Please see examples below for more
background.

Thanks so much in advance,
Tom

***********

Expr: IIf(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
") Is Not Null,Left(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", "),Len(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", ")-2)))

Below is the query how it pulls the records (I abbreviated "Field" with
"F").

F1 F2 F3 F4
Record #1: 1 2 3
Record #2: 21 22
Record #3: 31 34
Record #4: 100

The result in the expression field are now:

Expr
1, 2, 3,
21, 22,
31, 34
100,

As shown in the example above, the 3rd record is the only record that is
displayed properly (no comma at the end) since it is the only record which
has a value in the Field4. The 1st, 2nd, and 3rd record are followed by a
comma for the last character.

Do you know of a way to drop that "last comma" as well... please keep in
mind that maybe there won't be a value for the 2nd or 3rd record either.
Click to expand...
Click to expand...
 
Ok, now you will need to write a user function in VBA and pass in the values and
have it return what you need. This is a lot longer than your example.

Which version of Access are you using? The following UNTESTED AIRCODE should
work for any version of Access.

Function fJoin(ParamArray vArray())
Dim strReturn As String
Dim iCount As Integer

For iCount = LBound(vArray) To UBound(vArray)
If Len(Trim(vArray(iCount) & vbNullString)) > 0 Then
strReturn = strReturn & vArray(iCount) & ", "
End If
Next iCount
If Len(strReturn) > 2 Then
strReturn = Left(strReturn, Len(strReturn) - 2)
End If
fJoin = strReturn
End Function

In the query you would need to list all your fields.

Concatenated: fJoin([C4_CONVERT],[CG_CONVERT], DEFPLAN_CONVERT], ...
,[RESLOG_CONVERT])

John:

Thanks... that query works for me (for the sample data).

I now translated the expression query for the actual fields I'm using.
This query is super long and the field in the query design does not even
except it.

Is there a way to save this expression "somewhere" else and then call a
function in the design view of the query?

Thanks,
Tom

Here's the lengthy query:

Concatenate:IIf(([C4_CONVERT] & [CG_CONVERT] & DEFPLAN_CONVERT] &
[FCRT_CONVERT] & [HQSPT_CONVERT] & [INTEL_CONVERT]& [JEEA_CONVERT]&
[JET_CONVERT] & [LOG_CONVERT] & [RESOURCES_CONVERT] & [SCPI_CONVERT] &
[C4I_CONVERT] & [CAPABILITIES_CONVERT] & [CGMGMT_CONVERT]
& [IMPLEMENTATION_CONVERT] & [RESLOG_CONVERT] )Is Not
Null,Left(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") &([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") & ([SCPI_CONVERT]+", ") &
([C4I_CONVERT]+", ") &([CAPABILITIES_CONVERT]+", ") & ([CGMGMT_CONVERT]+",
") & ([IMPLEMENTATION_CONVERT]+", ") &
([RESLOG_CONVERT]+","))Len(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") & ([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") &
([SCPI_CONVERT]+", ") & ([C4I_CONVERT]+", ") & ([CAPABILITIES_CONVERT]+", ")
& ([CGMGMT_CONVERT]+", ") & ([IMPLEMENTATION_CONVERT]+", ")
&([RESLOG_CONVERT]+", ")))-1))

--
Thanks,
Tom

John Spencer (MVP) said:
Check the number of spaces in your concatenation. It would seem that you have a
comma followed by more than one space. You could try trimming the statement and
then removing the last character which should be a comma.

And Hopefully I get all the left and right parentheses in the correct places.
The basic statement is

IIF(X is Not Null,
Left(Trim(X),
Len(Trim(X))-1))

So substituting

IIF(([Field1] & [Field2] & [Field3] & [Field4]) is Not Null,
Left(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
")),
Len(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ")))-1))

Since we don't need to add the commas and spaces in the criterion statement, we
can trim it slightly.
I got some help from John Vinson in another thread. I was wondering if I
could get some addtional information.

The query below requires some modification to ensure that the comma of the
last value is removed.

Does anyone know how to modify it. Please see examples below for more
background.

Thanks so much in advance,
Tom

***********

Expr: IIf(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
") Is Not Null,Left(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", "),Len(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") &
([Field4]+", ")-2)))

Below is the query how it pulls the records (I abbreviated "Field" with
"F").

F1 F2 F3 F4
Record #1: 1 2 3
Record #2: 21 22
Record #3: 31 34
Record #4: 100

The result in the expression field are now:

Expr
1, 2, 3,
21, 22,
31, 34
100,

As shown in the example above, the 3rd record is the only record that is
displayed properly (no comma at the end) since it is the only record which
has a value in the Field4. The 1st, 2nd, and 3rd record are followed by a
comma for the last character.

Do you know of a way to drop that "last comma" as well... please keep in
mind that maybe there won't be a value for the 2nd or 3rd record either.
Click to expand...
Click to expand...
Click to expand...
 
John,

that works just fabulously!!! Again, thanks so much!

--
Tom


John Spencer (MVP) said:
Ok, now you will need to write a user function in VBA and pass in the values and
have it return what you need. This is a lot longer than your example.

Which version of Access are you using? The following UNTESTED AIRCODE should
work for any version of Access.

Function fJoin(ParamArray vArray())
Dim strReturn As String
Dim iCount As Integer

For iCount = LBound(vArray) To UBound(vArray)
If Len(Trim(vArray(iCount) & vbNullString)) > 0 Then
strReturn = strReturn & vArray(iCount) & ", "
End If
Next iCount
If Len(strReturn) > 2 Then
strReturn = Left(strReturn, Len(strReturn) - 2)
End If
fJoin = strReturn
End Function

In the query you would need to list all your fields.

Concatenated: fJoin([C4_CONVERT],[CG_CONVERT], DEFPLAN_CONVERT], ...
,[RESLOG_CONVERT])

John:

Thanks... that query works for me (for the sample data).

I now translated the expression query for the actual fields I'm using.
This query is super long and the field in the query design does not even
except it.

Is there a way to save this expression "somewhere" else and then call a
function in the design view of the query?

Thanks,
Tom

Here's the lengthy query:

Concatenate:IIf(([C4_CONVERT] & [CG_CONVERT] & DEFPLAN_CONVERT] &
[FCRT_CONVERT] & [HQSPT_CONVERT] & [INTEL_CONVERT]& [JEEA_CONVERT]&
[JET_CONVERT] & [LOG_CONVERT] & [RESOURCES_CONVERT] & [SCPI_CONVERT] &
[C4I_CONVERT] & [CAPABILITIES_CONVERT] & [CGMGMT_CONVERT]
& [IMPLEMENTATION_CONVERT] & [RESLOG_CONVERT] )Is Not
Null,Left(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") &([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") & ([SCPI_CONVERT]+", ") &
([C4I_CONVERT]+", ") &([CAPABILITIES_CONVERT]+", ") & ([CGMGMT_CONVERT]+",
") & ([IMPLEMENTATION_CONVERT]+", ") &
([RESLOG_CONVERT]+","))Len(Trim(([C4_CONVERT]+", ") & ([CG_CONVERT]+", ") &
([DEFPLAN_CONVERT]+", ") & ([FCRT_CONVERT]+", ") & ([HQSPT_CONVERT]+", ") &
([INTEL_CONVERT]+", ") & ([JEEA_CONVERT]+", ") & ([JET_CONVERT]+", ") &
([LOG_CONVERT]+", ") & ([RESOURCES_CONVERT]+", ") &
([SCPI_CONVERT]+", ") & ([C4I_CONVERT]+", ") & ([CAPABILITIES_CONVERT]+", ")
& ([CGMGMT_CONVERT]+", ") & ([IMPLEMENTATION_CONVERT]+", ")
&([RESLOG_CONVERT]+", ")))-1))

--
Thanks,
Tom

John Spencer (MVP) said:
Check the number of spaces in your concatenation. It would seem that
Click to expand...
you
have a
comma followed by more than one space. You could try trimming the statement and
then removing the last character which should be a comma.

And Hopefully I get all the left and right parentheses in the correct places.
The basic statement is

IIF(X is Not Null,
Left(Trim(X),
Len(Trim(X))-1))

So substituting

IIF(([Field1] & [Field2] & [Field3] & [Field4]) is Not Null,
Left(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
")),
Len(Trim(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+", ")))-1))

Since we don't need to add the commas and spaces in the criterion statement, we
can trim it slightly.

Tom wrote:

I got some help from John Vinson in another thread. I was wondering if I
could get some addtional information.

The query below requires some modification to ensure that the comma
Click to expand...
of
the
last value is removed.

Does anyone know how to modify it. Please see examples below for more
background.

Thanks so much in advance,
Tom

***********

Expr: IIf(([Field1]+", ") & ([Field2]+", ") & ([Field3]+", ") & ([Field4]+",
") Is Not Null,Left(([Field1]+", ") & ([Field2]+", ") & ([Field3]+",
Click to expand...
")
&
([Field4]+", "),Len(([Field1]+", ") & ([Field2]+", ") & ([Field3]+",
Click to expand...
")
&
([Field4]+", ")-2)))

Below is the query how it pulls the records (I abbreviated "Field" with
"F").

F1 F2 F3 F4
Record #1: 1 2 3
Record #2: 21 22
Record #3: 31 34
Record #4: 100

The result in the expression field are now:

Expr
1, 2, 3,
21, 22,
31, 34
100,

As shown in the example above, the 3rd record is the only record that is
displayed properly (no comma at the end) since it is the only record which
has a value in the Field4. The 1st, 2nd, and 3rd record are
Click to expand...
followed by
a
comma for the last character.

Do you know of a way to drop that "last comma" as well... please keep in
mind that maybe there won't be a value for the 2nd or 3rd record
Click to expand...
Click to expand...
Click to expand...
either.
 
Back
Top