DateDiff and some operations with it

[Eugenios]

Hello everyone. I've recieved wonderful help last time when i turned to this forum, now i have another problem and hoping you will help a beginner again.

I have a table with containers. Each container has an arrival date. I am trying to make a function using DateDiff to calculate the difference between the arrival date and todays date to implement further action.

If the difference is > 31 (month) one calculation follows
If the difference is <31 (month) another calculation follows

I've tried,for now,wihtout any calculations, simply to retrieve the difference from the table, but so far without any luck.

Dim qSQL As String
qSQL = "SELECT DateDiff('d',[Depot In Date], Now()) FROM tblContLife "

Is this the correct statement to use? And what are the options of outputing all the DateDiff values so i could make the "if >31 or <31" statements ?

Thank you in advance.
Best regards, Eugene.

 

[PWise]

where are you running this?

try running this in a query grid it should work

 

[Eugenios]

I'm trying to run this from an independant form. It is easy to run it in a query, but I need a a function in vba that will make calculations based on the datediff values ;(

Am i getting this through ? ;(

 

[Golom]

It's not really clear where you are going with this. Your code is SQL that retrieves information from a table but you state that you want a VBA function to do some calculations.

You have two choices about how to do that

Choice 1: Use a Recordset
Pull the records from your SQL into a recordset and then loop through the rows in the recordset, calling the function for each of them.

Choice 2: Call the Function from SQL
Place the function call in the SQL, generating other fields to contain the results being returned from the function.

Not knowing what your final outcome should be, it is difficult to say which alternative would work better.

 

[Eugenios]

right guys, thanks for the great help, managed to do it with this function:

DepotFee: IIf(DateDiff('d',[Depot In Date],Date())>31,31*[Storage Fee/Day],DateDiff('d',[Depot In Date],Date())*[Storage Fee/Day])

Have a little problem left. On my input form I have a combobox to input the [Storage Fee/Day] , it's two options 0,40 and 0,80. When selected these are represented as £1 or £2 in the table. any idea why ? ;(

 

[Eugenios]

solved.