susan said:
Hello,
Iwant to make a query on a table where only the values of te field
DAE are shown where the values are between NOW and NOW + 14 DAYS.
You should avoid using NOW, which returns date and time, unless you are
really interested in limiting by both date and time. Your field contains
date values only, so use the Date() function to return the current date
rather than using Now().
Important to know: the field DATE
DAE or DATE? If the latter, you need to change the name of that field as
soon as possible. "Date" is the name of a vba function used in queries and
is therefore a restricted keyword. To avoid restricted keywords when naming
your objects, you could google for "restricted keyword" and find the list
for Jet, but I prefer to simply "personalize" my field names. Yes, this
field stores dates, but so do fields like CreationDate, SalesDate,
BirthDate, etc. all of which are guaranteed not to be reserved. So, give the
field a name that really describes what it contains and you will avoid
problems associated with using reserved keywords for field names. Using
"Date", in particular, is a common cause of the "unknown function" error
that mystifies people when they encounter it.
is a textfield where the date is
defined like yyyy-mm-dd.
Well, storing dates in a text field is a mistake, but at least you've
mitigated the associated problems by storing them in an unambiguous and
sortable format.
Is there something possible with DateAdd()? Do I have to convert the
DATE-field? If xo, how?
If you mean converting the value contained to a date in the WHERE clause of
a query, you should avoid that. The best plan is to create a new field with
the correct Date/Time datatype and use an update query to populate it with
the values from the text field. Then the solution is trivial:
WHERE DateField BETWEEN Date() and Date() + 14
However, since you have properly formatted date values in the text field, a
solution that is only slightly more complex is available:
WHERE DateText BETWEEN Format(Date(),"yyyy-mm-dd") AND Format(Date() +
14,"yyyy-mm-dd")
HTH
