query on date


S

susan

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.

Important to know: the field DATE is a textfield where the date is defined
like yyyy-mm-dd.

Is there something possible with DateAdd()? Do I have to convert the
DATE-field? If xo, how?

Anyone an idea?

Thanks,

Susan
 
Ad

Advertisements

B

Bob Barrows

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting date gives bad results in query 2
Delete Query 6
Date Function in query 3
Date Calculation 1
Date format in query export. 4
Date Calculation 1
Changing date format 2
Date query and previous year 5

Top