Last post Mar 20, 2010 07:50 AM by hans_v
Mar 20, 2010 03:00 AM|asikrifai|LINK
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')
Mar 20, 2010 05:19 AM|sourabh3120|LINK
Are you sure that it is "and" before Appoint.Date = ('03/21/2010') Or it should be "Where"..
Mar 20, 2010 06:59 AM|asikrifai|LINK
You are right!!!
Mar 20, 2010 07:50 AM|hans_v|LINK
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.