Hi,
1st: QUERY #1
- This should not be in a textbox control (or query column):
Expr1: (SELECT * FROM [Loan] WHERE [Loan Date] Is Null)
The statement "SELECT * FROM [Loan] WHERE [Loan Date] Is Null" would be the
Query. Would have a Form with it's RecordSource based on this Query.
2nd: QUERY #2
- Use an update query that would increment the field "RENEWED" by one.
Here's an example of the SQL Syntax for the Update Query
UPDATE [table name] SET [table name].[RENEWED] = [table
name].[RENEWED]+1;
3rd: QUERY #3
- have a query that would return the records where it was renewed 2x or more
Here's the SQL Syntax (same as before)
SELECT * FROM [your table name] WHERE [RENEWED] >= 2;
4th:
DaysLate: DateDiff("d",[Loan Due],[Loan Returned])
Now this would be included in a textbox control on your Form.
SUMMARY:
You would have 3 separate queries
- QUERY #1 - to find records that have a no Loan Date
- QUERY #2 - update query to increment the RENEWED field. When
you call it to run and on what records you decide. For example you can
place a criteria in the Update Query
- QUERY #3 - select only the records where RENEWED is equal to or
greater than 2
Now if you are trying to get ALL of the above in one Form then you got your
work cut out for you. Ideas would be using SubForms for QUERY 1 & 3 and
running QUERY 2 from a macro that would be called from a command button.
Just some ideas!!
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Content-Class: urn:content-classes:message
| From: "Book Man" <
[email protected]>
| Sender: "Book Man" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| Subject: RE: Creating expressions
| Date: Tue, 30 Mar 2004 03:45:07 -0800
| Lines: 147
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQWTHL1sXltGPKST0CEEDzbOjiifQ==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:195744
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi I entered the expression you gave me into the field:
|
| Expr1: (SELECT * FROM [Loan] WHERE [Loan Date] Is Null)
|
| and it says that there is a problem: "You have written a
| subquery that can return more than one field without
| using the EXISTS reserved word in the main query's FROM
| clause. Revise the SELECT statement of the subquery to
| request only one field." What does this mean? What do I
| change the expression to?
|
| Cany ou also explain more clear how I do this:
|
| Use an update query that would increment the
| field "RENEWED" by one. Then
| >have a query that would return the records where it was
| renewed 2x or more
| >
| >SELECT * FROM [your table name] WHERE [RENEWED] >= 2;
|
| How do I make the updtae query?
|
| Can you give me the expressions which I can use in Design
| View eg.
| DaysLate: DateDiff("d",[Loan Due],[Loan Returned])
| [This type of format] SQL is confusing me.
|
| >-----Original Message-----
| >Hi,
| >
| >Here's SQL Syntax that could be the RecordSource for
| your Form "Books
| >Available":
| >
| > SELECT * FROM [your table name] WHERE [Loan Date]
| Is Null;
| >
| >I'm not sure about the "renewed more than twice by the
| same student" part.
| >If you have a field in your table that records the renew
| times maybe.
| >Example:
| >
| >Student Name
| >Book Number
| >Loan Date
| >Loan Due
| >Loan Returned
| >Days Late
| >Fine Amount
| >RENEWED
| >
| >Use an update query that would increment the
| field "RENEWED" by one. Then
| >have a query that would return the records where it was
| renewed 2x or more
| >
| > SELECT * FROM [your table name] WHERE
| [RENEWED] >= 2;
| >
| >I hope this helps! If you have additional questions on
| this topic, please
| >respond back to this posting.
| >
| >
| >Regards,
| >
| >Eric Butts
| >Microsoft Access Support
| >
[email protected]
| >"Microsoft Security Announcement: Have you installed the
| patch for
| >Microsoft Security Bulletin MS03-026? If not Microsoft
| strongly advises
| >you to review the information at the following link
| regarding Microsoft
| >Security Bulletin MS03-026
| ><
http://www.microsoft.com/security/security_bulletins/ms0
| 3-026.asp> and/or
| >to visit Windows Update at
| <
http://windowsupdate.microsoft.com/> to install
| >the patch. Running the SCAN program from the Windows
| Update site will help
| >to insure you are current with all security patches, not
| just MS03-026."
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights
| >
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| Wrom: SWZIDREXCAXZOWCONEUQZAAFXISHJEXXIMQZUIVOT
| >| Sender: "Book Man" <
[email protected]>
| >| Subject: Creating expressions
| >| Date: Mon, 29 Mar 2004 08:04:05 -0800
| >| Lines: 24
| >| Message-ID: <
[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE
| V5.50.4910.0300
| >| Thread-Index: AcQVp3X1ewghvZ8UT3efzpEThkuysg==
| >| Newsgroups: microsoft.public.access.queries
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.access.queries:195639
| >| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| >| X-Tomcat-NG: microsoft.public.access.queries
| >|
| >| HI there, I currently have a database which records
| book
| >| Loans.
| >|
| >| I am creating a Loan query which currently has the
| >|
| fields:
| >|
| >| Student
| Name
| >| Book Number
| >| Loan Date
| >| Loan Due
| >| Loan Returned
| >| Days Late
| >| Fine Amount
| >|
| >| What expression or form of validation could I use so
| only
| >| books not on loan can be borrowed.
| >|
| >| and
| >|
| >| What expression could I use to prevent a book from
| being
| >| renewed more than twice by the same student.
| >|
| >| Thanks
| >|
| >|
| >
| >.
| >
|
