Adding Dates

leerlap

Registered User.
Local time
Today, 16:39
Joined
May 4, 2003
Messages
22
Hi - I have what should be a simple question for someone to answer. I'm creating an access database and will have 2 fields. The first will allow the user to enter a date. The second will show this date + 14 days ahead. So, if i enter 5/3/03 in field name [Date], field name [Date14] should show 5/17/03.

Initially, I thought I should modify the default value box. However, when i do that, I keep getting an error saying that access doesn't recognize that field name or default value.

I then tried putting [date14]=[date]+14 in the [date] afterupdate property, but when i did, i received the following error when i tried out the form:
"Microsft Access can't find the macro '[Date14]=[Date]+14' The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under."

What's the best way to do this? Do I need to create this as a macro somehow? Thanks for the help.

Lee
 
you should not use Date as a field name as its a reserved word in access
assuming you are using a form for data entry and the txtbox
control names are Mydate and Date14
in the date14 txtbox set its control source to

=dateadd("d",14,Mydate)
 
Thanks so much - that worked; however, it brought up two issues I didn't realize would be problems:
(1) The [date14] box is now uneditable. Occasionally, I will want to modify this date, without changing [MyDate]. Is this possible?
(2) I want [date14] to be entered into the table. How can I make this a variable that gets automatically entered into the table, rather than just the form.
Thanks again for your help - i really appreciate it.
Lee
 
i have to assume a bit here as you dont give me a lot of information to work from.
i assume your form is bound to a table
to answer your questions

1.use the after update event for the textbox Mydate on your form,and insert this code

me.[Date14]=dateadd("d",14,Mydate)
that way you can edit date14 if you have to

2. Set the Record Source for date14 on your form to the corresponding field in the table that your form is bound to
3.dont use date as a field name in a table,form ,report or you will
get subtle errors in your program
 
leerlap said:
What's the best way to do this?

Forget the best way and do it the correct way.

You should not be storing the date that is fourteen days on as you can calculate it in a query.
 
Thanks for bearing with me. I tried the steps you mentioned, but came up with a couple of issues:

1. I entered me.[Date14]=dateadd("d",14,Mydate) into the afterupdate event for the textbox Mydate on my form, but received the following error msg:
"Microsft Access can't find the macro 'me.' The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under."

2. You suggested setting the RecordSource for date 14 to the corresponding field in the table. Did you mean ControlSource? The only RecordSource I could find was for the whole form.

I'd be happy to provide more info about this, but i'm not sure what more to say regarding the technical needs of the database. I'm trying to create a data tracking database program. We're doing a large clinical trial and we send out materials to subjects in our study. We want to track when we send things out to them and when we expect to get them back. So, we want to put in the date we send out materials and have the database log when we expect the subjects to return the materials. We may want to update the return date, because, for example, a subject may call and say they need a little more time. I'll also want to create reports for the project coordinator to run each day to know what needs to be checked on for that day. There are quite a few events we're wanting to track, but once I figure this small issue out, I think the whole program should fall in place.

Thanks again for your help. Mile-O-Phile, i'd also be interested in hearing additional ways to doing this using queries (which I know even less about). The more i learn, the better. Thanks again.

Lee
 
Hmm not sure why you would get that error
maybe my instructions were a bit vague

try this
1.right click on afterupdate and select build
2.select codebuilder
3.paste the code
4.on the top toolbar select debug
5.select compile and save
6.close the form and save the changes

You may Be better off to use field names that reflect better what
they do. Eg Sentdate, ReturnDate

as to Mile-O-Phile comments,he is right to say dont store calculated values.However You are in the best position to know what you need from your database.If you dont
need to look at [date14] some where down the track then by all
means dont store its value.If however the value of [date14] is
not constantly 14 days from mydate as you have said it may not be, then i dont know what query would return [date14]

e.g.
you have a table with 2000 records
of those 250 had a [Returndate] that was only 12 days from [SentDate],and 10 were 18days from [SentDate],and 50 were
11 days from [sentdate]


if you needed to see all records whose Returndate was less than 14 days from [Sentdate] then i dont know how you would retrieve those records,or if you needed to see all records whose
[returndate] was over due,again i cant see how you could retrieve
those records
 
You could add another field to the table which allows you to enter/change the number of days you want to add.
Then in the query it would just be DueDate":"DateAdd("D",[DaysAllowed],[SentDate])
there are times when smilies are a nuisance
 
Sorry leerlap in all my waffle i forgot to answer your question

yes i did mean controlsource,not recordsource
 
Great! This worked like a charm. Thanks so much. It seems like there should have been an easier (non-VB) way to do this, but i can work with this. Is the 'me.' part necessary? I tried it without and it seemed to work fine; however, i'm sure there was a reason to include it.

Well, i can now get on with this project - can't wait for the next problem to stick me :)
Thanks again,
Lee
 

Users who are viewing this thread

Back
Top Bottom