Someone Please Assist me with Expression ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gonzal - 21 Apr 2008 18:16 GMT

Ok, I worked on this for 2 hours last night! I needs some assistance, any
would be greatly appriciated. I am new to Access and trying to work through
it to understand it.

The Problem: I need to Create a query that lists overdue items, the person
borrowing them, and computes the amount of the fine due. The fine will be
created using the following calculation:
Fine = $1 + days overdue*1% of the Value of the item. Name this query:
qryOverdue. Note: The days overdue can be computed by subtracting the date
due from the date returned. You do not need to include items that have not
yet been returned.

Well I am doing something wrong oviously but just cant pin point it. I am
typing in FineDue: then clicking on the "Builder" option I then attempt to
put in the formula given and it will never work. it looks a little something
like this when I have the formula all set up

FineDue:1+[tblLending]![DateReturned]-[tblLending]![DateDue]*.01[tlbItems]![Value]

I think I am not doing the last part right which is the "Value" part. I take
it I cant type in *.01"of the value"

In my Query table I have listed the following in the colums:
ItemID
DateDue
DateReturned
FirstName
LastName
Value
I have 3 tables I have included in the query:tblItem, tblLending, and
tblBorrowers
The DateDue and Date Returned is from the Lending table then the first and
last name from the Borrowers table.
I hope I didnt confuse you in reading this!

Reply to this Message

Marshall Barton - 21 Apr 2008 19:09 GMT

>The Problem: I need to Create a query that lists overdue items, the person
>borrowing them, and computes the amount of the fine due. The fine will be
[quoted text clipped - 25 lines]
>The DateDue and Date Returned is from the Lending table then the first and
>last name from the Borrowers table.

You are missing some parenthesis and a multiply. The square
brackets are optional with your table and field names so I
didn't bother with them. Also, you should use dot instead
of exclamation between the table and field names.

I think this should be close:

FineDue:1+(tblLending.DateReturned-tblLending.DateDue)*.01*tlbItems.Value

Signature

Marsh
MVP [MS Access]

Reply to this Message

Gonzal - 21 Apr 2008 19:46 GMT

Thank you very much for responding, I will check it out!

> >The Problem: I need to Create a query that lists overdue items, the person
> >borrowing them, and computes the amount of the fine due. The fine will be
[quoted text clipped - 34 lines]
>
> FineDue:1+(tblLending.DateReturned-tblLending.DateDue)*.01*tlbItems.Value

Reply to this Message

Steve Sanford - 22 Apr 2008 10:56 GMT

Another problem might be that you have a field named "Value". "Value" is a
reserved word and shouldn't be used for object names. Also it is not very
descriptive... "ItemValue" might be a better name.

For a list of reserved words in Access, Jet, MS Query and SQL Server, see

http://allenbrowne.com/AppIssueBadWord.html

HTH

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> Thank you very much for responding, I will check it out!
>
[quoted text clipped - 36 lines]
> >
> > FineDue:1+(tblLending.DateReturned-tblLending.DateDue)*.01*tlbItems.Value

Reply to this Message