Problem with Weekdays function

[dazstarr]

Hi

I require some help please.

I have a access database with a module which works out a certain date 20 days from a day in a field.

The module adds a function called AddWkDays which I call upon in a query. Below is the line in the query:

AdmitBy: AddWkDays([DateReceivedForm],20,"17")

This works out 20 days from the date entered in the field [DateReceivedForm].

The problem I have is when I open the form based on this query i get a run time error if no date is entered in this field.

How can i get the function to ignore the calculation if the date is blank?

Many thanks in advance
Dazstarr

 

[namliam]

What is your exact error and what part of the function is failing??

Perhaps you can just add to your function (as your first line)
If isnull(inputteddate) then exit function
and be done with it.

 

[namliam]

I found your previous thread...
http://www.access-programmers.co.uk/forums/showthread.php?t=153774

It is ussually (like in this case) to ask followup questions in the same thread so that we all know where things belong and how they started and followed up, etc...

But I was right, you can use the IF Isnull() thing in the function as the first line after the Dim's

 

[dazstarr]

Hi

The exact error I get is:

Run time error 94

Invalid Use of Null.

Below is the full module:

******************************************************
Function AddWkDays(varDate As Variant, numdays As Integer, _
Optional pexclude As String = "17") As Date
Dim thedate As Date, n As Integer, incl As Boolean
thedate = DateValue(varDate)
incl = False
Do While InStr(pexclude, Weekday((thedate) + 1)) > 0
thedate = thedate + 1
incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
If InStr(pexclude, Weekday(thedate)) = 0 Then
n = n + 1
End If
If n = numdays Then Exit Do
thedate = thedate + 1
Loop
AddWkDays = thedate
End Function
*****************************************************

When I press debug it takes me to the line highlighted above in red.

Hope this helps
Many thanks for your response.

 

[dazstarr]

Hi

Thanks for your response.

The previous thread was a different function that I required help on a while ago so didnt continue that one.

I will give it a try. Fingers crossed!!

Many Thanks again

 

[dazstarr]

Hi

I tried what you advised me:
*****************************************************
Function AddWkDays(varDate As Variant, numdays As Integer, _
Optional pexclude As String = "17") As Date
Dim thedate As Date, n As Integer, incl As Boolean

If IsNull(DateReceivedIYFAForm) Then
Exit Function

thedate = DateValue(varDate)
incl = False
Do While InStr(pexclude, Weekday((thedate) + 1)) > 0
thedate = thedate + 1
incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
If InStr(pexclude, Weekday(thedate)) = 0 Then
n = n + 1
End If
If n = numdays Then Exit Do
thedate = thedate + 1
Loop
AddWkDays = thedate
End Function
*****************************************************

But I got the following error:

Complie error

Variable not defined.

Please help me, I am not very experienced at programming.

Many Thanks

 

[namliam]

Please use [ code ] and [/ code ] without the spaces around your code when you post code on the forum...

Still the code came from that thread.... or atleast it looks VERY simular!

It is either all on one line like so:
If IsNull(DateReceivedIYFAForm) Then Exit Function
Or you need to add an End If line to each if you have...

If IsNull(DateReceivedIYFAForm) Then 
    Exit Function
end if

(see what the [ code ] tags do??)

 

[dazstarr]

Hi

I have tried both variants you suggested but still get the 'variable not defined error'.

Am I correct in editing this directly in the module?

Thanks

 

[MSAccessRookie]

I am not a VB Expert, but I reformatted the code and I think that something (things) might be missing. I hope I got the intent correct

Function AddWkDays(varDate As Variant, numdays As Integer, _
[COLOR=paleturquoise]...[/COLOR]Optional pexclude As String = "17") As Date
[COLOR=paleturquoise]...[/COLOR]Dim thedate As Date, n As Integer, incl As Boolean
 
[COLOR=black][COLOR=paleturquoise]...[/COLOR]If IsNull(DateReceivedIYFAForm) Then
[COLOR=paleturquoise]......[/COLOR]Exit Function
[COLOR=red][COLOR=paleturquoise]...[/COLOR][B]End If[/B][/COLOR]
 
