A spin on a common question. DateAdd

This is a spin on a common question. I have a query with a field labeled:
Day: Format([Date],"ddd, mm/dd/yy"). I need it to display the day along with
the date (Sat, 4/24/210) ~ works great. I’m trying to retrieve all records
for one month out by using: DateAdd("d",+31,Date()) ~ not working. The
only thing I can think of is that the format is somehow messing it up. Would
this be the case? Thank you in advance!

On Thu, 22 Apr 2010 17:59:01 -0700, Flopbot
wrote:

This is a spin on a common question. I have a query with a field labeled:
Day: Format([Date],"ddd, mm/dd/yy"). I need it to display the day along with
the date (Sat, 4/24/210) ~ works great. I’m trying to retrieve all records
for one month out by using: DateAdd("d",+31,Date()) ~ not working. The
only thing I can think of is that the format is somehow messing it up. Would
this be the case? Thank you in advance!

The Format() function takes its argument and turns it into a string. DateAdd()
works with Date/Time values (actually stored as a number), not with strings!

Apply the criterion to the (badly misnamed, Date is a reserved word and will
cause problems) [Date] field, not to the text string [Day].

Note that your criterion will not return all records "for one month out" - it
will return all records PAST 31 days from today. To get the records between
today's date and a month from today, use a criterion on Date() of

= Date() AND = DateAdd("m", 1, Date())

--

John W. Vinson [MVP]

Thank you John.

Good point about the name. I’ve changed it to Event Date. Also, I
simplified by query based on your suggestions and changed the criterion.
Basically, the reason for the query is to include the information as a table
imbedded in an email. I’m using word’s mail merge function to create the
email and want to use the “Insert Database Wizard� located on word’s
“Database Toolbox� to insert the table. However, this wizard doesn’t
recognize forms or reports. Also, it doesn’t recognize any queries with
something unique (expressions, Dept: Department, anything other than a
simple label) in the Field row. Due to size & display requirements in the
email, I can’t create such a simple query. After much experimenting, the
only workaround I found was to link the query to a form and do any formatting
of date/field names there. Then, by displaying it in datasheet view, I can
cut/paste the table into an email. It’s not as slick as I hoped, but it
still shaves several hours off my past method.

Thank you for your advice!

Chris


"John W. Vinson" wrote:

On Thu, 22 Apr 2010 17:59:01 -0700, Flopbot
wrote:

This is a spin on a common question. I have a query with a field labeled:
Day: Format([Date],"ddd, mm/dd/yy"). I need it to display the day along with
the date (Sat, 4/24/210) ~ works great. I’m trying to retrieve all records
for one month out by using: DateAdd("d",+31,Date()) ~ not working. The
only thing I can think of is that the format is somehow messing it up. Would
this be the case? Thank you in advance!

The Format() function takes its argument and turns it into a string. DateAdd()
works with Date/Time values (actually stored as a number), not with strings!

Apply the criterion to the (badly misnamed, Date is a reserved word and will
cause problems) [Date] field, not to the text string [Day].

Note that your criterion will not return all records "for one month out" - it
will return all records PAST 31 days from today. To get the records between
today's date and a month from today, use a criterion on Date() of

= Date() AND = DateAdd("m", 1, Date())

--

John W. Vinson [MVP]
.