Dropping Zeros in a crosstab query

[kcdoell]

I have a listbox on a form that uses my crosstab query to display the query results. For the fields that display numbers, the numbers with a zero in the last digit are being dropped. Example $88.80 is displayes as $88.8. I have the field set as a numeric/double. The interesting thing is that the raw data file has the zeros on the end. I am thinking I need to resolve the formating issue in the query itself.

Does anybody have any ideas?

[jaxjagfan]

I have a listbox on a form that uses my crosstab query to display the query results. For the fields that display numbers, the numbers with a zero in the last digit are being dropped. Example $88.80 is displayes as $88.8. I have the field set as a numeric/double. The interesting thing is that the raw data file has the zeros on the end. I am thinking I need to resolve the formating issue in the query itself.

Does anybody have any ideas?

For those columns in your listbox you want to display as currency - CCur(yourcolumnname) and it should display $88.80.

[kcdoell]

For those columns in your listbox you want to display as currency - CCur(yourcolumnname) and it should display $88.80.

Do I have to go into the properties of the listbox to do this? If not, how would I accomplish this? Thanks a lot

Keith.

[jaxjagfan]

Do I have to go into the properties of the listbox to do this? If not, how would I accomplish this? Thanks a lot

Keith.

In the Rowsource property of the listbox edit the the rowsource similar to the following:

SELECT qryYourCrosstab.Column1, qryYourCrosstab.Column2, CCur(qryYourCrosstab.Column3) AS YourCurrencyCol FROM qryYourCrosstab;

Hope this helps!

[kcdoell]

I tried the following:

SELECT qry1DataTable_Crosstab_Ordered.Column1, qry1DataTable_Crosstab_Ordered.Column2, CCur(qry1DataTable_Crosstab_Ordered.Column3) AS DetailAmt FROM qry1DataTable_Crosstab_Ordered;

but it did not work. The way I have it set up now I have "row/source type" set to table/Query and "row source" to the name of the query "1DataTable_Crosstab_Ordered". I tried to manipulate your suggestion but then the list box does not pull the query in.... and displays blank......

Keith

[jaxjagfan]

I tried the following:

SELECT qry1DataTable_Crosstab_Ordered.Column1, qry1DataTable_Crosstab_Ordered.Column2, CCur(qry1DataTable_Crosstab_Ordered.Column3) AS DetailAmt FROM qry1DataTable_Crosstab_Ordered;

but it did not work. The way I have it set up now I have "row/source type" set to table/Query and "row source" to the name of the query "1DataTable_Crosstab_Ordered". I tried to manipulate your suggestion but then the list box does not pull the query in.... and displays blank......

Keith

