Solved

Format of date stored as text compared to date() => problem with month

Posted on 2014-01-29
4
432 Views
Last Modified: 2014-01-29
I'm using format to arrange a date time stored as text:

Format(Mid([CUSTOM_FIELD],5,2) & "/" & Mid([CUSTOM_FIELD],7,2) & "/" & Left([CUSTOM_FIELD],4),"mm/dd/yyyy")

so that I can then use date() to work out data from yesterday date()-1

The trick is that my formating creates a month as 2 digits always and date() will return the month as a single digit creating a situation where 01/28/2014 and 1/28/2014 are not recognized as the same.

I'm wondering what I can do with this to correct for this:

WHERE (((Format(Mid([CUSTOM_FIELD],5,2) & "/" & Mid([CUSTOM_FIELD],7,2) & "/" & Left([CUSTOM_FIELD],4),"mm/dd/yyyy"))=Date()-1));
0
Comment
Question by:ghettocounselor
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39817826
Try this (compares both sides using the same format):

WHERE (((Format(Mid([CUSTOM_FIELD],5,2) & "/" & Mid([CUSTOM_FIELD],7,2) & "/" & Left([CUSTOM_FIELD],4),"mm/dd/yyyy"))=Format(Date()-1),"mm/dd/yyyy)");

Open in new window



Or if your text field is a standard/valid date format:

WHERE CDate([CUSTOM_FIELD])  = dateadd("d", -1, Date())
0
 

Author Closing Comment

by:ghettocounselor
ID: 39817850
Sweet, thanks for quick response.
Notes:
Needed one more paren after format
Format((Date()-1),"mm/dd/yyyy")
0
 
LVL 31

Expert Comment

by:awking00
ID: 39817876
It appears that your "dates" are stored as text in a 'yyyymmdd' format, so it might be easier to convert date() - 1 to text to do the compare. This only works for that format since any comparison will be based on ascii values.
where custom_field = format(date() - 1,"yyyymmdd")

A good example why you should NEVER store dates as anything other than date/time datatypes if you need to perform any kind of data math.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39817890
Be careful! ASCII comparisons can produce some unwanted results. For example,
01/15/2014 will come before 02/15/2013. The reason I said you can get away with yyyymmdd formats.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now