If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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 |
#2
|
|||
|
|||
Crosstab query dynamic columns ala 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. -- Duane Hookom Microsoft Access MVP "javablood" wrote: 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 |
#3
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#4
|
|||
|
|||
Crosstab query dynamic columns ala 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#5
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#6
|
|||
|
|||
Crosstab query dynamic columns ala 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#7
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#8
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
Your dates should actually be columns with names like [A], [b], 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 "Duane Hookom" wrote: 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 "javablood" wrote: 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 |
#9
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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 Duane Hookom wrote: Your dates should actually be columns with names like [A], [b], 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". |
#10
|
|||
|
|||
Crosstab query dynamic columns ala Duane Hookom
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([b])),Val([b]),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 "John Spencer" wrote: 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 Duane Hookom wrote: Your dates should actually be columns with names like [A], [b], 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". |
|
Thread Tools | |
Display Modes | |
|
|