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

[kcdoell]

Hello:

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

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
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.

[nico5038]

Hello:

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

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
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)

[kcdoell]

Hello:

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

Keith.

[nico5038]

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)

[kcdoell]

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:

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.

[Stewart Ross]

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.

Public Function NonNullDbl(Val_In) As Double
  If IsNull(Val_In) Then
    NonNullDbl = 0
  Else
    NonNullDbl = Val_In
  End If
End Function
 
Public Function NonNullLong(Val_In) As Long
  If IsNull(Val_In) Then
    NonNullLong = 0
  Else
    NonNullLong = Val_In
  End If
End Function

-Stewart

[kcdoell]

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:

 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.