only calculate business days exclude weekends

[ASSK]

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[Wayne-I-M]

Yu could add another table with week days = Sun = 1, Mon = 2, etc and do do
lots of mad calculations - but you don't really need to - use this (change
TableName to what it really is)

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=7
And
[TableName]![LLOOReqTime]<#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))


If LLOOReqDate is a Sat or Sun it will give the following Monday's date.
If the LLOOReqTime is before 12 (not Sat or Sun) it will give the same date
If the LLOOReqTime is after 12 (not Fri Sat or Sun) it will give the
following day
If the LLOOReqTime is after 12 on a Fri it will give the following Mon

 

[Wayne-I-M]

opps - sorry (forgot to add the Friday after 12pm exclusion)

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=6
And
[TableName]![LLOOReqTime]>#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))

 

[ASSK]

Hi Thanks for the function.

Just two questions. Would I be right by doing this:

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=7

And

[TableName]![LLOOReqTime]<#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))

And

IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=6

And

[TableName]![LLOOReqTime]>#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))

So do I just join all the four syntax together? Can I put it in the field of
a query? Also, [TableName] do I put the name of the table where I pulled the
query from? The table which contain the LLOOReqDate and LLOOReqTime is called
[1 Loan]. Do I just replace the [TableName] to [1 Loan]?

Sorry to be a pain.....

Thankyou so much for your help.

opps - sorry (forgot to add the Friday after 12pm exclusion)

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=6
And
[TableName]![LLOOReqTime]>#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))



--
Wayne
Manchester, England.

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[ASSK]

I have created a test database where the LLOOReqDate is the same as
[Received] and LLOOReqTime is the same as [Time Rec]. I have combined the
function you've written for me together and this is what it looks like.

IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=6
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]>#12:01:00#,[received],DateAdd("d",1,[received],
IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=7
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]<#12:01:00#,[received],DateAdd("d",1,[received]))))

What wrong with this? It's telling me "The expression you entered has a
function containing the wrong number of arguments".

Please tell me what I am doing wrong.....

opps - sorry (forgot to add the Friday after 12pm exclusion)

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=6
And
[TableName]![LLOOReqTime]>#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))



--
Wayne
Manchester, England.

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[Wayne-I-M]

This is just the type of formula that you need to "step through". It is a
nested IIF. This means that forumlas are "nested" (placed) inside other
formulas.

If I was you I would "step through" each section of the formula one by one.
I may be wrong (I learnd english at school) but I "think" step through means
to test each bit of the formual or code to see where it fails - or it may not
fail.

So

Create the most basic (normally the 1st in a nested IIF) section of the
formula and see if it works. If it does add the next bit and so on.

Another benifi of this is that you will learn "how" the forumula works
rather than just copying it. This is "vital" as it means you can change it
if needed.

I have spotted where you are going wrong - but, this is the 1st time I have
said this in the forum - I am not going to tell you the answer. You must
step though and test it yourself. You "will" find it. It is very simple
error.

Try this

IIF formulas "only" give True or False. If something is True "display"
something, if it is False either Display something else or do another formula.

You want to arrive at an answer that that is "true".

You need to check "each" forula at a time.

This is you formula and it complex - so cut it down

Weekdays are Sun=1 Mon=2 Tues=3 etc etc

IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=6
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]>#12:01:00#,[received],DateAdd("d",1,[received],
IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=7
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]<#12:01:00#,[received],DateAdd("d",1,[received]))))

The 1st part of the formula is this

IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7)

What is this doing???

You are asking a question - the first part is this.....
If this statment is true - - - the content of the field named [received]
has a "weekday" number that = 7 or a weekeday number = 1 then you want to
display the following Monday.
You get the follwing Monday date using the formula
([received]-Weekday([Received],3)+7)
If the statment is NOT true (false) you want to ask
Ok then if that statment is Not true then lets check if somethign else is
true. So you say - right then the 1st statment is false what about this

