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

Report that does not display format properly (Based on Crosstab query).....

kcdoell
100+
P: 230
Hello:

I have a report that is based on the following crosstab query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProduct.ProductName, QryProd_Bud.GWP_BUD, Nz([Week 1],0) AS Week_1, Nz([Week 2],0) AS Week_2, Nz([Week 3],0) AS Week_3, Nz([Week 4],0) AS Week_4, Nz([Week 5],0) AS Week_5, QryProd_Prior.GWP_PRI
  2. FROM ((tblProduct LEFT JOIN ctqQueryP_G ON tblProduct.ProductID = ctqQueryP_G.ProductIDFK) LEFT JOIN QryProd_Bud ON tblProduct.ProductID = QryProd_Bud.ProductIDFK) LEFT JOIN QryProd_Prior ON tblProduct.ProductID = QryProd_Prior.ProductIDFK;
My problem is that when the report displays my [GWP_PRI] or [GWP_BUD] they are not displaying in the standard format. For example the number should be 1,255 but is displaying 1255.

I have checked the table where [GWP] resides ([GWP_PRI] & [GWP_BUD] are based on this field) and I have it set to Number, Long Integer, Standard.

I have also checked the properties within the report, [GWP_PRI] & [GWP_BUD],and the properties format is set to "Standard". None of my other reports (they are not crosstab queries) have this issue with this same field [GWP].

Do crosstab queries do something funky to the format properties or am I missing something here? Any ideas would be helpful.

Thanks,

Keith.
May 9 '08 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
Hello:

I have a report that is based on the following crosstab query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProduct.ProductName, QryProd_Bud.GWP_BUD, Nz([Week 1],0) AS Week_1, Nz([Week 2],0) AS Week_2, Nz([Week 3],0) AS Week_3, Nz([Week 4],0) AS Week_4, Nz([Week 5],0) AS Week_5, QryProd_Prior.GWP_PRI
  2. FROM ((tblProduct LEFT JOIN ctqQueryP_G ON tblProduct.ProductID = ctqQueryP_G.ProductIDFK) LEFT JOIN QryProd_Bud ON tblProduct.ProductID = QryProd_Bud.ProductIDFK) LEFT JOIN QryProd_Prior ON tblProduct.ProductID = QryProd_Prior.ProductIDFK;
My problem is that when the report displays my [GWP_PRI] or [GWP_BUD] they are not displaying in the standard format. For example the number should be 1,255 but is displaying 1255.

I have checked the table where [GWP] resides ([GWP_PRI] & [GWP_BUD] are based on this field) and I have it set to Number, Long Integer, Standard.

I have also checked the properties within the report, [GWP_PRI] & [GWP_BUD],and the properties format is set to "Standard". None of my other reports (they are not crosstab queries) have this issue with this same field [GWP].

Do crosstab queries do something funky to the format properties or am I missing something here? Any ideas would be helpful.

Thanks,

Keith.
Standard will follow the settings you've specified on your PC in the Setting/ControlPanel application "Regional and Language options".
When you want to set it in the query it's best to use a Format() function, thus the format is explicit visible, although the format of the graphical query editor wil work, I hate it because you don't see it when opening the query in design mode.

Nic;o)
May 9 '08 #2

kcdoell
100+
P: 230
Hello:

So, I would do this modification in the property settings of the contol on the report itself? Something like "#,##0.00"

Keith.
May 9 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Yep, for a report and form you can use the Format property, in a query a Format() statement.

Just give it a try :-)

Nic;o)
May 9 '08 #4

kcdoell
100+
P: 230
Yep, for a report and form you can use the Format property, in a query a Format() statement.

Just give it a try :-)

Nic;o)

Okay I tried to input it into the field on the report and it did not change the number format.

I never formatted within a query expresion before so I am hoping you can guide me through it. I did some research but could not find any examples. Below is one of my expressions in my query:

Expand|Select|Wrap|Line Numbers
  1. Week_1: Nz([Week 1],0)
"Week_1" is a vaule that feeds off of my [GWP]. Can you show me how you would modify the above statement to incorporate the format I am looking for?

That would be great.

Thanks,

Keith.
May 10 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi Keith. Format is a very versatile function - but you have to be careful that what goes into it is of the correct type. When you use Nz, what is returned is a string value, not a number (if you look at the columns returned in your query you will see that the ones where you have used Nz are left-aligned instead of the normal numeric right-alignment, a sure sign of the change to a string). Trying to set a format for numbers will not work if the value to be formatted is actually a string.

To resolve this and let format do its job properly you need to convert the output of Nz to a number again. You can use CLng() if it is a whole number, CDbl() if it is a floating-point value, or CCur() if it is a currency value.

For the whole-number example where you need to show the comma for thousands you can use:

Week 1A: Format(CLng(Nz([Week 1], 0)), "#,###")

However, Format also returns a string value, not a numeric, so it may be better to leave the format function out of your base query (retaining the CLng(Nz()) parts) then use the custom format property of the report or form control involved to display the value in the correct format for users. The format property of a control uses formats similarly to the Format function, so to display a value with the comma separator you would just enter #,### as the format (not enclosed in double quotes).

Instead of using Nz for numbers I use simple custom functions to return non-null whole-number and floating-point values. That way I don't have to explicitly type convert the output of the function. These are shown below.
Expand|Select|Wrap|Line Numbers
  1. Public Function NonNullDbl(Val_In) As Double
  2.   If IsNull(Val_In) Then
  3.     NonNullDbl = 0
  4.   Else
  5.     NonNullDbl = Val_In
  6.   End If
  7. End Function
  8.  
  9. Public Function NonNullLong(Val_In) As Long
  10.   If IsNull(Val_In) Then
  11.     NonNullLong = 0
  12.   Else
  13.     NonNullLong = Val_In
  14.   End If
  15. End Function
-Stewart
May 10 '08 #6

kcdoell
100+
P: 230
Stewart:

First and foremost my apologies for not getting back to you sooner, I was MIA for awhile making sure I did not miscalculate Mother's Day here in the USA... Second, I always enjoy reading your thoughtful responses.

Now to this particular lesson. You are correct all my values in this particular query are left aligned and that is because I incorporated the Nz. Now it makes better sense to me what was happening with respect to it not recognizing the standard format (the query sees it as a string). My “end” query (The query in question) for this report actually is based on three others. In which I did not use the Nz function. In those other queries the values are right aligned. So, to conclude I applied the following code to my end query:

Expand|Select|Wrap|Line Numbers
  1.  Week_1: CLng(Nz([Week 1],0)) 
Afterwards I went to my report, opened it in design mode, went to the properties of [Week_1] and applied the Standard format. The result, my formating was there!

Very simple when you understand what is going on……

Thanks a million to both of you!

Best regards,

Keith.

P.S. I must admit that I am not very familiar with public functions though that seems to be something I may incorporate later as I gain more experience.
May 12 '08 #7

Post your reply

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