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" <[EMAIL PROTECTED]> 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! > > > > > >
