Including an IF formula in Access

[Bas Versteegen]

Hello,

With Access I have the following situation. I have created a table for
payment of employees in which I have include 2 columns. In one column it is
stated if a shopper is payed called "Payed". The datatype is "Yes/No". In the
column next to this I want to have the date the payment is made. Now if I
have to do this for thousands of employees so to say it takes up a lot of
time. I was wondering if it is possible to have access fill out today´s date
automatically when the checkbox in the row "Payed" is checked?

Thanks in advance!

 

[John W. Vinson]

Hello,

With Access I have the following situation. I have created a table for
payment of employees in which I have include 2 columns. In one column it is
stated if a shopper is payed called "Payed". The datatype is "Yes/No". In the
column next to this I want to have the date the payment is made. Now if I
have to do this for thousands of employees so to say it takes up a lot of
time. I was wondering if it is possible to have access fill out today´s date
automatically when the checkbox in the row "Payed" is checked?

Thanks in advance!

Sure, if you're using a Form to enter the data. (If you're entering data
directly into a table, *don't* - the inability to do things like this is just
one of many reasons).

On the Form you can bind the Payed field to a checkbox control, and have a
textbox bound to the PaymentDate field. You can put code in the checkbox's
AfterUpdate event. Click the ... icon by the After Update property and choose
"Code Builder"; edit the code to

Private Sub Payed_AfterUpdate()
If Me!Payed Then ' only set the date if the user checked, not unchecked it
Me!DatePaid = Date
End If
End Sub

 

[Bas Versteegen]

Thank you, I will give it a try. The problem in this case is that it is a
large amount of data. Somewhat around 30,000 lines per month. The data s
exported from excel... Is it in no way possible to do this?

 

[Philip Herlihy]

Hello,

John W. Vinson wrote:
Sure, if you're using a Form to enter the data. (If you're entering data
directly into a table, *don't* - the inability to do things like this is
just
one of many reasons).

On the Form you can bind the Payed field to a checkbox control, and have a
textbox bound to the PaymentDate field. You can put code in the checkbox's
AfterUpdate event. Click the ... icon by the After Update property and
choose
"Code Builder"; edit the code to

Private Sub Payed_AfterUpdate()
If Me!Payed Then ' only set the date if the user checked, not unchecked it
Me!DatePaid = Date
End If
End Sub

(quoting crudely reinstated by Phil)

Thank you, I will give it a try. The problem in this case is that it is a
large amount of data. Somewhat around 30,000 lines per month. The data s
exported from excel... Is it in no way possible to do this?

I'm guessing you're imagining a form which displays only a single
record. You can build a form which looks very much like a table, and
it's a form which gives you the option to use "events" as John has
suggested.

However, it sounds to me that you could do with a root-and-branch
re-examination of what you are doing. Excel's brilliant at what it
does, but I think of it as a brainstorming tool. Where you have volumes
of data following a consistent path it makes much more sense (and will
give much more power) to keep it all in Access. Of course, you may not
control the Excel spreadsheet. Did you know you can link an Access
database to an Excel spreadsheet so that the database reads the Excel
file directly?

For the particular operation you describe I'd be looking at creating a
facility within Access which selects the records to be marked as paid
and updates them (including the date) in one smooth sweep. However, if
you create a form to display multiple records (muck about with the
form-building wizard!) you can add an event procedure to the checkbox
once you have it laid out as you'd like it. And perhaps, now that
you're thinking in terms of the things only forms can do, you might take
a fresh look at how you might create a form which matches more precisely
how the user thinks and operates.

Phil, London