1st image is the app using SQL Server and the 2nd image is the MS Access Db app.

The above image seems to suggest that when I uploaded the table to MS SQL Server the Primary key wasn't created.  As a Newbie using MS SQL Server if it hasn't created the Primary Key how do I create it?

I have got into MS SQL Server Studio and created the Primary Key for each of the Tables but the result is exactly the same.  If I look at the tables and the queries in Datasheet view it looks correct.

As a trial I went to the row source for the unbound box which displays the course information and adjusted the day number -2 and that then displays the data correctly for the right day. However that would mean I start the 1st Sunday in the Calendar as Startdate-2 which is stupid.

Just a couple things off the top of my head first did you make sure that the system clock date is set properly on the SQL Server? Is this server something you have on premises or is it an online server at a web host? Check the date it could be wrong. Another thing is make sure that you're handling dates properly for SQL Server. In access you have to enclose them in pound symbols but in SQL Server you have to enclose them inside of single quotes.

SELECT * FROM CustomerT WHERE AccessDate = #1/1/90#
SELECT * FROM CustomerT WHERE SQLServerDate = '1/1/90'

Aside from that without actually sitting down and playing with your database I don't know if I can help fix it. I don't see anything wrong with what you showed me.

To answer your question primary keys in SQL Server are called identities. So set the identity property to true for that field.

Hi Richard,  Thanks for your response.  I have checked our in-house SQL Server and the system date is correct.  I also checked that the Identity for the Primary Key is set to True and it is!  My Select statement in the Unbound field is:

SELECT [ClusterDateID], [Location], [CourseCode], [ClusterCode], [Group], [ClusterDate], [CountofVTStudentCode] FROM CalendarQ WHERE ClusterDate = Forms!DatePickerF!StartDate+1 ORDER BY [ClusterDate] DESC , [Location];

This Select statement from the 2nd across, 1st row of the Calendar as it has some data in it.

Any further ideas appreciated?

Regards

Ron

It looks fine from here just looking at your data I don't see any glaring errors but without actually playing with the database it's impossible to tell.

Thx Richard, What I can't fathom is that with the MS Access Db engine it works correctly.  Let me investigate a little further before I get further, either way.

Hi Ronald, I would look closely at this portion of your query:

    WHERE ClusterDate = Forms!DatePickerF!StartDate+1

since this determines the day under which your data is shown.  If the date in the WHERE clause is wrong, it's going to put the data under the wrong date.

Hi Kevin/Richard,  I have looked at this code for days and can't see the problem, especially as this exact code has been working for over a year using the MS Access Db engine.  As I explained earlier it is only when I moved the tables from MS Access to SQL Server does this error occur.  

The company that runs this Calendar software is also running a 3rd party SQL Server database on the same server and that software has worked correctly for ten+ years so I can't see how it is a SQL Server configuration issue.

I am sending the MS Access frontend and Split backend to Richard as he has suggested. If you or he successfully convert the backend to MS SQL Server and it displays correctly then it must be something I am doing in the conversion or configuration of SQL Server.

If you or Richard find something in the code then I will be a happy boy and a tip especially goes in the tip jar!

Ron de Boer

Hi Ron. I emailed you earlier. I did get your file, but as a general rule I don't accept file attachments other than screen shots. Sorry. It's just too time consuming. I'm also in the middle of moving right now, so time is even more scarce than ever. I'm sorry if you thought me saying "without actually playing with the database" was an invitation for you to send me the database. I didn't mean it that way. I just meant it's hard to tell from here.

Perhaps one of the guys in the DevNet will take a look at it for you. I just don't have the time right now. But by all means please do continue posting here in this thread. Maybe something will pop out.

I still think it's a server configuration issue. Do you have ASP available on a webserver on that machine? Try doing a:

<% Response.Write Date() %>

And see what you get.

Worth running some SQL in SSMS?

-- Current Datetime
SELECT
   GETDATE()         as 'GETDATE()'  
  ,CURRENT_TIMESTAMP as 'CURRENT_TIMESTAMP'
  ,SYSDATETIME()     as 'SYSDATETIME()';

--UTC and Time zone Offset
SELECT  
   SYSDATETIMEOFFSET() as 'SYSDATETIMEOFFSET()'  
  ,GETUTCDATE()        as 'GETUTCDATE()'
  ,SYSUTCDATETIME()    as 'SYSUTCDATETIME()';  
GO

No I don't have ASP but will run Alex's code above to prove Date, Thx

Hi,  Could someone please tell me how to find out what the start day number of the week is in MS SQL using the SSMS App?  I am wondering that MS SQL is using say Monday (3) when my MS Access DB is using Saturday (1) which would account for why all my data is displaying 2 days out when I moved my data from MS Access to MS SQL.

@@DATEFIRST

THX