IIf(Weekday([Received])=6 > And [Time
Rec]>#12:01:00#,([Received]-Weekday([received],3)+7)
As you will see - this is the 2nd part of your (big) formula

So this is False (the content of your field [recieved] is NOT = 7 (Saturday)
or = 1 (Sunday)
IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7)

So you ask the next question
OK if it's not either Saturday or Sunday then what this
IIf(Weekday([Received])=6 > And [Time
Rec]>#12:01:00#,([Received]-Weekday([received],3)+7)
If the content of the field [receive] = 6 (Friday) AND the content of the
field [Time Rec] > (is greater than) #12:01:00# (1 minute after 12 noon) then
again - using the same section of the formula as above
([Received]-Weekday([received],3)+7) display the date of the following Monday.

If this statment is also not true you need to ask another question
IIf([Time Rec]>#12:01:00#,[received],DateAdd("d",1,[received]

If the content of [Time Rec] is greater than 12:01:00 then insert then
content of [recieved] if not then add one day to the date contained within
the field [received] - which is what this section does - - -
DateAdd("d",1,[received].

Ask you can see if you break it down you "Will" find the answer.

If this is your 1st nested if then you may want to read up on the subject
before you start. This formla "looks" complex but if you break it down in to
it individual section it is really very simple. Is this true then do this,
if not then do something else or ask another question, if the 2nd question is
true then do this or (again) ask another question.

Just one point. If you are making am application for a company it is "your"
task to understand what you are doing. It "will" go wrong sooner or later
and you company (boss, workmates, etc) will look to you to fix it. It is
really not a good idea just to copy codes that seem to work and use them.
You "really do need" to understand them.

Good luck

I hope this helped a littl.








--
Wayne
Manchester, England.

I have created a test database where the LLOOReqDate is the same as
[Received] and LLOOReqTime is the same as [Time Rec]. I have combined the
function you've written for me together and this is what it looks like.

IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=6
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]>#12:01:00#,[received],DateAdd("d",1,[received],
IIf(Weekday([received])=7 Or
Weekday([received])=1,([received]-Weekday([Received],3)+7),IIf(Weekday([Received])=7
And [Time Rec]>#12:01:00#,([Received]-Weekday([received],3)+7),IIf([Time
Rec]<#12:01:00#,[received],DateAdd("d",1,[received]))))

What wrong with this? It's telling me "The expression you entered has a
function containing the wrong number of arguments".

Please tell me what I am doing wrong.....

opps - sorry (forgot to add the Friday after 12pm exclusion)

Expected Completion Date: IIf(Weekday([TableName]![LLOOReqDate])=7 Or
Weekday([TableName]![LLOOReqDate])=1,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf(Weekday([TableName]![LLOOReqDate])=6
And
[TableName]![LLOOReqTime]>#12:01:00#,([TableName]![LLOOReqDate]-Weekday([TableName]![LLOOReqDate],3)+7),IIf([TableName]![LLOOReqTime]<#12:01:00#,[TableName]![LLOOReqDate],DateAdd("d",1,[TableName]![LLOOReqDate]))))



--
Wayne
Manchester, England.

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[John Spencer]

So if the day is Friday add two more days and if it is not Friday add zero
more days. This assumes that you aren't going to have an LLOOReqDate that is
on a Saturday or Sunday and you don't care about Holidays.


IIF(LLOORegTime<=#12:00:00),1,2)+
IIF(Weekday(LLOOReqDate)=6,2,0)

DateAdd("d",IIF(LLOORegTime<=#12:00:00),1,2)+
IIF(Weekday(LLOOReqDate)=6,2,0),LLOOREQDate)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

You might also like to try this:

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime] <= #12:00:00#,1 +
IIf(Weekday([LLOOReqDate],1) = 6,2,0),2 + IIf(Weekday([LLOOReqDate],1) =
6,1,0)),[LLOOReqDate])

It adds an extra 2 days to the 1 if its before 12;00 on Friday, or an extra
1 day to the 2 if its after 12:00 on Friday.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Now there's a coincidence! Identical to John's, given a few minor
variations. Isn't there something said about "great minds …..and fools…."?
We'll leave you to decide which applies.

Good luck

Ken Sheridan
Stafford,

 

[ASSK]

Hi,

Thankyou so much. The function worked like a GEM. You guys are lifesavers.

One more question, as the database I am working on is an existing one and
there are already pre existing date. What I want to do is use a "isblank" so
that if there is no LLOOReqTime entered, the result would be blank, how can I
do that to incorporate into the function?

You might also like to try this:

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime] <= #12:00:00#,1 +
IIf(Weekday([LLOOReqDate],1) = 6,2,0),2 + IIf(Weekday([LLOOReqDate],1) =
6,1,0)),[LLOOReqDate])

It adds an extra 2 days to the 1 if its before 12;00 on Friday, or an extra
1 day to the 2 if its after 12:00 on Friday.

Ken Sheridan
Stafford, England

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[Ken Sheridan]

You could extend it a little to test for Null:

Expected Completion Date: IIf(Not IsNull([LLOOReqTime]),DateAdd
("d",IIf([LLOOReqTime] <= #12:00:00#,1 + IIf(Weekday([LLOOReqDate],1) =
6,2,0),2 + IIf(Weekday([LLOOReqDate],1) = 6,1,0)),[LLOOReqDate]),Null)

One thing occurs to me, however. What about if the LLOOReqTime is after
12:00 on a Thursday? The expression would compute the estimated completion
date as the following Saturday. Should it not be the following Monday?

If so I'd be inclined to abandon the expression as its going to get pretty
convoluted to cater for this. Its far simpler to write a function. Yes, I
know you say you know nothing of VBA coding, but its not really that scary.
A suitable function would be:

Public Function ECD(varLLOOReqTime, varLLOOReqdate)

Dim dtmECD As Date

' if LLOOReqTime is Null don't do anything
' Null will be returned
If Not IsNull(varLLOOReqTime) Then
' add one day
' if LLOOReqTime at or before noon
' add one day
dtmECD = DateAdd("d", 1, varLLOOReqdate)
' if after noon add an extra day
If varLLOOReqTime > #12:00:00 PM# Then
dtmECD = DateAdd("d", 1, dtmECD)
End If

' if result is a weekend day add days until Monday
Do While Weekday(dtmECD, vbSaturday) < 3
dtmECD = DateAdd("d", 1, dtmECD)
Loop

ECD = dtmECD
End If

End Function

All you have to do is paste the above into a standard module in your
database. Either open an existing module or a new one, and in the
declarations area you should see the following two lines in place:

Option Compare Database
Option Explicit

Just paste the whole of the above code below those lines. It will then be
inserted as a function, so you'll find it will move off the declarations area
to the new function. Save the module under a different name from the
function, e.g. mdlDateStuff. You can call it anything you like in fact, but
I'd suggest you keep the 'mdl' prefix as that way it won't get confused with
any other objects names.

Now in your query you just need to put:

Expected Completion Date: ECD([LLOOReqTime],[ LLOOReqDate])

The function is declared Public, which exposes it throughout the database,
so you can also call it in the same way in the ControlSource property of a
text box in a form or report if you wish.

Ken Sheridan
Stafford, England

Hi,

Thankyou so much. The function worked like a GEM. You guys are lifesavers.

One more question, as the database I am working on is an existing one and
there are already pre existing date. What I want to do is use a "isblank" so
that if there is no LLOOReqTime entered, the result would be blank, how can I
do that to incorporate into the function?

You might also like to try this:

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime] <= #12:00:00#,1 +
IIf(Weekday([LLOOReqDate],1) = 6,2,0),2 + IIf(Weekday([LLOOReqDate],1) =
6,1,0)),[LLOOReqDate])

