Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-29
4
Medium Priority
?
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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 32

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 32

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

610 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