[/COLOR]
[COLOR=paleturquoise]...[/COLOR]thedate = DateValue(varDate)
[COLOR=paleturquoise]...[/COLOR]incl = False
 
[COLOR=paleturquoise]...[/COLOR]Do While InStr(pexclude, Weekday((thedate) + 1)) > 0
[COLOR=paleturquoise]......[/COLOR]thedate = thedate + 1
[COLOR=paleturquoise]......[/COLOR]incl = True
[COLOR=paleturquoise]...[/COLOR]Loop
 
[COLOR=paleturquoise]...[/COLOR]thedate = thedate + IIf(incl = False, 1, 0)
[COLOR=paleturquoise]...[/COLOR]n = 0
 
[COLOR=paleturquoise]...[/COLOR]Do While n < numdays
[COLOR=#ffffff][COLOR=paleturquoise]......[/COLOR][/COLOR]If InStr(pexclude, Weekday(thedate)) = 0 Then
[COLOR=paleturquoise].........[/COLOR]n = n + 1
[COLOR=paleturquoise]......[/COLOR]End If
 
[COLOR=paleturquoise]......[/COLOR]If n = numdays Then 
[COLOR=paleturquoise].........[/COLOR]Exit 
[COLOR=paleturquoise]......[/COLOR][B][COLOR=red]End If[/COLOR][/B]
 
[COLOR=paleturquoise]......[/COLOR]Do
[COLOR=paleturquoise].........[/COLOR]thedate = thedate + 1
[COLOR=paleturquoise]......[/COLOR]Loop
 
[COLOR=paleturquoise]......[/COLOR]AddWkDays = thedate
[COLOR=paleturquoise]...[/COLOR][B][COLOR=red]Loop[/COLOR][/B]
End Function

 

[namliam]

*UHM*

Offcourse your "DateReceivedIYFAForm" will not be known in the function. You need to use the date field that is inside the function!!!!
You pass your "DateReceivedIYFAForm" date into the function, then inside the function it is known as "varDate"!

 

[dazstarr]

Module Problem

Namliam - no need to be arrogant - as I explained I am not an experienced programmer.

Is there anyone else more helpful and less pompous that can help please.

Many Thanks in advance.

 

[dazstarr]

Sorted

I have got this working now.

Thanks for your help.

 

[MSAccessRookie]

I have got this working now.

Thanks for your help.

What was the solution to your problem? I ask this in case your solution is able to help another user with a similar problem.

 

[dazstarr]

Solution

Hi

I added the following to my code

Function AddWkDays(varDate As Variant, numdays As Integer, _
Optional pexclude As String = "17") As Date
Dim thedate As Date, n As Integer, incl As Boolean
 
[B][COLOR=red]If IsNull(varDate) Then
Exit Function
End If[/COLOR][/B]
 
thedate = DateValue(varDate)
incl = False
Do While InStr(pexclude, Weekday((thedate) + 1)) > 0
  thedate = thedate + 1
  incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
   If InStr(pexclude, Weekday(thedate)) = 0 Then
      n = n + 1
   End If
   If n = numdays Then Exit Do
   thedate = thedate + 1
Loop
AddWkDays = thedate
End Function

Hope this helps!!

Thanks again for your help!

 

[namliam]

no need to be arrogant... less pompous

Er.. ???

This was meant towards myself... not towards you... I should have noticed that slight error on the previous post...

and "more helpfull" ?? Guess spending 5 posts reading your questions and finding the answers for you was NOT beeing helpfull???

Talk about arrogant and pompous !

 

[dazstarr]

Reply

I appreciate your help, but maybe you needed to make it more obvious that you were referring to yourself rather than me.

If you re-read your comments, you will see that the way it was written implies you were referring to me.

Thanks for your help anyway.

 

[namliam]

Well I was talking to you... in regards of what you need to do... but I was pissed at myself for not spotting that earlier....

I didnt mean offense and appologize if I offended you

 

[dazstarr]

No problem

No problem. I greatly appreciate your expertise.

Thanks again.