Date if yes...

[Chrisopia]

Im creating a query, where a check box (paid) enters "Now()" into a date field, labeled "Pad Date:"

I thought a simple query in the citeria of the "Paid Date" box

Switch([Paid]=Yes,"=Now()")

would solve this porblem.

I was just thinking, perhaps a blonde moment... will this enter the date that the button was checked on or would it keep applying todays date whilst the box is checked?

 

[namliam]

that will return NOW at the time the query is run.

If you want the moment it was clicked you will have to add code "on click" of the check box and a field in your table you can fill with NOW

 

[Chrisopia]

Im not familiar with the on click methods?

 

[namliam]

Right click your field, find properties.

Now find the "Events" tab, in there find the "On Click" event.

Now click the "..." button that appears to the right (only when you mouse over it) you can add coding there to do whatever you want.
i.e.
Me.YourDateField = Now()

 

[Chrisopia]

Cheers!
Worked a treat!

 

[namliam]

Take care now, this code is triggered every time the box is clicked (or de-clicked).

You have to make sure to now overwrite (I think) if there is already something there? Or if the box is changes from True to False, to reset the field ... or something.

 

[Brianwarnock]

You are correct Mailman there are a number of things for Chris to consider, however my concern is that although talking about Date he uses Now() which includes the time, this must be considered in any future use of the field. E.G. Comparing against Date()-1 to find all of yesterdays data will result in no hits.

Brian

 

[namliam]

I am not even going down that avenue yet...

Lots of potential issues, like the actual paydate is probably not the moment of processing but one day before or something

Possibly another 101 other potential issues I can think up, i.e. Should the user be able to do what I discribed or is payed actually payed, thus it cannot be unclicked...

 

[Chrisopia]

ACH - why is it one day something works, then the next it doesn't?.

I tested it on a tester database and it worked, I ticked a box and the date appeared as today. I could use Date() or Now(), I think for this case Ill need Date() because a query will be run for those which are unpaid for 30days and so on.

Problem is wehn I tried it in my real database, it didn't work!? I tried all methods and the box just ticks but nothing changes!? :S very annoying.

 

[namliam]

did you do it on the same computer? Your regional setting can mess with dates

 

[Chrisopia]

yup, same computer and everything.

My real database is getting quite messy so I started testing things in new .db files.

 

[Brianwarnock]

How about that old check - Compact and Repair, it sometimes corrects oddities.

Brian