Crosstab query dynamic columns ala Duane Hookom

[javablood]

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[Duane Hookom]

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

 

[javablood]

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[Duane Hookom]

You location might be like my EmployeeID but you don't want different dates
for every location. You want the same dates for every location. This suggests
you need to ignore the location in the append query and apply A-... aliases
for each unique date.

This is part of the current code with some lines commented out with 's
'Do While !employeeID = lngEmpID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
'Loop

I'm not entirely sure this is the solution but it should get you closer.

Do you expect more dates than you have room for columns?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[javablood]

Duane,

I see now what you meant about ignoring the location. I am fine with column
space. I tried this but did not get column dates for all the locations and
the dates were in reverse order!? I will work on this a liitle more but I am
not too worried about getting this aspect of the report to work because I
will explain the empty space in the report footer. However, when I try to
sum the columns (using val) for each location/date, I get “Data type mismatch
in criteria expression†when there is no data. The sum works fine if there
are data for all the locations, e.g., each location has an “Aâ€, but if only
one location has a “B†then the error appears. I have tried iserror,
hasdata, nz, etc. to no avail. Do you have any idea how I may make this work?

Thanks for your help and that sample database. BTW, did you submit the
article about dynamic column procedure?

thanks,

--
javablood

You location might be like my EmployeeID but you don't want different dates
for every location. You want the same dates for every location. This suggests
you need to ignore the location in the append query and apply A-... aliases
for each unique date.

This is part of the current code with some lines commented out with 's
'Do While !employeeID = lngEmpID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
'Loop

I'm not entirely sure this is the solution but it should get you closer.

Do you expect more dates than you have room for columns?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP


:

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[Duane Hookom]

You may need to change the crosstab query vlaue like:
TRANSFORM val(NzSum(tblSales.SaleAmt),0)) AS SumOfSaleAmt

If you can't figure this out, reply back with your full SQL view of the
crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane,

I see now what you meant about ignoring the location. I am fine with column
space. I tried this but did not get column dates for all the locations and
the dates were in reverse order!? I will work on this a liitle more but I am
not too worried about getting this aspect of the report to work because I
will explain the empty space in the report footer. However, when I try to
sum the columns (using val) for each location/date, I get “Data type mismatch
in criteria expression†when there is no data. The sum works fine if there
are data for all the locations, e.g., each location has an “Aâ€, but if only
one location has a “B†then the error appears. I have tried iserror,
hasdata, nz, etc. to no avail. Do you have any idea how I may make this work?

Thanks for your help and that sample database. BTW, did you submit the
article about dynamic column procedure?

thanks,

--
javablood

You location might be like my EmployeeID but you don't want different dates
for every location. You want the same dates for every location. This suggests
you need to ignore the location in the append query and apply A-... aliases
for each unique date.

This is part of the current code with some lines commented out with 's
'Do While !employeeID = lngEmpID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
'Loop

I'm not entirely sure this is the solution but it should get you closer.

Do you expect more dates than you have room for columns?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood


:

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP


:

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[javablood]

Duane,

I was trying to sum in the footer. So I put
Sum(Nz(val(tblNWBAtemp.RESULT),0)) in the SQL and it worked. Using
val(Nz(Sum(tblNWBAtemp.RESULT),0)) resulted in a mismatch. However, two
problems:

1. it provides the Sum or Results for all the dates (A, B, etc.) and I need
separate sums for each date; and

2. it sums the result even when the text contains text, e.g., if Result =
25U then it should be 0. I tried IIf([Result] Like
"*U*",Sum(Nz(Val([tblNWBAtemp].[RESULT]),0)),0) but get error " You tried to
execute a query that does not include the specified expression . . as part
of an aggregate function."

I wonder is it is better to try and do the sum in the footer but I need to
get past when the column is a String or NULL.

If I have not said so already, thanks for your help. I am learning a bunch.

Thanks,
--
javablood

