vba 在 Access 中使用 DateDiff 访问条件格式中的颜色代码日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24562599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using DateDiff in Access to colour code dates in Conditional Formatting
提问by hello123
I have a table of clearances and their expiry dates. If a clearance has expired I would like it to be red, if it expires within 6 months I want it to be amber, if it expires in more than 6 months green and if it is yet to be granted (hence doesn't yet have an expiry date) then black.
我有一张清关表及其有效期。如果许可已过期，我希望它是红色的，如果它在 6 个月内过期，我希望它是琥珀色的，如果它在 6 个月内过期，则它是绿色的，如果它尚未被授予（因此还没有到期日）然后是黑色。
I have got the text to default as black and then have
我已经将文本默认为黑色，然后有
RED
红色的
[C_ExpiryDate]<Now()
AMBER
琥珀色
DateDiff("m",[C_ExpiryDate],Now())<6 And [C_ExpiryDate]>Now()
GREEN
绿色
DateDiff("m",[C_ExpiryDate],Now())>6 And [C_ExpiryDate]>Now()
This seems to have a few different problems, the red works fine from what I can tell. But the other two seem to be handled poorly, for example a clearance with 06 Sep 2015 is amber (today is 03 July 2014 so this should be green). Similar for 05 Mar 2017, I think it is just looking at the month regardless of the year, but I'm not too sure how to handle this better.
这似乎有一些不同的问题，据我所知，红色效果很好。但其他两个似乎处理得不好，例如 2015 年 9 月 6 日的许可是琥珀色（今天是 2014 年 7 月 3 日，所以这应该是绿色的）。与 2017 年 3 月 5 日类似，我认为它只是查看月份而不考虑年份，但我不太确定如何更好地处理这个问题。
Not sure if it might be easier to default as green and perhaps write something like
不确定默认为绿色是否更容易，也许写一些类似的东西
[C_ExpiryDate] = ""
But this didn't seem to do anything and everything was coming up as green even when there was a blank date. Similar for single quotations.
但这似乎没有任何作用，即使有一个空白日期，一切都变成了绿色。单引号类似。
Any suggestions appreciated...
任何建议表示赞赏...
回答by Fionnuala
Which version of MS Access are you using? This works for me:
您使用的是哪个版本的 MS Access？这对我有用：
回答by Brad
The way you have it written this is happening (i'm substituting #06/Sept/2015#for [C_ExpiryDate]to keep with your example)
你有它写出现这种情况的方式（我取代#06/Sept/2015#了[C_ExpiryDate]保持你的榜样）
Red?
红色的？
#06/Sept/2015#<Now() --> False
Amber?
琥珀色？
DateDiff("m",#06/Sept/2015#,Now())<6 And #06/Sept/2015#>Now() --> True
Green is not evaluated
绿色不评价
The problem is that you have the greater date first and the lesser date last. Switch them.
问题是您先有较大的日期，最后有较小的日期。切换它们。
Or alternatively, since you are checking [C_ExpiryDate]>Now()anyway you can take the absolute value of them and not worry about order.
或者，由于您[C_ExpiryDate]>Now()无论如何都在检查，因此您可以取它们的绝对值而不必担心顺序。
#06/Sept/2015# < Now() --> False (Red)
DateDiff("m",Now(),#06/Sept/2015#) < 6 And #06/Sept/2015# > Now() --> False (Amber)
DateDiff("m",Now(),#06/Sept/2015#) > 6 And #06/Sept/2015# > Now() --> True (Green)
You have no equal to 6 monthscondition though. Something for you to think about.
equal to 6 months虽然你没有条件。有什么值得你考虑的。
