weekday counter

[Homegrownandy]

Nothing seems to be working today so I apologise for the frequency of questions on here.



I've been looking at the following two links:

https://msdn.microsoft.com/en-us/lib...or=-2147217396
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

I've tried to use the first one in a test environment and all I get back is #name?

nothing I try generates a result. How can I troubleshoot this further? I've checked all the names are correct and changed some test data so far. I don't know what else to try.

[JoeM]

I've tried to use the first one in a test environment and all I get back is #name?

That usually means that it is unable to find the User Defined Function you are calling.
Where exactly are you placing the VBA code for the User Defined Function? In which Module?
Them, please post the formula you are creating that is trying to use it.

[Homegrownandy]

location: My new app > modules > work_days.
Name: Work_days

Code:

function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
 
 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer
 
 On Error GoTo Err_Work_Days
 
 BegDate = DateValue(BegDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", BegDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, BegDate)
 EndDays = 0
 
 Do While DateCnt <= EndDate
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop
 
 Work_Days = WholeWeeks * 5 + EndDays
 
Exit Function
 
Err_Work_Days:
 
 ' If either BegDate or EndDate is Null, return a zero
 ' to indicate that no workdays passed between the two dates.
 
 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
 Else
' If some other error occurs, provide a message.
 MsgBox "Error " & Err.Number & ": " & Err.Description
 End If
 
End Function

control source of textbox on form: =Work_Days([first],[second])

this is different code again from the two links I posted earlier but the situation is the same.

[JoeM]

I re-created it on my side, and it seems to work for me.
If you go to your VB Editor and click "Compile" under the "Debug" menu, do you get any errors?

One option you can do is upload your database for analysis. I can't download it from my current location (but can later tonight or others may jump in and help).
If you do that, just be sure to remove any sensitive data first.

[Homegrownandy]

My New App.zip

I cant click compile. I'm sure its me being stupid doing something wrong. I just cant see what it is. If you're able to tell me what's wrong that would be great. (no rush ill be out the office till Monday soon enough)

[JoeM]

OK. One more quick thing to check (if you cannot Compile).
Are Macros/VBA enabled on your computer?
If not, I could see how you are getting these type of errors.

[Homegrownandy]

Yes mate, although I don't know how to check; I use them all the time with no issue.

Thanks for the responses so far.

[June7]

What is name of the module?

Function and module cannot have the same name.

[JoeM]

Yep, June hit it on the head. Your Module and Function are named the same thing. I changed the name of your module to "Module2" and it worked like a charm!

[Homegrownandy]

to be honest I don't know the difference but thanks for the answer. Ill change it and see

[Homegrownandy]

Sorted! I could have sworn I tried it before I changed the name though. Anyways, thanks!

Is there a way to apply this to a table rather than a form?

It returns the value I need in the form, but I want to keep this information in a field in the table. So either I return values directly to the field (which makes more sense to me). Or I have the data moved on an event. What would you suggest?

[JoeM]

One should never store values which can be calculated in a table - it goes against the Rules of Normalization and can ruin database integrity.
Newer version of Access allow you to do calculations in a table, but other relational database programs (like SQL do not), because it goes against the nature of relational databases.

You can do the calculations in a query, and base your Forms/Reports on those queries.

[Mahendra1000]

Dear Andy,

Few months back I got the same problem and had a discussion over here with few experts and I got the answer "NO". This is the reason I was explained "We should not store the calculated values from form to table as it is against the Normalization rules and we need to calculate it in query whenever we need it."

Thanks

[Homegrownandy]

Okay I appreciate your answers, but this leads me to another potential problem. I want events to happen based on the number generated.

The number represents how many working days notice we were given for cancelations. If its 1 we charge for the day and if its 2 we charge for half a day. Anything more than 2 wont have a charge.

If it needs to have a charge applied I would want the database to apply that charge automatically. Currently we spend a long time looking through history of jobs (from several spreadsheets) to accumulate the costs. I see this as wasted time and I want to eliminate it as much as possible.

Suggestions appreciated.

[June7]

Whether or not to show the charge would be another calculation.

I have a db that violates many 'rules'. It is for processing laboratory test data. The calculations in the db are a convenience so the techs don't have to do manual calcs. We save the calc results to table. The primary purpose of this db is to publish the results to a report for distribution. Essentially, results become the 'raw' data. We do very little actual analysis of the data, just publish it. We don't want the results to be altered if test formula is modified. Rarely happens but there are constants in formulas that get recalibrated periodically and that can also alter results. Simplest solution for us is to save the calc.

However, none of these calcs are aggregations of records. I have always advised to never save aggregate calcs.

My point is, in my view it is a balancing act between normalization and ease of data entry/output.