VBA Function Code

[Dano60]

Need to put on this public function an extra option on this date calculation function.



I'm a newbee with Access and coding.


Need to add that the earliest date return for termination date has to be june 15th of the current year.

Here is the date calculation function.

Public Function CalcDateTerminaison(ProduitID As Long) As Date
Dim rs As Recordset
Dim sqlString As String
Dim tempDate, DateExp As Date
Dim DateOK As Boolean
Dim count As Integer

DateOK = False

sqlString = "SELECT IIf(tbl_Commande.DateSignature<Max(tbl_Tache.DateF inReel),Max(tbl_Tache.DateFinReel),DateValue(tbl_C ommande.DateSignature))+56 AS DateTerminaisonCalc, tbl_Commande.DateExpedition " & _
"FROM (tbl_Commande_Produit LEFT JOIN tbl_Tache ON tbl_Commande_Produit.ID_CommandeProduit = tbl_Tache.ID_CommandeProduit) INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande " & _
"WHERE (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ((tbl_Tache.TypeTacheID) Is Null Or (tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID) Is Null Or (tbl_Tache.MachineID)=34)) " & _
"GROUP BY tbl_Commande.DateSignature, tbl_Commande.DateExpedition;"

Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If Nz(rs.Fields("[DateTerminaisonCalc]"), 0) <> 0 Then
   tempDate = rs.Fields("[DateTerminaisonCalc]")
Else
   tempDate = 0
   DateOK = True
End If

DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close

Do While DateOK = False
   sqlString = "SELECT tbl_DateConge.DateConge " & _
   "FROM tbl_DateConge " & _
   "WHERE (((tbl_DateConge.DateConge)=#" & tempDate & "#));"
   Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
   count = rs.RecordCount
   rs.Close
   If Weekday(tempDate) = 1 Or Weekday(tempDate) = 7 Then
       tempDate = tempDate + 1
   ElseIf count > 0 Then
       tempDate = tempDate + 1
   Else
       DateOK = True
   End If
Loop

If tempDate > DateExp Then
   CalcDateTerminaison = DateExp
Else
   CalcDateTerminaison = tempDate
End If

End Function

[June7]

Don't understand what you want. Exactly where do you need this date used?

Note that I modified your post to put code between CODE tags so indentation is retained and easier to read.

[Dano60]

This is part of an ordering database.

This calcDateterminaison is a function that return a termination date for a product to be manufacture. It choose the furthest date between either Date signature or from the furthest of two date in tbl_Tache.DateFinReel. When this is complete it will take the date and add 56 days to that date. Now what I want to add to that function is that after the 56 days are added to the furthest date, is no date prior to june 15th of the current year. ex: if the date return is April 3rd 2016 then I want the function to return June 15th 2016.

[June7]

Construct date:

CDate("6/15/" & Year(Date()))

And use it wherever you need in the function.

[Dano60]

I'm a newbee have no idea what ur talking about. please put it in my code

[June7]

Where did your code come from?

I am not sure where it should go. What is the DateExp variable for? It is declared but never set with a value. Maybe you should use the suggested expression in place of the DateExp variable.

If tempDate < CDate("6/15/" & Year(Date()))Then
   CalcDateTerminaison = CDate("6/15/" & Year(Date()))
Else
   CalcDateTerminaison = tempDate
End If

[Dano60]

Hi,

This code was done by a previous employee that can not be found anymore.

DateExp is in the code :
DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close
DateExpedition is a date entered in the order.

Do I have to declare anything for the code that you wrote and were.

I also need to kept the dateExp variable. Can you please write it for me?

Thank you very much for your help.

Dan

[June7]

Okay, sorry I missed it.

Nothing needs to be declared. And DateExp variable is no longer needed. Or you can set the DateExp variable to the suggested CDate() expression and not change the original If Then. So I am not sure if the suggested edit is correct. Why would the DateExp value need to be in the code to begin with? Why was it used in the conditional statement?

You need to understand the code logic before you go changing it.

[Dano60]

When filling the order form. There are important date such as date signature which is date the contract was approuved and then you have date terminaison which is the date that we want to end the production of that item ( usually 56 days of production but could be done faster but not ideal) then we have a date of expedition which sometime does not give us 56 days of production time. The shop open on April 15 therefore the first delivery date being June 15th

Now these order form are filled by the salesmen outside the office and are not very good at respecting our wishes.

So order from approuval of contract or delivery of the signed contract ( which is the Max(tbl_Tache.DateFinReel) ) but if the date calculate is before June 15th then return June 15th or if date return is bigger then the expedition date then return expedition date.

Hope I set it right this time.

Thanks

[June7]

By 'bigger' you mean later, more recent?

Still not sure what you want. As best as I can interpret from your last post, maybe:

If tempDate < CDate("6/15/" & Year(Date())) Then
   CalcDateTerminaison = CDate("6/15/" & Year(Date()))
ElseIf tempDate > DateExp
   CalcDateTerminaison = DateExp
Else
   CalcDateTerminaison = tempDate
End If

[Dano60]

For what I read it sound right.

Will try this in the beginning of January and let you know.

Thanks very much.

Have happy and safe holidays.

Thanks again.

Dan ;-)

[Dano60]

Hi,

Tried the function did not work

If tempDate < CDate("6/15/" & Year(Date)) Then
CalcDateTerminaison = CDate("6/15/" & Year(Date))
ElseIf tempDate > DateExp
CalcDateTerminaison = DateExp
Else
CalcDateTerminaison = tempDate
End If

The section in bold red was highlighted and a Microsoft Visual Basic window pop-up appear saying compilation error and syntax error.

Help please

[June7]

What are the values of tempDate and DateExp when this error occurs? Review link at bottom of my post for debugging guidelines.

I don't see any syntax error.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Dano60]

Could it be because of this? Cannot send database as table are link to server that we don't have access.


Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If Nz(rs.Fields("[DateTerminaisonCalc]"), 0) <> 0 Then
tempDate = rs.Fields("[DateTerminaisonCalc]")
Else
tempDate = 0
DateOK = True
End If

DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close

[June7]

Still don't see error.

Only possible cause for error I can see is tempDate is not explicitly declared as a date value.

Dim tempDate, DateExp As Date

only declares DateExp as Date, tempDate defaults to Variant.

Dim tempDate As Date, DateExp As Date

now tempDate is explicitly declared


You would have to make a copy of db, delete the links and import data to local tables.