Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Unanswered: Another Access Question!

    Greetiungs again!

    Now I don't like to ask this kind of question but I am struggling to cope with all these queries that I need to convert in to Access format. Below is probably the most demanding of the lot so I ask you kind people to help convert the following code in to a format that Access would accept?

    Thanks in advance.

    Code:
    SELECT	SER.SERVHIST_MEMBNO MEMBNO, 
    		CONVERT(DATETIME,SER.SERVHIST_STARTDTE) STARTDATE, 
    		CONVERT(DATETIME,SER.SERVHIST_ENDDTE) ENDDATE,
    		SER.SERVHIST_ACCRAT ACCRAT, 
    		HRS.HRSHIST_PTHRS PTHRS, 
    		HRS.HRSHIST_FTHRS FTHRS,
    		DATEDIFF(D, SER.SERVHIST_STARTDTE, SER.SERVHIST_ENDDTE) DAYSCALC
          	FROM	SRC_GSK.DBO.SERVHIST SER
          	JOIN SRC_GSK.DBO.HRSHIST HRS 
    		ON SER.SERVHIST_MEMBNO = HRS.HRSHIST_MEMBNO
    	WHERE	CONVERT(DATETIME,SER.SERVHIST_STARTDTE) >='1996-11-01'
    Shadow to Light

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of CONVERT(DATETIME,x), just use x, and access will convert it if it is in a recognizable date format

    for DATEDIFF(D,...) use DATEDIFF("d",...)

    table names must of course be access names, access has no server.owner.tablename structure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Talking

    Originally posted by r937
    instead of CONVERT(DATETIME,x), just use x, and access will convert it if it is in a recognizable date format

    for DATEDIFF(D,...) use DATEDIFF("d",...)

    table names must of course be access names, access has no server.owner.tablename structure
    Thanks man.

    I didn't realise Access is so similar to SQL Server because I have never been a big fan of Access in the first place. I guess it's the bad experience I had with it back at University.

    I will carry on with the rest now!
    Shadow to Light

  4. #4
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Could you tell me how this is written in Access ?

    Code:
     and convert(datetime,ser.servhist_startdte) between convert(datetime,hrs.hrshist_startdte) 
              and case 
    		when convert(datetime, hrs.hrshist_enddte)is null then getdate() 
    		else convert(datetime, hrs.hrshist_enddte) end
    Thanks.
    Shadow to Light

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •