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

  • Thread starter Thread starter Sandy
  • Start date Start date
S

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.
 
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, thank you so much. This worked great. I will see if I can complete
the second piece I'm trying to accomplish.
Sandy Norton


Allen Browne said:
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.

Sandy said:
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.
Click to expand...
Click to expand...
 
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 Month:DatePart("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


Allen Browne said:
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.

Sandy said:
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.
Click to expand...
Click to expand...
 
Criteria line under your date field (all on one line):
= DateSerial([Enter Year], [Enter Month], 1) AND
Click to expand...
< 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

Sandy said:
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 Month:DatePart("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?
Click to expand...
 
Back
Top