Want to place a prompt on a field created by using DateAdd

[Sandy]

In a query, I created a new date field by adding 1 to an existing date field
with DateAdd. I can display the value of the new field and I see the date
has been increased by 1. On the new date field I want to apply a prompt and
the query is returning no records. I eventually want to place prompts on the
month and year and display these fields in a report. Any help would be
greatly appreciated.

 

[Allen Browne]

When you view the query results (datasheet), is the field displaying
right-aligned, or left?

If Access recognises it as a date, it will be right-aligned. If it is
left-aligned, Access treats it as text, and then any sorting or criteria
turn out wrong. To solve this problem, wrap the expression in CVDate(),
e.g.:
CVDate(DateAdd("m",1, [InvoiceDate]))

Now you want to add a criterion under this field, e.g.:
[What invoice date]
Again, you must ensure that Access understands the data type of this
parameter correctly. Open the Parameters dialog (from the Query menu, or the
ribbon in A2007), and enter a row there:
[What invoice date] Date/Time

 

[Sandy]

Allen, thank you so much. This worked great. I will see if I can complete
the second piece I'm trying to accomplish.
Sandy Norton

When you view the query results (datasheet), is the field displaying
right-aligned, or left?

If Access recognises it as a date, it will be right-aligned. If it is
left-aligned, Access treats it as text, and then any sorting or criteria
turn out wrong. To solve this problem, wrap the expression in CVDate(),
e.g.:
CVDate(DateAdd("m",1, [InvoiceDate]))

Now you want to add a criterion under this field, e.g.:
[What invoice date]
Again, you must ensure that Access understands the data type of this
parameter correctly. Open the Parameters dialog (from the Query menu, or the
ribbon in A2007), and enter a row there:
[What invoice date] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In a query, I created a new date field by adding 1 to an existing date
field
with DateAdd. I can display the value of the new field and I see the date
has been increased by 1. On the new date field I want to apply a prompt
and
the query is returning no records. I eventually want to place prompts on
the
month and year and display these fields in a report. Any help would be
greatly appreciated.

 

[Sandy]

My second goal is to place a prompt on the month and the year of the date
field created in DateAdd with your help.

CheckOutDate:CVDate(DateAdd("d",1,[EndDate]))

I want to place a prompt on CheckOutDate for the month and a second one for
the year. I created a field MonthatePart("m",[CheckOutDate]) and added a
prompt on this field [Enter Month: ]. When the query runs it first prompts
for CheckOutDate (I don't know why) and then it prompts for Enter Month:
I get no results. In Query Parameter , what datatype is this new two digit
field? Do I add all parameters to Query Parameter and supply it a datatype?
--
Sandy Norton

When you view the query results (datasheet), is the field displaying
right-aligned, or left?

If Access recognises it as a date, it will be right-aligned. If it is
left-aligned, Access treats it as text, and then any sorting or criteria
turn out wrong. To solve this problem, wrap the expression in CVDate(),
e.g.:
CVDate(DateAdd("m",1, [InvoiceDate]))

Now you want to add a criterion under this field, e.g.:
[What invoice date]
Again, you must ensure that Access understands the data type of this
parameter correctly. Open the Parameters dialog (from the Query menu, or the
ribbon in A2007), and enter a row there:
[What invoice date] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In a query, I created a new date field by adding 1 to an existing date
field
with DateAdd. I can display the value of the new field and I see the date
has been increased by 1. On the new date field I want to apply a prompt
and
the query is returning no records. I eventually want to place prompts on
the
month and year and display these fields in a report. Any help would be
greatly appreciated.

 

[Allen Browne]

Criteria line under your date field (all on one line):

= DateSerial([Enter Year], [Enter Month], 1) AND

< DateSerial([Enter Year], [Enter Month] + 1, 1)

Parameter dialog:
[Enter Year] Long
[Enter Month] Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.1

My second goal is to place a prompt on the month and the year of the date
field created in DateAdd with your help.

CheckOutDate:CVDate(DateAdd("d",1,[EndDate]))

I want to place a prompt on CheckOutDate for the month and a second one
for
the year. I created a field MonthatePart("m",[CheckOutDate]) and added a
prompt on this field [Enter Month: ]. When the query runs it first
prompts
for CheckOutDate (I don't know why) and then it prompts for Enter Month:
I get no results. In Query Parameter , what datatype is this new two
digit
field? Do I add all parameters to Query Parameter and supply it a
datatype?