Solved

Conditional Formatting - Date()

Posted on 2016-11-20
8
37 Views
Last Modified: 2016-11-21
Experts, I have been trying to shade a cell based on if it is between Date() and Date()-1.  It doesn't seem to work as I have it (see pic).

Value is between Date() and Date()-1

thank you
duedate
0
Comment
Question by:pdvsa
8 Comments
 
LVL 19
ID: 41895444
dates are store as Date AND Time. While beginning date may work to filter for the start, Date() may not for the end since there may also be a time. Instead, use:
>=( Date()-1 ) and < (Date() +1)

Dates are stored as whole numbers.
Time is a fraction through the day. for instance, 6am is 0.25, noon is 0.5, and 6pm is 0.75

When assigning values, use Date(), not Now() if all you want is date
0
 

Author Comment

by:pdvsa
ID: 41895463
thank you.
How should I enter that in the msg box?  
I get a syntax error.  

syntax
0
 
LVL 19
ID: 41895472
instead of Field Value is, choose: Expression Is

[due date]  >=( Date()-1 ) and [due date] < (Date() +1)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41895477
Hi,

Pls try

Field Value is / between  / Date()-1 / Date()+1

Regards
0
 
LVL 19
ID: 41895481
Rgonzo1971, that will only work whern there always is an extra time component ... there may not be.  The solution needs to work with all the data

DateValue([due date] ) can also be compared but that creates another issue in that you must first test to make sure it is filled since you can't use a conversion function on Null.  Easiest is to get everything less than the next day for the end date.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41895527
I recall that for some reason your dates are text. If not, if they were true date values, your original statement would work.

So try converting to date values:

    Expression is DateValue([due date]) Between Date()-1 And Date()

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41895723
you might also want to try:

Field Value:  >= Date() - 1 AND < Date() + 2

or, if the date is actually stored as a string:

Expression: (CDATE([DateField]) >= Date() -1) AND (CDATE([DateField] < Date() + 2)
0
 

Author Closing Comment

by:pdvsa
ID: 41895725
that was it.  thank you.  :)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question