Calculater query

[Celtic Queen]

OK,I'm really sorry ppl,I know u're gonna hate me and that its totally selfish of me,but cud 1 of u super-smart ppl please give me the code I need...
OK, so I've got a table that holds details of when a library member takes out a book. thing is, I need a query that calculates the date due back, which need to be the date 30 days on from the date in the 'date taken out' field (which is entered by users).
I also have a field that is 'the charge incurred by this item so far' which will be the number in the 'number of days overdue' field multiplied by €0.23.
Thank u xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

[Robert_Mc]

Hi

You can create a simple select query along the lines of this:

SELECT [Tablename].Book, [Tablename].[Date out], [Tablename].[Date Out]+30 AS [Date Due In], IIf(Date()>[date due in],(Date()-[date due in])*0.23,Null) AS [Overdue Charge]
FROM Test_Dateout;

I have selected the book from the main table but of course you can add other fields in the query as you need (I don't know the table so have assumed a few basics). The two important calculated fields above are [Date Due In] & [Overdue Charge].

Hope this helps

Regards

Robert

 

[Celtic Queen]

What I'm looking for is the criteria to write underneath in the criteria section of the query
Thanx.x

 

[Mile-O]

You haven't said what the criteria is...

 

[Celtic Queen]

Dats because I don't have any criteria!!! I just have the fields, date taken out, date due back, number of days overdue, and charge incurred by this item so far

 

[ColinEssex]

Just butting in here a moment - If you have a form where the DateBookOut field is filled in, why can't you have some Vb code OnExit of that field to populate the DateDueBack field.

Then when the ActualDateBack is filled in, if its > DateDueBack field flag up that its overdue and do a dateDiff and multiply by 0.23 to get the Total overdue amount.

I can't see why you need a query unless I'm missing something (which is quite likely)

Col

EDIT

I've just read Roberts reply underneath this one. You need the query to give you a list of all outstanding books (I think) - just ignore me, I'll go away eventually

 

[Robert_Mc]

Hi again

What I showed in the first reply was just the underlying SQL to generate the fields you require. You can add the "number of days overdue" (which I forgot) by adding

No of days Overdue: Date()-[Date_Duein]

as an expression in the Field line of the Desgn grid. This translates to

Date()-[Date_Duein] as [No of Days Overdue]

in the SQL view.

When youhave done this, you will have a dynamic query that gives the latest information on days overdue & overdue charges. You can then go to the Design grid criteria lines & enter any parameters you want to filter the data; for example, you could enter

>20 in the criteria field for No of days overdue

& the query will return only those books which are more than 20 days overdue. There are a lot of possibilities for filtering the base data using criteria.

Hope this helps, & my apologies for maybe confusing you with a hit of SQL straight off!

Regards

Robert

 

[Celtic Queen]

Oo0OO Rob!!! U iz a Mile-o-phile in the making!!!
Thank u!!!!!!!!!!!!! *mwah*

 

[Mile-O]

No of days Overdue: Date()-[Date_Duein]

As an aside, you can use the DateDiff() function to calculate differences in time much the same way as you can use the DateAdd() function to generate dates based on an initial date.

 

[Celtic Queen]

n what criteria wud I write 2 multiply the 'number of days overdue' by €0.23, oh,and wot wud I put to add 30 to the 'date taken out'??

 

[Mile-O]

n what criteria wud I write 2 multiply the 'number of days overdue' by €0.23, oh,and wot wud I put to add 30 to the 'date taken out'??

First thing - it's not criteria you are after, it's an expression.

Cost: [DaysOverdue] * 0.23


And to add 30 days to the date loaned:

DueBack: DateAdd("d", 30, [DateLoaned])