It adds an extra 2 days to the 1 if its before 12;00 on Friday, or an extra
1 day to the 2 if its after 12:00 on Friday.

Ken Sheridan
Stafford, England

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[ASSK]

It worked great....

Thankyou so much for you VBA codes...As I've never ever done coding before,
it was scary doing it.

I've actually self taught myself how to use access.....so thankyou so much
for you help again.

You could extend it a little to test for Null:

Expected Completion Date: IIf(Not IsNull([LLOOReqTime]),DateAdd
("d",IIf([LLOOReqTime] <= #12:00:00#,1 + IIf(Weekday([LLOOReqDate],1) =
6,2,0),2 + IIf(Weekday([LLOOReqDate],1) = 6,1,0)),[LLOOReqDate]),Null)

One thing occurs to me, however. What about if the LLOOReqTime is after
12:00 on a Thursday? The expression would compute the estimated completion
date as the following Saturday. Should it not be the following Monday?

If so I'd be inclined to abandon the expression as its going to get pretty
convoluted to cater for this. Its far simpler to write a function. Yes, I
know you say you know nothing of VBA coding, but its not really that scary.
A suitable function would be:

Public Function ECD(varLLOOReqTime, varLLOOReqdate)

Dim dtmECD As Date

' if LLOOReqTime is Null don't do anything
' Null will be returned
If Not IsNull(varLLOOReqTime) Then
' add one day
' if LLOOReqTime at or before noon
' add one day
dtmECD = DateAdd("d", 1, varLLOOReqdate)
' if after noon add an extra day
If varLLOOReqTime > #12:00:00 PM# Then
dtmECD = DateAdd("d", 1, dtmECD)
End If

' if result is a weekend day add days until Monday
Do While Weekday(dtmECD, vbSaturday) < 3
dtmECD = DateAdd("d", 1, dtmECD)
Loop

ECD = dtmECD
End If

End Function

All you have to do is paste the above into a standard module in your
database. Either open an existing module or a new one, and in the
declarations area you should see the following two lines in place:

Option Compare Database
Option Explicit

Just paste the whole of the above code below those lines. It will then be
inserted as a function, so you'll find it will move off the declarations area
to the new function. Save the module under a different name from the
function, e.g. mdlDateStuff. You can call it anything you like in fact, but
I'd suggest you keep the 'mdl' prefix as that way it won't get confused with
any other objects names.

