DateDiff & 30 days

[James]

I'm trying to add a field to a query that would find the difference between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.

 

[KARL DEWEY]

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

 

[Michel Walsh]

You give the two dates, you don't subtract them, yourself.

The earliest date is the first one.

Unless you want fractional intervals, use INT, or other integer-rounding.

Try:

Serv_ch: (((Int( DateDiff("d", [OrderDate], Now() ))/30)-1)*.015)



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

You probably meant integer division, \


Vanderghast, Access MVP

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

--
KARL DEWEY
Build a little - Test a little

I'm trying to add a field to a query that would find the difference
between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.

 

[James]

Your line using Mod throws a "wrong number of arguments" error on save.

Michel Walsh's line is accepted without error, but I'm looking to see why it
returns no result.

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

--
KARL DEWEY
Build a little - Test a little

I'm trying to add a field to a query that would find the difference
between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.

 

[James]

Correction, result IS visible in query.
Not making it to my report. Its Me.
Sorry.

Your line using Mod throws a "wrong number of arguments" error on save.

Michel Walsh's line is accepted without error, but I'm looking to see why
it returns no result.

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

--
KARL DEWEY
Build a little - Test a little

I'm trying to add a field to a query that would find the difference
between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.

 

[James]

I have a follow up question relating to this...

When my number of days is less than 30, I'm trying to set the Serv_ch value
to 0 or 1

as follows:
Serv_ch: IIf(DateDiff("d",[OrderDate],Now()))>30
(((Int(DateDiff("d",[OrderDate],Now()))/30)-1)*0.015), 0

I'm getting a "wrong number of arguments though, any suggestions?

Correction, result IS visible in query.
Not making it to my report. Its Me.
Sorry.

Your line using Mod throws a "wrong number of arguments" error on save.

Michel Walsh's line is accepted without error, but I'm looking to see why
it returns no result.

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to add a field to a query that would find the difference
between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current
date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.

 

[James]

I think I have it working, counting the number of 30 day periods after the
initial 30 days from OrderDate.
If its less than 30 days, it sets my Serv_ch =0

Serv_ch:
IIf(DateDiff("d",[OrderDate],Now())>30,(((Int(DateDiff("d",[OrderDate],Now()))/30)-1)*0.015),0)

I have a follow up question relating to this...

When my number of days is less than 30, I'm trying to set the Serv_ch
value to 0 or 1

as follows:
Serv_ch: IIf(DateDiff("d",[OrderDate],Now()))>30
(((Int(DateDiff("d",[OrderDate],Now()))/30)-1)*0.015), 0

I'm getting a "wrong number of arguments though, any suggestions?

Correction, result IS visible in query.
Not making it to my report. Its Me.
Sorry.

Your line using Mod throws a "wrong number of arguments" error on save.

Michel Walsh's line is accepted without error, but I'm looking to see
why it returns no result.

Better to use Mod function.

Serv_ch: ((DateDiff("d",Now() - [OrderDate])Mod 30)-1) *.015

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to add a field to a query that would find the difference
between
two dates and some math like:

Serv_ch((( DateDiff("d",Now() - [OrderDate]))/30)-1)*.015)

To derive the number of 30 day intervals from OrderDate to Current
date,
subtract 1, then multiply the remaining integer * 0.015
My syntax has an error I can't resolve.

Any suggestions appreciated.