Form Expression help

  • Thread starter Thread starter a_m101
  • Start date Start date
A

a_m101

Hi,

I am having a problem with an expression in a form that I am making.
It is for a library system. In one field I have the date a book wa
taken out. However, in another text box in the form, I need to buil
an expression which will calculate the due date of the book. The du
date has to be 1 calender month from when the book was taken out.

However, if the calender month gives a date due back on a Saturday o
Sunday, then the date must be automatically changed to the followin
Monday. i.e. a book due date cannot fall on a weekend.

I tried using the DateAdd function, but could not find a way o
changing the date to a Monday if the due date happened to fall on
weekend.

I appreciate any help.

Thank
 
i think this should work for you:

Code
-------------------

Dim dueDate As Date

'get the due date one month from now
dueDate = DateAdd("m", 1, <check out date field>)

If Weekday(dueDate) = vbSunday Then
'check if due date is a sunday
'add one day if true
dueDate = DateAdd("d", 1, dueDate)
ElseIf Weekday(dueDate) = vbSaturday Then
'check if a due date is saturday
'add two days if true
dueDate = DateAdd("d", 2, dueDate)
End If

<due date field> = dueDate
 
Thanks for the response. I am having trouble though on using th
expressions. I am not familiar with using expressions in forms so I d
not know in which field boxes I should enter them.

Would I have to create a new input box on the form to enter th
expression
“If Weekday(dueDate) = vbSunday Then” ?

And do I have to enter two expressions together? For example, with th
expression:

“If Weekday(dueDate) = vbSunday Then” What do I write after this? Ca
I just write “dueDate = DateAdd("d", 1, dueDate)”? Would this b
accepted by Access?

Thanks for your hel
 
Thanks for the response. I am having trouble though on using th
expressions. I am not familiar with using expressions in forms so I d
not know in which field boxes I should enter them.

Would I have to create a new input box on the form to enter th
expression
“If Weekday(dueDate) = vbSunday Then” ?

And do I have to enter two expressions together? For example, with th
expression:

“If Weekday(dueDate) = vbSunday Then” What do I write after this? Ca
I just write “dueDate = DateAdd("d", 1, dueDate)”? Would this b
accepted by Access?

Thanks for your hel
 
hmmm...the code i posted should be used in VBA behind the form.
this code can be run based off of certain events on the form (ie. afte
a user types in a date in the check out text box or a button click
etc.)
if you're not at all familiar with VBA then the easiest thing to d
would be this:
- create a button on your form in design view
- Right click on the button and select properties
- On the events tab, select OnClick and click the box with the thre
dots in it. This will open the VBA editor
- It should say something like "Private Button1_OnClick Sub()". Past
the code after this line and before the "End Sub"
- change your references in the code to the field's actual name. ie
Change <check out date field> to the actual name of the field. You ca
find the name of the text box in the properities of the field. You'l
have to reference the text box in code with the "Me" qualifier. So i
should look something like this: Me.CheckOutDate.Value

Now when you click on the button, it will calculate a date. Make sur
you have a date in the check out date field or else it will cause a
error. You can do error checking or disable the button until someon
types in a date as well.

I'm not sure if this is more than you are looking for. I don't kno
that what you're asking for is possible in an expression. you will fin
using VBA instead of expressions on the form much more flexible onc
you get used to using it but it all depends on how much time you wan
to spend on it
 
Back
Top