Manipulating dates within a form...

[cme]

G'Day from Downunder!

This is my first post here, other than to say G'Day, that is, which makes sense seeing I joined up only today.

I've been tasked to create a company client record database which permits several people at once to access and modify a communal database of clients. So far, so good. It's for a firm of solicitors, so I'd better get it RIGHT!!

One field requires the entry of a date, which the next field is supposed to automatically subtract 180 days from. I've hit the proverbial brick wall, I'm afraid. :banghead:

Is it possible for me to do this within the one entry form? The way I see it is that the first date would be entered, then the form would automatically display the -180 days result in the next box.

I've scoured a variety of books and manuals, and can't make this apparently simple task work. I feel there HAS to be a way, but I just can't see it.

Can anyone suggest the right way to enter the calculation, please? (And thanks in advance!)

ChrisE, Brisbane.

 

[JHB]

Place the below code in the AfterUpdate event for the control where you enter the date, (remember to change MyDate and TheDateMinus180 to the names of your control).

Private Sub MyDate_AfterUpdate()
  Me.TheDateMinus180 = DateAdd("d", -180, Me.MyDate)
End Sub

 

[cme]

Thank you! Y'know, I have a funny feeling I may not have nutted out that one without your help. Again, thanks. I'll try it as soon as I get back home...
Chris

 

[cme]

G'Day again. I am a total newbie to Access 2010 and, while I understand the expression you have suggested, I have no idea where to put it! Is this within the actual database itself, on a report form - or somewhere else? Sorry to sound so... well, DUMB, but I can't nut it out, and the F1 help on the topic was no use whatever...
Chris

 

[JHB]

See attached picture.
Let's say the unbound field is the field is where you enter your date as to be deducted 180 days from. Choose from the dropdown box [Event procedure]. Click afterwards on the 3 dots = "..." to the right.

 

[Khalid_Afridi]

G'Day again. I am a total newbie to Access 2010 and, while I understand the expression you have suggested, I have no idea where to put it! Is this within the actual database itself, on a report form - or somewhere else? Sorry to sound so... well, DUMB, but I can't nut it out, and the F1 help on the topic was no use whatever...
Chris

hmmm... so you know how to use the code on your form?

well its simple:
1. create a form and name your date field as 'MyDate'
2. then another field where you want to calculate your -180 days, name it 'TheDateMinus180'
3. in the event property of MyDate control, search for AfterUpdate property.
4. click the ... ellipses button and open up the VBA code builder.
5. copy and paste provided by JHB

Private Sub MyDate_AfterUpdate()
  Me.TheDateMinus180 = DateAdd("d", -180, Me.MyDate)
End Sub

On AfterUpdate event of MyDate let see what happened

 

[cme]

Gee, I'm sorry to have to continue this thread, but I must be thick or something! It's a bit hard for an absolute novice like me (and a long-time Mac user to boot, I'm embarrassed to admit) to be thrown in at the deep end and produce a working database in just a couple of days. (Of course, they need it yesterday!)

I'm simply bashing my head against the proverbial brick wall trying to get this thing to cooperate, and it's stopping me from getting stuck into the design stage of the data input and report forms.

I've added an image shot from my iPad (faster, sigh) showing what I've tried, and another showing the unfortunate result.

To test this formula before incorporating it into the 'real' database, I created a small form using "Date" and "Minus180" as the field names, and have put them in several times as shown. I even added the two spaces before the calculation details, as shown in the original formula suggestion!

Each time I run it I get a compile error which highlights the end of the argument, presumably as suspect code.

What have I done wrong, please?

 

[cme]

Here is the second image... oops!

 

[Brianwarnock]

The after update event being used is for text2, where is that from , you also talked about a field, probably meant control, called Date, bad choice call it myDate but then used Due in the formula.

Suggest that you carefully check all names.

Brian