Now in your query you just need to put:

Expected Completion Date: ECD([LLOOReqTime],[ LLOOReqDate])

The function is declared Public, which exposes it throughout the database,
so you can also call it in the same way in the ControlSource property of a
text box in a form or report if you wish.

Ken Sheridan
Stafford, England

Hi,

Thankyou so much. The function worked like a GEM. You guys are lifesavers.

One more question, as the database I am working on is an existing one and
there are already pre existing date. What I want to do is use a "isblank" so
that if there is no LLOOReqTime entered, the result would be blank, how can I
do that to incorporate into the function?

You might also like to try this:

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime] <= #12:00:00#,1 +
IIf(Weekday([LLOOReqDate],1) = 6,2,0),2 + IIf(Weekday([LLOOReqDate],1) =
6,1,0)),[LLOOReqDate])

It adds an extra 2 days to the 1 if its before 12;00 on Friday, or an extra
1 day to the 2 if its after 12:00 on Friday.

Ken Sheridan
Stafford, England

:

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.

 

[Felina]

where do i post the code at? i tried to post to: form, my scheduled due date,
properties, event and then used the code button...please help!!

You could extend it a little to test for Null:

Expected Completion Date: IIf(Not IsNull([LLOOReqTime]),DateAdd
("d",IIf([LLOOReqTime] <= #12:00:00#,1 + IIf(Weekday([LLOOReqDate],1) =
6,2,0),2 + IIf(Weekday([LLOOReqDate],1) = 6,1,0)),[LLOOReqDate]),Null)

One thing occurs to me, however. What about if the LLOOReqTime is after
12:00 on a Thursday? The expression would compute the estimated completion
date as the following Saturday. Should it not be the following Monday?

If so I'd be inclined to abandon the expression as its going to get pretty
convoluted to cater for this. Its far simpler to write a function. Yes, I
know you say you know nothing of VBA coding, but its not really that scary.
A suitable function would be:

Public Function ECD(varLLOOReqTime, varLLOOReqdate)

Dim dtmECD As Date

' if LLOOReqTime is Null don't do anything
' Null will be returned
If Not IsNull(varLLOOReqTime) Then
' add one day
' if LLOOReqTime at or before noon
' add one day
dtmECD = DateAdd("d", 1, varLLOOReqdate)
' if after noon add an extra day
If varLLOOReqTime > #12:00:00 PM# Then
dtmECD = DateAdd("d", 1, dtmECD)
End If

' if result is a weekend day add days until Monday
Do While Weekday(dtmECD, vbSaturday) < 3
dtmECD = DateAdd("d", 1, dtmECD)
Loop

ECD = dtmECD
End If

End Function

All you have to do is paste the above into a standard module in your
database. Either open an existing module or a new one, and in the
declarations area you should see the following two lines in place:

Option Compare Database
Option Explicit

Just paste the whole of the above code below those lines. It will then be
inserted as a function, so you'll find it will move off the declarations area
to the new function. Save the module under a different name from the
function, e.g. mdlDateStuff. You can call it anything you like in fact, but
I'd suggest you keep the 'mdl' prefix as that way it won't get confused with
any other objects names.

Now in your query you just need to put:

Expected Completion Date: ECD([LLOOReqTime],[ LLOOReqDate])

The function is declared Public, which exposes it throughout the database,
so you can also call it in the same way in the ControlSource property of a
text box in a form or report if you wish.

Ken Sheridan
Stafford, England

Hi,

Thankyou so much. The function worked like a GEM. You guys are lifesavers.

One more question, as the database I am working on is an existing one and
there are already pre existing date. What I want to do is use a "isblank" so
that if there is no LLOOReqTime entered, the result would be blank, how can I
do that to incorporate into the function?

You might also like to try this:

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime] <= #12:00:00#,1 +
IIf(Weekday([LLOOReqDate],1) = 6,2,0),2 + IIf(Weekday([LLOOReqDate],1) =
6,1,0)),[LLOOReqDate])

It adds an extra 2 days to the 1 if its before 12;00 on Friday, or an extra
1 day to the 2 if its after 12:00 on Friday.

Ken Sheridan
Stafford, England

:

Hi,

I have a a function below in my query, where it either add 1 or two day/s to
the LLOOReqDate depending on when I receieve the request. The problem is if I
receive the requeston a Friday, then it will need to show that the expected
completion date should either be the next business day (i.e. Monday (1) or
the following Business day (i.e. Tuesday (2)).

Expected Completion Date: DateAdd("d",IIf([LLOOReqTime]<=#12:00:00
PM#,1,2),[LLOOReqDate])

Pls tell me how to do this. I am new at this and don't know anything about
VBA or coding.