30 day count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a database used at work to track certin accounts. recently, we have
decided to monitor these accounts heavily for 30 days, after which they will
be retained but there will no longer be a need to review on a daily basis.
All i want my code to do is take the date entered into the "Date Acct Open"
field and add 30 days to it and display it in the "Review End Date" field.
This is what i have in the "Review End Date" field:

=DateAdd("d", 30, [Date Acct Open])

I keep getting 1/1/1900 as output regardless of what date i use in the Acct
Open field, what am i doing wrong? Thanks for the help.
 
If you open your table in design view, what Data Type is the Date Acct Open
field? Is it Date/Time? Or is it Text? This won't work if it is Text.

Assuming it is a Date/Time field, is Review End Date a text box on a form,
or a calculated field in a query?

If Review End Date is a text box on a form, and Date Acct Open is a
Date/Time field, the expression you have should work. Set the Format
property of the text box to:
Short Date

If Review End Date is in the Field row in query design, try this:
Review End Date: CVDate(DateAdd("d", 30, [Date Acct Open]))
The type casting can make the difference, as explained here:
http://allenbrowne.com/ser-45.html
 
Move your code to the Current Event of the Form. The code you have executes
before [Date Acct Open] has any data to work with. Your CurrentEvent code
will be:
Me.ReviewDateControlName = DateAdd("d", 30, Me.[Date Acct Open])
...using your ReviewDateControlName of course.

You should really avoid spaces in your names for objects. Here's some links
to review:
http://support.microsoft.com/?id=286335
http://support.microsoft.com/?id=321266
http://support.microsoft.com/?id=826763

i have a database used at work to track certin accounts. recently, we have
decided to monitor these accounts heavily for 30 days, after which they will
be retained but there will no longer be a need to review on a daily basis.
All i want my code to do is take the date entered into the "Date Acct Open"
field and add 30 days to it and display it in the "Review End Date" field.
This is what i have in the "Review End Date" field:

=DateAdd("d", 30, [Date Acct Open])

I keep getting 1/1/1900 as output regardless of what date i use in the Acct
Open field, what am i doing wrong? Thanks for the help.
Click to expand...
 
I appreciate your help. It doesnt seem to be working though. It doesnt fill
the box at all now. This should be simple, i dont understand what's going
on. I changed the end/date box to text, which i think was the problem, and
now i dont get anything regardless of what i change the box too. It will
update immediatly, right? As soon as a date is entered into the Acct Open
field, the End Date field should update, right?

Allen Browne said:
If you open your table in design view, what Data Type is the Date Acct Open
field? Is it Date/Time? Or is it Text? This won't work if it is Text.

Assuming it is a Date/Time field, is Review End Date a text box on a form,
or a calculated field in a query?

If Review End Date is a text box on a form, and Date Acct Open is a
Date/Time field, the expression you have should work. Set the Format
property of the text box to:
Short Date

If Review End Date is in the Field row in query design, try this:
Review End Date: CVDate(DateAdd("d", 30, [Date Acct Open]))
The type casting can make the difference, as explained here:
http://allenbrowne.com/ser-45.html

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

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

edluver said:
i have a database used at work to track certin accounts. recently, we have
decided to monitor these accounts heavily for 30 days, after which they
will
be retained but there will no longer be a need to review on a daily basis.
All i want my code to do is take the date entered into the "Date Acct
Open"
field and add 30 days to it and display it in the "Review End Date" field.
This is what i have in the "Review End Date" field:

=DateAdd("d", 30, [Date Acct Open])

I keep getting 1/1/1900 as output regardless of what date i use in the
Acct
Open field, what am i doing wrong? Thanks for the help.
Click to expand...
Click to expand...
 
Thanks for the help. will this update the field immediatly? I just assumed
that once a date is entered into the Acct Open field, the End Date would
automatically update. Is that right? Because, if so, it's not working. As
a matter of fact, the End Date box remains empty. what am i doing wrong?

Allen Browne said:
If you open your table in design view, what Data Type is the Date Acct Open
field? Is it Date/Time? Or is it Text? This won't work if it is Text.

Assuming it is a Date/Time field, is Review End Date a text box on a form,
or a calculated field in a query?

If Review End Date is a text box on a form, and Date Acct Open is a
Date/Time field, the expression you have should work. Set the Format
property of the text box to:
Short Date

If Review End Date is in the Field row in query design, try this:
Review End Date: CVDate(DateAdd("d", 30, [Date Acct Open]))
The type casting can make the difference, as explained here:
http://allenbrowne.com/ser-45.html

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

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

edluver said:
i have a database used at work to track certin accounts. recently, we have
decided to monitor these accounts heavily for 30 days, after which they
will
be retained but there will no longer be a need to review on a daily basis.
All i want my code to do is take the date entered into the "Date Acct
Open"
field and add 30 days to it and display it in the "Review End Date" field.
This is what i have in the "Review End Date" field:

=DateAdd("d", 30, [Date Acct Open])

I keep getting 1/1/1900 as output regardless of what date i use in the
Acct
Open field, what am i doing wrong? Thanks for the help.
Click to expand...
Click to expand...
 
then i have no idea. It just simply will not work. I thought it was the
format of the field, but it was set as date before, and it didnt work then
either. If the code that i copied on here the first time "should" work, then
it doesnt make any sense that i am still having problems.

Allen Browne said:
[snip]
I changed the end/date box to text,
Click to expand...

Repeating the previous reply:
This won't work if it is Text.
Click to expand...
 
It is important to understand that the Data Type (middle column in table
design) defines what kind of data goes in the field.

If you choose Date/Time, Access will treat the values as dates, and you will
be able to perform date/time math with it.

If you choose Text, you have no guarantee that the entry is a date. It will
accept 14/32/2007, or Apr 33 2007, or Don't Know, or anything. The values
will not sort as dates. And performing date math on the contents will be
unreliable.

The first step, therefore, is to use a date/time field in your table.

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

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

edluver said:
then i have no idea. It just simply will not work. I thought it was the
format of the field, but it was set as date before, and it didnt work then
either. If the code that i copied on here the first time "should" work,
then
it doesnt make any sense that i am still having problems.

Allen Browne said:
[snip]
I changed the end/date box to text,
Click to expand...

Repeating the previous reply:
This won't work if it is Text.
Click to expand...
Click to expand...
 
Back
Top