Count query (loosing my mind)

[Monzer]

Hope someone could help me with this! In my customertbl, I have 2 fields that i use to run my query..
[OrderDate] & [BillingDate]. First payment is due 14 days from[OrderDate]and this will automatically update[BillingDate]with Date().From there I bill a customer every 30 days from the [BillingDate]at the same time that same feild gets updated every time i bill a customer.
Now,I would like to run a query the counts how many times each customer should have been billed. Is this possible!!!
I tried everything.. Thanks in advance.

 

[Steve101]

Try adding the following expression to your query. I think it should do the trick.

[tt]
iif(DateDiff("d", OrderDate, BillingDate)>=14,1,0) + DateDiff("d", OrderDate+14, BillingDate) \ 30
[/tt]


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Monzer]

Thank you for you response Steve101. I keep getting an error msg "You tried to execute a query that dose not include the specified expression as part of an aggregate function" ANY HINTS..
Thanks

 

[Steve101]

If you post the query SQL, then it will be easier to find the cause of the problem.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Monzer]

This is the SQL statement
SELECT tbl.[Customer Phone], tbl.[Call Date], IIf(DateDiff('d',[Call Date],[BillingDate])>=14,1,0)+DateDiff('d',[Call Date]+14,[BillingDate])\30 AS NumberOfCharges
FROM tbl;
It results in an error, any suggestions please.
Thank you

 

[Steve101]

I've mocked up a simple table, and applied your sql and it works fine .... providing:

(a) The two date fields in the table are defined as Dates, and
(b) The field values for the two dates are entered as valid dates.

If you have defined the date fields as Text fields, then you will need to surround their respective references in the query with the CVDate function (convert to date); for example, [Call Date] above would become CVDate([Call Date]).

Notwithstanding the above, it should work. Let me know.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Monzer]

Thank you very much Steve. It works fine.