You may need to change the crosstab query vlaue like:
TRANSFORM val(NzSum(tblSales.SaleAmt),0)) AS SumOfSaleAmt

If you can't figure this out, reply back with your full SQL view of the
crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane,

I see now what you meant about ignoring the location. I am fine with column
space. I tried this but did not get column dates for all the locations and
the dates were in reverse order!? I will work on this a liitle more but I am
not too worried about getting this aspect of the report to work because I
will explain the empty space in the report footer. However, when I try to
sum the columns (using val) for each location/date, I get “Data type mismatch
in criteria expression†when there is no data. The sum works fine if there
are data for all the locations, e.g., each location has an “Aâ€, but if only
one location has a “B†then the error appears. I have tried iserror,
hasdata, nz, etc. to no avail. Do you have any idea how I may make this work?

Thanks for your help and that sample database. BTW, did you submit the
article about dynamic column procedure?

thanks,

--
javablood

You location might be like my EmployeeID but you don't want different dates
for every location. You want the same dates for every location. This suggests
you need to ignore the location in the append query and apply A-... aliases
for each unique date.

This is part of the current code with some lines commented out with 's
'Do While !employeeID = lngEmpID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
'Loop

I'm not entirely sure this is the solution but it should get you closer.

Do you expect more dates than you have room for columns?
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood


:

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP


:

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[Duane Hookom]

Your dates should actually be columns with names like [A], , etc. Are you
unable to sum these columns in group or report footer sections? Did you try
use something like:
=Sum(Val(Nz([A],0)))

I'm not understanding what you want to do with Result = "25U".
--
Duane Hookom
Microsoft Access MVP

Duane,

I was trying to sum in the footer. So I put
Sum(Nz(val(tblNWBAtemp.RESULT),0)) in the SQL and it worked. Using
val(Nz(Sum(tblNWBAtemp.RESULT),0)) resulted in a mismatch. However, two
problems:

1. it provides the Sum or Results for all the dates (A, B, etc.) and I need
separate sums for each date; and

2. it sums the result even when the text contains text, e.g., if Result =
25U then it should be 0. I tried IIf([Result] Like
"*U*",Sum(Nz(Val([tblNWBAtemp].[RESULT]),0)),0) but get error " You tried to
execute a query that does not include the specified expression . . as part
of an aggregate function."

I wonder is it is better to try and do the sum in the footer but I need to
get past when the column is a String or NULL.

If I have not said so already, thanks for your help. I am learning a bunch.

Thanks,
--
javablood

You may need to change the crosstab query vlaue like:
TRANSFORM val(NzSum(tblSales.SaleAmt),0)) AS SumOfSaleAmt

If you can't figure this out, reply back with your full SQL view of the
crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane,

I see now what you meant about ignoring the location. I am fine with column
space. I tried this but did not get column dates for all the locations and
the dates were in reverse order!? I will work on this a liitle more but I am
not too worried about getting this aspect of the report to work because I
will explain the empty space in the report footer. However, when I try to
sum the columns (using val) for each location/date, I get “Data type mismatch
in criteria expression†when there is no data. The sum works fine if there
are data for all the locations, e.g., each location has an “Aâ€, but if only
one location has a “B†then the error appears. I have tried iserror,
hasdata, nz, etc. to no avail. Do you have any idea how I may make this work?

Thanks for your help and that sample database. BTW, did you submit the
article about dynamic column procedure?

thanks,

--
javablood


:

You location might be like my EmployeeID but you don't want different dates
for every location. You want the same dates for every location. This suggests
you need to ignore the location in the append query and apply A-... aliases
for each unique date.

This is part of the current code with some lines commented out with 's
'Do While !employeeID = lngEmpID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
'Loop

I'm not entirely sure this is the solution but it should get you closer.

