Ah, you also always have to have the ID in there... Add Select LoanID AS
'ID'
Select LoanID AS 'ID', Loans.LoanNumber, Constituents.DisplayName As
'Lender/Borrower', Loans.EndISODate as EndDate
from Loans inner join vConXrefs_Classic on
Loans.PrimaryConXrefID=vconxrefs_classic.ConXrefID
inner join Constituents on
vConXrefs_Classic.ConstituentID=Constituents.ConstituentID
where len(Loans.EndISOdate) = 10 /*if len is 10, date has year and month
and day*/
and datediff(week,getdate(),convert(datetime,loans.EndISODate)) between 0
and 8
*Stephanie Hansen*
Database Administrator / Assistant Registrar for Exhibitions & Loans
*Milwaukee Art Museum*
700 N. Art Museum Dr. | Milwaukee, WI 53202
Tel. 414.224.3273 | Fax. 414.271.7588 | [log in to unmask]
On Mon, Feb 24, 2014 at 2:58 PM, Sarah Gillis
<[log in to unmask]>wrote:
> Hmmm, I took out the last line, selected "Loans" for Modules, and pressed
> "save". It's not doing anything... Updated SQL:
>
>
>
> Select Loans.LoanNumber, Constituents.DisplayName As 'Lender/Borrower',
> Loans.EndISODate as EndDate
>
> from Loans inner join vConXrefs_Classic on
> Loans.PrimaryConXrefID=vconxrefs_classic.ConXrefID
>
> inner join Constituents on
> vConXrefs_Classic.ConstituentID=Constituents.ConstituentID
>
> where len(Loans.EndISOdate) = 10 /*if len is 10, date has year and month
> and day*/
>
> and datediff(week,getdate(),convert(datetime,loans.EndISODate)) between 0
> and 8
>
>
>
>
>
> *Best Regards,*
>
> Sarah Gillis | *Assistant Registrar, Image Management*
>
>
>
> WORCESTER ART MUSEUM / www.worcesterart.org<https://urldefense.proofpoint.com/v1/url?u=http://www.worcesterart.org/&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=1dd099a1a416afa9c5c1f9a19aaf8a729deb0bcad384e7eecf3ca5398833aff8>
> fifty-five salisbury street / worcester, massachusetts 01609
> 508.799.4406/ direct 508.793.4427 / fax 508.799.8638
>
>
>
>
>
> [image: Knights-Sig1]
>
>
>
>
> *****************************************************************************
>
> *Please contact Bridgeman or Davis Art Images for High Resolution Images
> for Publication Needs:*
>
> *Bridgeman Art Library
> <https://urldefense.proofpoint.com/v1/url?u=http://www.bridgemanart.com/en-US/collections/collection/worcester-art-museum/&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=488f8da2c2c07ed2d59e5fa037a987707212b4227641f31c40b109d6e733e3b0>*
>
>
>
> *Davis Art Images
> <https://urldefense.proofpoint.com/v1/url?u=http://www.davisart.com/Portal/DAI/DAIdefault.aspx?curPage%3DImageSearchResults%26SearchReq%3DDAI_AdvancedSearch%26navTitle%3DAdvanced%2BSearch%2BResults%26ReSearch%3Dslide_format-1%2Amuseum_id-496&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=e64cfbc87440307aa45f51d1cafe1270fe27cc83daf537571fcf2bc175a45ebc>*
>
>
>
> ***Temporarily Disabled Due to Higgins Integration***
>
> Our image reproduction application is now available online!
>
> Image Reproduction Request<https://urldefense.proofpoint.com/v1/url?u=http://www.worcesterart.org/Collection/collection_information.html&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=3d958a47db6010b99e9bc45fa041171bd45cf540828a8d0c32cfb5bb2275504e>
>
>
>
> Want to own your own custom reproduction of a Worcester Art Museum
> masterpiece? Visit our partner Rudinec & Assoc. today!
>
> RequestAPrint<https://urldefense.proofpoint.com/v1/url?u=http://www.requestaprint.net/worcester/index.php&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=57462aa1a82eaaf64fefc4c346b3794f1ce99cd176974bda8a036437406e9194>
>
> Introducing: Zazzle! Our online museum shop where you can purchase custom
> merchandise containing images from our permanent collection! Check it out
> today!
>
> Worcester Art Museum - Zazzle Shop<https://urldefense.proofpoint.com/v1/url?u=http://www.zazzle.com/worcesterartmuseum&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r4FGv2s0yKBRB9CFyYk65y5ynRIMPDMYAFVPFVCN%2FoI%3D%0A&s=4cf2b9b912b502d519dc3270124eb2ccdb824a1b844157f857c0005b7a73a6f1>
>
>
>
>
>
> *From:* The Museum System (TMS) Users [mailto:[log in to unmask]]
> *On Behalf Of *Hansen, Stephanie
> *Sent:* Monday, February 24, 2014 3:54 PM
> *To:* [log in to unmask]
> *Subject:* Re: Loan Alerts
>
>
>
> Hi Sarah,
>
> I think it's a problem to use the "order by" for alerts. Take that out and
> I bet it will work.
>
> Best,
>
> Stephanie
>
>
> *Stephanie Hansen*
>
> Database Administrator / Assistant Registrar for Exhibitions & Loans
>
> *Milwaukee Art Museum*
> 700 N. Art Museum Dr. | Milwaukee, WI 53202
> Tel. 414.224.3273 | Fax. 414.271.7588 | [log in to unmask]
>
>
>
> On Mon, Feb 24, 2014 at 2:49 PM, Sarah Gillis <
> [log in to unmask]> wrote:
>
> Hello TMS-ers:
>
>
>
> I have a question about an alert that was used in the Loan Module prior to
> 2012, which has stopped working. A previous co-worker wrote it and has
> since moved on to other projects. Below is the SQL code:
>
>
>
> Select Loans.LoanNumber, Constituents.DisplayName As 'Lender/Borrower',
> Loans.EndISODate as EndDate
>
> from Loans inner join vConXrefs_Classic on
> Loans.PrimaryConXrefID=vconxrefs_classic.ConXrefID
>
> inner join Constituents on
> vConXrefs_Classic.ConstituentID=Constituents.ConstituentID
>
> where len(Loans.EndISOdate) = 10 /*if len is 10, date has year and month
> and day*/
>
> and datediff(week,getdate(),convert(datetime,loans.EndISODate)) between 0
> and 8
>
> Order By Loans.EndISODate
>
>
>
>
>
> When I select "Loans" for the Module, it freaks out and doesn't like the
> last line of the SQL code. Can anyone help me or share with me one of
> their alerts that works well for them? I'd be in your debt!
>
>
>
> *Best Regards,*
>
> Sarah Gillis | *Assistant Registrar, Image Management*
>
>
>
> WORCESTER ART MUSEUM / www.worcesterart.org<https://urldefense.proofpoint.com/v1/url?u=http://www.worcesterart.org/&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=1faa6bd5c958ff712cb5ef8b12760d7e1f7db2cffbeac98f4bb9880aabecd59e>
> fifty-five salisbury street / worcester, massachusetts 01609
> 508.799.4406/ direct 508.793.4427 / fax 508.799.8638
>
>
>
>
>
> [image: Knights-Sig1]
>
>
>
>
> *****************************************************************************
>
> *Please contact Bridgeman or Davis Art Images for High Resolution Images
> for Publication Needs:*
>
> *Bridgeman Art Library
> <https://urldefense.proofpoint.com/v1/url?u=http://www.bridgemanart.com/en-US/collections/collection/worcester-art-museum/&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=0b6084b14a623e17a9cfc0003a251c0610309437e7331cf69b8b876ed805e614>*
>
>
>
> *Davis Art Images
> <https://urldefense.proofpoint.com/v1/url?u=http://www.davisart.com/Portal/DAI/DAIdefault.aspx?curPage%3DImageSearchResults%26SearchReq%3DDAI_AdvancedSearch%26navTitle%3DAdvanced%2BSearch%2BResults%26ReSearch%3Dslide_format-1%2Amuseum_id-496&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=7ce8b86e44040929afcdbee33874f99e119ae4440b42cff4abea08fca397d854>*
>
>
>
> ***Temporarily Disabled Due to Higgins Integration***
>
> Our image reproduction application is now available online!
>
> Image Reproduction Request<https://urldefense.proofpoint.com/v1/url?u=http://www.worcesterart.org/Collection/collection_information.html&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=893c62803f60278bc2691756b78d6ed8fe852947de1c59bf3340455aada584f2>
>
>
>
> Want to own your own custom reproduction of a Worcester Art Museum
> masterpiece? Visit our partner Rudinec & Assoc. today!
>
> RequestAPrint<https://urldefense.proofpoint.com/v1/url?u=http://www.requestaprint.net/worcester/index.php&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=479bb5038e0db9eedc92cb0d1c400e8f12419c6b85c58af2078347698b00f1a7>
>
> Introducing: Zazzle! Our online museum shop where you can purchase custom
> merchandise containing images from our permanent collection! Check it out
> today!
>
> Worcester Art Museum - Zazzle Shop<https://urldefense.proofpoint.com/v1/url?u=http://www.zazzle.com/worcesterartmuseum&k=diZKtJPqj4jWksRIF4bjkw%3D%3D%0A&r=XTJzpIcEHE%2Fdpcfy6DYLFGXNnGz7Bd9JKTihuOsgjts%3D%0A&m=r%2B6dy6srimyGaMhppaFYhnsKmvoguTxFHCk2Mchdz0I%3D%0A&s=10c3d5981a5e93a6be810e4ee341076a1817d50ffaa547051b12d31b7591b7d0>
>
>
>
>
>
> To unsubscribe, send an email to [log in to unmask] with the
> following commands in the body of the email:
>
> signoff TMSUSERS
>
> // eoj
>
> You will receive a confirmation that your subscription has been removed.
>
>
>
> To unsubscribe, send an email to [log in to unmask] with the
> following commands in the body of the email:
>
> signoff TMSUSERS
>
> // eoj
>
> You will receive a confirmation that your subscription has been removed.
> To unsubscribe, send an email to [log in to unmask] with the
> following commands in the body of the email:
>
> signoff TMSUSERS
>
> // eoj
>
> You will receive a confirmation that your subscription has been removed.
>
To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.