I just wanted to say a hoooge thank you to Peter for the help he gave me to sort my issue.
Whilst the solution provided didn't do exactly what I had originally intended (still not sure if my 'grand idea' is even possible?!!!) the result was a very practical 'work around', so I no longer need to worry about complicating things beyond my experience and expertise!! The building of this database for me has been a steep learning curve and without wonderful resouces like this list, I fear I may have given up when the going got really tough! Thanks once again :) Loosey --- In [email protected], "phoogenb" <[EMAIL PROTECTED]> wrote: > > Hi Loosey, > > Yes, that's the way with the kindest learning curve. > > Create a new query in design view. Add the table. Change the query > type to Update Query. Select the "lapsed" field. Type the > expression I gave in the "Update To" box. > > Each time you run the query, it will reset the value. In other > words, it will both clear and check as appropriate. > > I should note, however, that this exposes you to potential human > error, namely, forgetting to run the update query. > > A better approach would be to remove the lapsed field entirely, and > to use an expression to compare the renewal date to the current date > each time you want to display the lapsed checkbox. > > You could do this by creating a select query based on your table and > adding the expression as a new (calculated) field in the query. A > simple example, assuming your table is named "tblTable", would have > two columns in the QBE grid. In the Field row, the first column > would show tblTable.* (this includes all fields from tblTable). In > the second one, you would type: > > lapsed: RenewalDate<Date() > > If you like, change "lapsed" to whatever name you would like to give > to the calculated field. Change RenewalDate to the actual name of > the (real) field in your table. "Date()" is the function that > returns the current date (according to your computer's system clock). > > Good luck, > > Peter Hoogenboom > > > > > > --- In [email protected], "Loosey" <locket_uk@> wrote: > > > > Peter, > > > > Many thanks for the advice so far, yes (i think!) the checkbox is > > bound to a field in the table. At least when I go to properties, > the > > control source is the 'lapsed' field in the table I created. > > > > Thus if i manually tick the box, the table displays a yes value to > > indicate the person's membership has lapsed. > > > > How do I create an update query . . is this using the query > builder? > > > > Thanks in advance > > > > Loosey > > > > --- In [email protected], "phoogenb" <phoogenb@> wrote: > > > > > > Loosey: > > > > > > Is the checkbox bound to a field in the table? If so, you can > > make > > > an update query. If you're new to SQL then the QBE is probably > > the > > > way to go. > > > > > > If the checkbox is not bound to a field, then you can bind it to > > an > > > expression (this would be the same expression that you use in > your > > > update query, actually). > > > > > > The expression would be: > > > > > > =[RenewalDate] < Date() > > > > > > This expression evaluates to TRUE if the renewal date is earlier > > than > > > today, resulting in a checked checkbox. It evaluates to FALSE if > > the > > > renewal date is today or later, giving you an unchecked checkbox. > > > > > > Good luck, > > > > > > Peter Hoogenboom > > > > > > --- In [email protected], "Loosey" <locket_uk@> wrote: > > > > > > > > Hi, > > > > > > > > I am going round in circles and unfortunately don't know enough > > > > about the query builder or SQL (or if these are even going to > > > help!) > > > > to resolve my issue. > > > > > > > > I have a club membership database which i have made (steep > > learning > > > > curve for me so far!) which I need some help with, if anyone > > could > > > > help with the below I would be eternally grateful. > > > > > > > > I have a form in which I have inseted a check box for lapsed > > > members > > > > however I would like to have an auto-calculation (if possible) > > so I > > > > do not have to manually mark people as overdue/lapsed. > > > > > > > > Can I put a macro or something in the form that will cross > > > reference > > > > the renewal date and todays date and mark the record lapsed (by > > way > > > > of the check box?)if the date today is later than the renewal??? > > > > > > > > Is there anyway this can be done? And if so, help with the > > > code/how > > > > to do it would be greatly appreciated. > > > > > > > > I have looked online but everything I have tried so far has > > failed > > > > miserably! > > > > > > > > > >
