交叉表报告Null和Zeros [英] Crosstab report Null and Zeros

问题描述

你好，


我按照在
MSDN的Solutions.mdb示例中列出的步骤，从交叉表查询中做出报告。我现在有一个动态交叉表报告

（太棒了！），但有一个小问题。我无法单独访问show NULL

和0值。我在

我的文本框中尝试了以下格式属性：


0.0 ;; 0;"" - > NULL和0值都显示为0

0.0 ;;"";"" - > NULL和0值都显示为空白


我想要发生的是，如果在数据库中输入0，则它为
显示为0（或者0.0），如果没有数据进入该字段，则

显示空白。


有什么建议吗？ Duane - 你在外面，你似乎有很多

答案:)


谢谢，

JK

Hello,

I made a report from a crosstab query following the steps onlined in
MSDN''s Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get access to show NULL
and 0 values seperately. I''ve tried the following Format properties in
my text box:

0.0;;0;"" --> Both NULL and 0 values display as 0
0.0;;"";"" --> Both NULL and 0 values show up blank

What I want to happen is if a 0 is entered into the database, for it to
appear as 0 (or 0.0), while if no data is entered into the field, it
shows up blank.

Any suggestions? Duane - are you out there, you seem to have lots of
answers :)

Thanks,
JK

推荐答案

我不是Duane，但你应该看到Null为Null而没有任何东西在

Format属性中，或将格式设置为通用编号。


这些数字的来源是什么？例如，如果

交叉表中的值是某事物的数量，则计数可以返回零，其中

不匹配。

-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


< jk **** *@gmail.com>在消息中写道

news：11 ********************** @ z14g2000cwz.googlegr oups.com ...

I''m not Duane, but you should see Null as Null without anything in the
Format property, or with the Format set to General Number.

What is the source of these numbers? For example, if the Value in the
crosstab is a Count of something, the count could return a zero where there
are no matches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jk*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

您好，

我按照MSDN的Solutions.mdb示例中的步骤列出了交叉表查询报告。我现在有一个动态的交叉表报告
（太棒了！），但有一个小问题。我无法单独访问show NULL
和0值。我在
我的文本框中尝试了以下格式属性：

0.0 ;; 0;"" - > NULL和0值都显示为0
0.0 ;;"";"" - > NULL和0值都显示为空白

我想要发生的是，如果在数据库中输入0，它将显示为0（或0.0），而如果没有数据进入现场，它显示空白。

有什么建议吗？杜安 - 你在外面，你似乎有很多的答案:)

谢谢，
JK

Hello,

I made a report from a crosstab query following the steps onlined in
MSDN''s Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get access to show NULL
and 0 values seperately. I''ve tried the following Format properties in
my text box:

0.0;;0;"" --> Both NULL and 0 values display as 0
0.0;;"";"" --> Both NULL and 0 values show up blank

What I want to happen is if a 0 is entered into the database, for it to
appear as 0 (or 0.0), while if no data is entered into the field, it
shows up blank.

Any suggestions? Duane - are you out there, you seem to have lots of
answers :)

Thanks,
JK

你是对的。当我没有在格式属性中设置任何内容时，Null

为Null。但是，0也显示为NULL。对不起，忘了在第一个中提到

。报告的来源如下：


TRANSFORM Max（qryFinalCheckMarks.StudentMark）AS MaxOfStudentMark

SELECT [tblTeacher]。[FirstName]& " " &安培; [tblTeacher]。[LastName] AS

老师，UCase（[tblStudent]。[LastName]）& " " &

[tblStudent]。[FirstName] AS Student，qryFinalCheckMarks.CurrYear

来自qryFinalCheckMarks

GROUP BY [tblTeacher]。[FirstName] &安培; " " &安培; [tblTeacher]。[LastName]，

UCase（[tblStudent]。[LastName]）& " " &安培; [tblStudent]。[FirstName]，

qryFinalCheckMarks.CurrYear

ORDER BY [tblTeacher]。[FirstName]& " " &安培; [tblTeacher]。[LastName]，

UCase（[tblStudent]。[LastName]）& " " &安培; [tblStudent]。[FirstName]

PIVOT [AssName]& " （/"& [TotalMarks]&"）" ;;


我不会因任何特殊原因使用Max。它可能是任何

函数，因为我没有做任何计算。


谢谢，

JK

You''re right. When I don''t set anything in the format property, Null
is Null. BUT, 0 also appears as NULL. Sorry, forgot to mention that
in the first one. The source for the report is below:

TRANSFORM Max(qryFinalCheckMarks.StudentMark) AS MaxOfStudentMark
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMarks.CurrYear
FROM qryFinalCheckMarks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMarks.CurrYear
ORDER BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName]
PIVOT [AssName] & " (/" & [TotalMarks] & ")";

I don''t use Max for any particular reason though. It could be any
function as I''m not doing any calculation.

Thanks,
JK

我很困惑。


如果学生根本没有分数，则查询返回Null。

如果学生得到的最高分为零，则查询返回零。


在基于此查询的报告中，如果是，则为零

学生的最高分，如果该值不适用则为null。你的b $ b因此在报告中同时包含空值和零，这就是你所说的

你想要的。


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。

< jk ***** @ gmail。 COM>在消息中写道

news：11 ********************** @ g43g2000cwa.googlegr oups.com ...

I''m confused.

If the student has no marks at all, the query returns Null.
If the highest mark the student got was a zero, the query returns zero.

In the report based on this query, you will therefore get a zero if that was
the student''s highest mark, or a null if the value does not apply. You
therefore have both nulls and zeros in the report, and that''s what you say
you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<jk*****@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

你说得对。当我没有在格式属性中设置任何内容时，Null
是空的。但是，0也显示为NULL。对不起，忘了在第一个中提到
。报告的来源如下：

TRANSFORM Max（qryFinalCheckMarks.StudentMark）AS MaxOfStudentMark
SELECT [tblTeacher]。[FirstName]& " " &安培; [tblTeacher]。[LastName] AS
老师，UCase（[tblStudent]。[LastName]）& " " &
[tblStudent]。[FirstName] AS Student，qryFinalCheckMarks.CurrYear
来自qryFinalCheckMarks
GROUP BY [tblTeacher]。[FirstName]& " " &安培; [tblTeacher]。[LastName]，
UCase（[tblStudent]。[LastName]）& " " &安培; [tblStudent]。[FirstName]，
qryFinalCheckMarks.CurrYear
ORDER BY [tblTeacher]。[FirstName]& " " &安培; [tblTeacher]。[LastName]，
UCase（[tblStudent]。[LastName]）& " " &安培; [tblStudent]。[FirstName]
PIVOT [AssName]& " （/& [TotalMarks]&"）" ;;

我不会因为任何特殊原因使用Max。它可能是任何
功能，因为我没有做任何计算。

谢谢，
JK

You''re right. When I don''t set anything in the format property, Null
is Null. BUT, 0 also appears as NULL. Sorry, forgot to mention that
in the first one. The source for the report is below:

TRANSFORM Max(qryFinalCheckMarks.StudentMark) AS MaxOfStudentMark
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMarks.CurrYear
FROM qryFinalCheckMarks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMarks.CurrYear
ORDER BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName]
PIVOT [AssName] & " (/" & [TotalMarks] & ")";

I don''t use Max for any particular reason though. It could be any
function as I''m not doing any calculation.

Thanks,
JK

这篇关于交叉表报告Null和Zeros的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！