0

i have two fields in my access db table. One contains dates [D] and the other contains numbers [N]. I have created a calculated field that stores ( [D] - [N] ) dates.
my problem is that i would like to exclude weekends from the dates stored in the calculated field and cant seem to find how to do this from expression builder... is there a way to this? or is there any other way?

thanks

user3041384
  • 75
  • 2
  • 4
  • 11
  • How are you storing the dates in the calculated field ? – Richard Spencer Feb 18 '14 at 13:35
  • @RichardSpencer i am using an expression builder... so it kind of stores the results automatically – user3041384 Feb 18 '14 at 14:46
  • Is this on a form or within the table ? If so post what you have and I may have a way to do it. – Richard Spencer Feb 18 '14 at 15:03
  • @RichardSpencer doing this from the table. I selected the field tab... scolled down to calculated field and selected that. The expression builder comes up automatically and then i selected the two tables i wanted to subtract and hit enter. the table and values are then created automatically – user3041384 Feb 18 '14 at 15:31

1 Answers1

0

Assuming the [d] column contains a date and [n] is a number of days then a calculated column may look something like :-

=IIf(Weekday(DateAdd("d",[n]*-1,[d])) In (7,1),"",DateAdd("d",[n]*-1,[d]))

Notes :

  • (7,1) on my system Saturday is 7 and Sunday is 1 - this can be changed by altering the DateAdd function
  • You may wish to replace the "" with NULL
  • I have assumed [n] is a positive number so using the *-1 to go backwords in days.
Richard Spencer
  • 623
  • 7
  • 14
  • I used the code provided (replacing [n] with my days number and [d] with the target date) and i got an error message saying the expressin can not be used on a calculated field. – user3041384 Feb 19 '14 at 14:55
  • and the should the d in inverted comma's ((Weekday(DateAdd("d"...)be replaced by an actual date? – user3041384 Feb 19 '14 at 14:58
  • "d" ( first argument ) of dateadd tells dateadd that you wish to work in days rather than "y" years or "m" months. – Richard Spencer Feb 24 '14 at 11:25