Between [high date] and [low date] formula using Data Type: Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run a query using: Between [high date] and [low date] formula.
The dates I entered are in Data Type Text instead of Date/Time because
sometimes I need to enter text instead of dates. But when I run my query it
gives me the wrong output. How can I still use Data Type: Text and run a
query that gives me the right output?
 
Dates are stored internally as double precision numbers with the integer
portion representing the number of days since Dec 30, 1899 and the decimal
portion representing the time of day.

When you work with strings that "look" like dates they do not act as you
expect. Humans are quite flexible in their ability to interpret data.
Computers are rigid. Strings are processed column by column, left to right
whether they "look" like dates or someone's name. There is simply no way to
distinguish. I suspect your dates are formatted as mm/dd/yyyy or dd/mm/yyyy
depending on where you live. However, the ONLY way to accurately do a range
comparison with a string date or to properly sort it is to have it formatted
in yyyy/mm/dd order. Dates as humans view them are made up of three parts
and the relationship of those parts needs to be understood if you are going
to work with strings.

So, you either need to clean up your data so that the date is actually
stored as a date in a proper date/time data type OR you need to reformat
your strings to respect the hierarchical nature of the three components of a
date string.
 
Access will reconized most standard date formats like 11-5-05, 11/05/2005,
etc as criteria for your query of a datatype DateTime field.
What would be an example of your data entry that Access would not accept?

You might also post your SQL statement so it can be looked at for errors.
 
Back
Top