locked
Query in Access RRS feed

  • Question

  • User1982845672 posted

    Below is my query for Access.. Which it is saying Error Message ( Join expression Not Supported )

    Can anyone sort my problem!!!


    SELECT PatientMst.Name AS [Patient Name],REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,Appoint.Time,100),7)),7),'AM',' AM'),'PM',' PM') AS [Time],Appoint.Ptid,Appoint.Aptid,replace(convert(varchar,Appoint.Date,106),' ','-') as [Date] FROM PatientMst INNER JOIN Appoint ON PatientMst.Ptid = Appoint.Ptid and Appoint.Date = ('03/21/2010')


    Saturday, March 20, 2010 3:00 AM

Answers

  • User1353953242 posted

    Are you sure that it is "and" before Appoint.Date = ('03/21/2010') Or it should be "Where"..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 20, 2010 5:19 AM
  • User1982845672 posted

    Got it!


    You are right!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 20, 2010 6:59 AM

All replies

  • User1353953242 posted

    Are you sure that it is "and" before Appoint.Date = ('03/21/2010') Or it should be "Where"..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 20, 2010 5:19 AM
  • User1982845672 posted

    Got it!


    You are right!!!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 20, 2010 6:59 AM
  • User-1199946673 posted

    OK, you have been told the error, but looking to your query I do have some remarks. First of all, I noticed that you store the date and time of an appointment in two fields. Unless there's is a good reason to do otherwise, ussualy it's easier to strore this in 1 field.

     Althought it is possible to perform formatting in SQL, ussualy you better retrive the data unformatted form the database. In this case, you would retrieve a date and time (preferably 1 datetime hwoch stores both date and time), on hoch you can do any calculatuons. On the page where you want to show the value, you can do the formatting. You retrieve to strings from the database, not datetime values. For example, if you want to do any sorting in a gridview on the date, you'll get the worng order when you have dates in different years, because the sort is done on you string value, rather than on the underlying date.

    Saturday, March 20, 2010 7:50 AM