Do you expect more dates than you have room for columns?
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks for the input. So I understand, I can remove the Location from the
append query but still group on Location in the report correct? I am not
sure how that would work because my Location is analogous to your EmployeeID
and my Date is analogous to your CustomerID. And even now I realize that I
do not get a Date at the head of each column when the Location changes as you
do when your EmployeeID changes even when the data go onto the next page. Am
I not setting something correctly?

I do not know how to modify the function code to ignore changes in the
EmployeeID/Location. Is this difficult?

Sorry if double posted but I keep getting “Service Temporarily Unavailableâ€.

--
javablood


:

Looking at the sample download, you would need to first change the append
query qappEmpCust to remove your Location field. This should create just
unique Dates. You would also need to modify the code in the
UpdateEmpCustAlias function to ignore changes inthe EmployeeID/Location.

Your alias table would end up with each date being assigned a letter from A
to whatever. If you have more columns than will fit across the page, the
lettering begins back at A with the Level incremented by 1.

--
Duane Hookom
Microsoft Access MVP


:

I have used Duane Hookom's crosstab report samples at
www.RogersAccessLibrary.com for dynamic column headings to get one of my
reports that has different dates in the columns working. Well it does, work.
Thanks Duane. Now, I wonder if there is a way to have the columns with the
same date line up. For example, the report looks like:

Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date2
P1 v1
P2 v2
etc.

What I would like is:
Location1 Date1 Date 2
P1 v1 v1
P2 v2 v2
etc.

Location2 Date1 Date2
P1 ND v1
P2 ND v2
etc.

Where the Ps are chemicals, vs are concentrations, and ND is no data.
I have tried =IIf(IsNull([A]),"NS",[A]), where A is first column data and
this works to a point. However, if the first Location does not have data for
all the dates, then the subsequent date columns for the remaining Locations
will not line up.

Therefore, is there a way to have all the available dates across the columns
and then the body of the report will either be vs or NDs for the Locations
depending on available data?

Thanks for any help provided!

 

[John Spencer]

Try
=Sum(IIF(IsNumeric([Result],Val([Result]),Null))

That should sum only results that can be interpreted as numbers. So Nulls are
not numeric and "25U" is not numeric and "" is not numeric and therefore none
of them get summed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[javablood]

Duane & John,

I was using
=Sum(IIf([A] Like "*U*",0,Val([A])))

in the footer to sum the columns (A, B, etc.) and this works. I need to
differentiate between the rows that have “U†because I do not want the value
of the text if it has a “Uâ€. However, if all the rows have a “Uâ€, i.e.,
there is data in a column, I want to show a zero. So now I tried a variation
of John’s expression:

=Sum(IIf((IsNumeric()),Val(),0))

and this works except that I get a zero even if there is not data for that
column, i.e., NULL. Is there a way to have my zeros and blanks too?

Thanks,

--
javablood

Try
=Sum(IIF(IsNumeric([Result],Val([Result]),Null))

That should sum only results that can be interpreted as numbers. So Nulls are
not numeric and "25U" is not numeric and "" is not numeric and therefore none
of them get summed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Your dates should actually be columns with names like [A], , etc. Are you
unable to sum these columns in group or report footer sections? Did you try
use something like:
=Sum(Val(Nz([A],0)))

I'm not understanding what you want to do with Result = "25U".

 

[javablood]

Got it!!! I used a combination of both of your suggestions:

=IIf(Nz([A]),Sum(IIf((IsNumeric([A])),Val([A]),0)))

and it does what I need. Thanks so much!!!
--
javablood

Try
=Sum(IIF(IsNumeric([Result],Val([Result]),Null))

That should sum only results that can be interpreted as numbers. So Nulls are
not numeric and "25U" is not numeric and "" is not numeric and therefore none
of them get summed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Your dates should actually be columns with names like [A], , etc. Are you
unable to sum these columns in group or report footer sections? Did you try
use something like:
=Sum(Val(Nz([A],0)))

I'm not understanding what you want to do with Result = "25U".