how to do a macro... Taylor Francis <radiulo[ at ]gmail.com> 14.07.2006 21:53:16 I have a database with a date in it. When I enter that date, I'd like another field to automatically calculate a related date. How? Specifically, I'd like the second date field to be the monday which is 4 weeks before the date I entered, not counting the monday that week... thanks, Taylor

Re: how to do a macro... Steve Schapel <schapel[ at ]mvps.org.ns> 14.07.2006 22:21:19 Taylor, This is not a job for a macro. Also, since the related date can always be directly derived from the initial date, it is not valid to store the derived data in a table. It should be calculated when required. This would either be done in a calculated field in a Query, or else by an expression in the Control Source property of an unbound textbox on your Form or Report, as required. If I understand you correctly, the expression for calculation would be like this... SecondDate: DateAdd("ww",-4,[YourDate]-Weekday([YourDate],2)+1) -- Steve Schapel, Microsoft Access MVP Taylor Francis wrote: [Quoted Text] > I have a database with a date in it. When I enter that date, I'd like > another field to automatically calculate a related date. How? > > > Specifically, I'd like the second date field to be the monday which is 4 > weeks before the date I entered, not counting the monday that week... > > thanks, > Taylor

Re: how to do a macro... Taylor Francis <radiulo[ at ]gmail.com> 14.07.2006 22:37:37 OK...sound's good... but how do I do that???? I'm really new to Access.... thanks! Steve Schapel wrote: [Quoted Text] > Taylor, > > This is not a job for a macro. > > Also, since the related date can always be directly derived from the > initial date, it is not valid to store the derived data in a table. It > should be calculated when required. This would either be done in a > calculated field in a Query, or else by an expression in the Control > Source property of an unbound textbox on your Form or Report, as required. > > If I understand you correctly, the expression for calculation would be > like this... > SecondDate: DateAdd("ww",-4,[YourDate]-Weekday([YourDate],2)+1) >

Re: how to do a macro... Taylor Francis <radiulo[ at ]gmail.com> 14.07.2006 23:04:29 Just FYI, I do everything from a table view... Taylor Francis wrote: [Quoted Text] > OK...sound's good... but how do I do that???? I'm really new to Access.... > > thanks! > > Steve Schapel wrote: > >> Taylor, >> >> This is not a job for a macro. >> >> Also, since the related date can always be directly derived from the >> initial date, it is not valid to store the derived data in a table. >> It should be calculated when required. This would either be done in a >> calculated field in a Query, or else by an expression in the Control >> Source property of an unbound textbox on your Form or Report, as >> required. >> >> If I understand you correctly, the expression for calculation would be >> like this... >> SecondDate: DateAdd("ww",-4,[YourDate]-Weekday([YourDate],2)+1) >>

Re: how to do a macro... Steve Schapel <schapel[ at ]mvps.org.ns> 14.07.2006 23:46:52 Taylor, If you are going to use Access effectively, you will need to learn to use queries and forms. In general, the purpose of tables is data storage. Apart from during the design phases of the database, you should never see the tables. The type of functionality that you are asking about is not available with tables. If you make a query, based on your table, then you can enter the expression I gave you into the Field row of a blank column in the query design grid. Well, substitute the actual name of your existing date field for the [YourDate]. Alternatively, if you make a form based on your table, you can put another textbox on the form, and then look at the Properties of the textbox, and in the Control SOurce property type like this... =DateAdd("ww",-4,[YourDate]-Weekday([YourDate],2)+1) -- Steve Schapel, Microsoft Access MVP Taylor Francis wrote: [Quoted Text] > Just FYI, I do everything from a table view... >

Re: how to do a macro... Taylor Francis <radiulo[ at ]gmail.com> 15.07.2006 01:03:12 Oh well...I just need the linked tables (I prefer Excel... Access seems unnecessarily complex)... Guess I just keep doing it by hand. The few times I've looked at queries and forms, I was un-impressed... Thanks anyway... Steve Schapel wrote: [Quoted Text] > Taylor, > > If you are going to use Access effectively, you will need to learn to > use queries and forms. In general, the purpose of tables is data > storage. Apart from during the design phases of the database, you > should never see the tables. The type of functionality that you are > asking about is not available with tables. If you make a query, based > on your table, then you can enter the expression I gave you into the > Field row of a blank column in the query design grid. Well, substitute > the actual name of your existing date field for the [YourDate]. > > Alternatively, if you make a form based on your table, you can put > another textbox on the form, and then look at the Properties of the > textbox, and in the Control SOurce property type like this... > =DateAdd("ww",-4,[YourDate]-Weekday([YourDate],2)+1) >