By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Dropping Zeros in a crosstab query

kcdoell
100+
P: 230
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?
Dec 26 '07 #1
Share this Question
Share on Google+
10 Replies


jaxjagfan
Expert 100+
P: 254
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.
Dec 26 '07 #2

kcdoell
100+
P: 230
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.
Dec 26 '07 #3

jaxjagfan
Expert 100+
P: 254
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!
Dec 26 '07 #4

kcdoell
100+
P: 230
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
Dec 26 '07 #5

jaxjagfan
Expert 100+
P: 254
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)
Dec 26 '07 #6

kcdoell
100+
P: 230
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.
Jan 3 '08 #7

jaxjagfan
Expert 100+
P: 254
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).
Jan 3 '08 #8

kcdoell
100+
P: 230
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.
Jan 3 '08 #9

jaxjagfan
Expert 100+
P: 254
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")
Jan 3 '08 #10

kcdoell
100+
P: 230
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.
Jan 3 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.