Replace "1DataTable_Crosstab_Ordered" with your SELECT statement. Replace the Column1,Column2,etc with the names from your crosstab query columns (Unless those are the names you gave the columns. I don't know how many columns you are trying to display here so you may need to include more or less columns) Yes - rowsource type should be set to table/query.

In the RowSource property you can bring up the QBE (query designer) by clicking on the "..." and selecting your crosstab query along with the desired fields (columns). On the field you wish to display as currency:

DetailAmt:CCur(qry1DataTable_Crosstab_Ordered.thef ieldname)

[kcdoell]

So far I still can not get the zero to display. I am thinking that it has something to do with the way my tables may be set up..... I have a table (My main table) that has a text field called “DetailDesc”. That field is linked to another field “EorDName” on a separate table. The user can add various “EorDName”, so currently there are about 20 different EorDName to choose from on a drop down list. It is these EorDName fields that are being displayed on my listbox that are not showing the last zero. Could that be the cause of the problem?? I noticed that my calculated field (Net Salary) on my query that is not tied to DetailDesc does display the last zero.....

Here is the crosstab query in SQL view:

PARAMETERS [Forms].[ChooseEmply].[ChooseEmployee] Text ( 255 ), [Forms].[ChooseEmply].[ChoosePayrollYear] Text ( 255 );
TRANSFORM CCur(Sum(DataTable.DetailAmt)) AS SumOfDetailAmt
SELECT DataTable.Month, CCur(Sum(DataTable.DetailAmt)) AS [Net Salary]
FROM DataTable
WHERE (((DataTable.EmployeeID)=Forms.ChooseEmply.ChooseE mployee) And ((Right([Month],2)) Like Right(Forms.ChooseEmply.ChoosePayrollYear,2)))
GROUP BY DataTable.Month
PIVOT DataTable.DetailDesc;

Best regards,

Keith.

[jaxjagfan]

So far I still can not get the zero to display. I am thinking that it has something to do with the way my tables may be set up..... I have a table (My main table) that has a text field called “DetailDesc”. That field is linked to another field “EorDName” on a separate table. The user can add various “EorDName”, so currently there are about 20 different EorDName to choose from on a drop down list. It is these EorDName fields that are being displayed on my listbox that are not showing the last zero. Could that be the cause of the problem?? I noticed that my calculated field (Net Salary) on my query that is not tied to DetailDesc does display the last zero.....

Here is the crosstab query in SQL view:

PARAMETERS [Forms].[ChooseEmply].[ChooseEmployee] Text ( 255 ), [Forms].[ChooseEmply].[ChoosePayrollYear] Text ( 255 );
TRANSFORM CCur(Sum(DataTable.DetailAmt)) AS SumOfDetailAmt
SELECT DataTable.Month, CCur(Sum(DataTable.DetailAmt)) AS [Net Salary]
FROM DataTable
WHERE (((DataTable.EmployeeID)=Forms.ChooseEmply.ChooseE mployee) And ((Right([Month],2)) Like Right(Forms.ChooseEmply.ChoosePayrollYear,2)))
GROUP BY DataTable.Month
PIVOT DataTable.DetailDesc;

Best regards,

Keith.

1. Drop Down List (ComboBox) and List box (ListBox) are 2 different types of controls. Combo allows user to pick one value at a time or enter their own depending on settings. ListBox allows user to pick one or more values depending on settings. I think you are using a drop list (combobox) to display a list of values (EorDName) to pick from or allowing users to enter a value and then filtering or using that value to navigate your records.
2. According to your description of the linking you are doing EorDName is a Text datatype that your users enter into a table.
3. Make a query with your parameters or criteria and then make a crosstab_query based on the parameter query.
4. Is it EorDName from your droplist that is not displaying the correct format or DetailDesc in the crosstab?
5 How are you displaying the results of the crosstab - in a datagrid, listbox, continuous form, etc?
6. How are you using this value when entered or selected?

The rowsource of your combo could be:

SELECT DataTable.DetailDesc
FROM DataTable
Group By DetailDesc
Order By DetailDesc;

This would give the user a unique list of the DetailDesc values in ascending order to pick from without having to maintain another table (If that is the purpose of the table EorDName is stored in).

[kcdoell]

Happy New Year!

On my main form is a listbox which is displaying the crosstab query result (Data tab on the properties of the listbox). To the end user it looks like a table on the form. The number of columns and rows are dependent on the crosstab query result. The User can add various EorDNames (ComboBox) to a record which are linked to DetailDesc hence why some records will have more columns than others when displayed on the listbox. The fields DetailDesc (Text) and DetailAmt (Currency) are on the same table, so any specific EorDName will have a DetailAmt associated with it. Those are the numbers in which I am seeing that if the last digit is a zero the listbox will not display it even though the zeros are displayed in the crosstab query and the raw datafile…..

Hope this all makes sense….

Thanks,

Keith.

[jaxjagfan]

I have a listbox (lstDetails) and a Combobox (cboEorDName)

In design view of the form I selected the lstDetails, view Properties, select Data tab - RowSourceType = Table/Query
Now in RowSource I select the button with the (...) which opens the query builder to build a row source. I select my crosstab query and then select the fields I want displayed.
In the criteria section of the Details field I put [cboEorDName] and in the DetailAmt column where I want a currency value to be displayed I edit it to say Amt: CCur(DetailAmt)

I then close (not save) the query - when prompted to update properties select "Yes". This will put the SQL equivalent of a query based on the Crosstab query in lstDetails row source.

On the AfterUpdate Property of cboEorDName I would put the following code:

Me.lstDetails.Requery

In the event my Amt column still isn't displaying correctly I would consider changing it to Amt: Format(DetailAmt,"Currency")

[kcdoell]

Solved in the end, I conducted several experiments rebuilding the query, rebuilding the list box from scratch and making sure my DetailAmt was set to currency....... In the end going through that process all over again solved the problem. It ended up just being a conflict with the DetailAmt not being set to Currency as well as me cleaning up my crosstab query....

Thanks for your input and help

Keith.