locked
ACCESS DATA SOURCE GET CURRENT DATE RRS feed

  • Question

  • User810354248 posted

    I USED THIS CODE IN SQL

    DATEDIFF(DAY, from, GETDATE())/30 AS TEN,

    WHAT CAN BE USED IN ACCESS DATA SOURCE

    Sunday, December 9, 2012 8:58 AM

Answers

  • User-1716253493 posted
    open your db using ms access. Try some query there. There is litle bit deferent SQL command between SQL in ms access and SQL in SQL Server.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 7:07 AM
  • User810354248 posted

    DEAR  oned_gk I TRIED YOUT=R TRICK AND GOT IT

    CODE I USED IS AS UNDER

    SELECT PersData.PNO, PersData.RANK, PersData.Name, PersData.DOSEN, PersData.UNIT, PersData.APPOINTMENT,PersData.FROM, PersData.SPECI,PersData.COURSESTR,DateDiff('d', [PersData.FROM], Now()) / 30 AS TEN
    FROM PersData

    AND IN GRIDVIEW USED DataFormatString={0:f0} FOR REMOVING DECIMALS

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 11:31 AM
  • User-1199946673 posted

    CODE I USED IS AS UNDER

    SELECT PersData.PNO, PersData.RANK, PersData.Name, PersData.DOSEN, PersData.UNIT, PersData.APPOINTMENT,PersData.FROM, PersData.SPECI,PersData.COURSESTR,DateDiff('d', [PersData.FROM], Now()) / 30 AS TEN
    FROM PersData

    which is exactly the same as:

    SELECT PNO, RANK, Name, DOSEN, UNIT, APPOINTMENT,[FROM], SPECI,COURSESTR,DateDiff('d', [FROM], Now()) / 30 AS TEN
     FROM PersData
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 12:29 PM

All replies

  • User810354248 posted

    I tried IT BY USING THI S

    [from]-NOW()/30 AS TEN

    i want the out put in months

    Sunday, December 9, 2012 9:05 AM
  • User810354248 posted

    when i used this code

    DATEDIFF(DAY, [from], date())/30 AS TEN

    the error is 

    No value given for one or more required parameters.


    Sunday, December 9, 2012 9:24 AM
  • User-1407477457 posted

    when i used this code

    DATEDIFF(DAY, [from], date())/30 AS TEN

    the error is 

    No value given for one or more required parameters.

    <div>
    </div>

    Why did you switch from now() to date()?  Does date() take a parameter?  I can look it up, but so can you.

    Sunday, December 9, 2012 9:34 AM
  • User-1716253493 posted
    use hiddenfield as control param. Set the value in page load event. Hiddenfield1.value=now.date()
    Sunday, December 9, 2012 9:41 AM
  • User810354248 posted

    how can i add to access data source

    Sunday, December 9, 2012 10:22 AM
  • User-1199946673 posted

    Baiju EP

    WHAT CAN BE USED IN ACCESS DATA SOURCE

    As already said, Now() is the equivalent of GETDATE() in SQL Server

    Baiju EP

    when i used this code

    DATEDIFF(DAY, [from], date())/30 AS TEN

    the error is 

    No value given for one or more required parameters.

    Because day is a reserved word (just like from) in Jet. But in Access (jet), the interval parameter is a string

    DATEDIFF('d', [from], date())/30 AS TEN

    http://www.techonthenet.com/access/functions/date/datediff.php

    Sunday, December 9, 2012 11:15 AM
  • User810354248 posted

    TRIED THE SCRIPT IN GRID VIW TEN FIELD DISPLAYS dd/MM/yyyy AND NOT THE MONTH

    Sunday, December 9, 2012 11:55 AM
  • User-1199946673 posted

    TRIED THE SCRIPT IN GRID VIW TEN FIELD DISPLAYS dd/MM/yyyy AND NOT THE MONTH

    What do you mean?

    Sunday, December 9, 2012 5:48 PM
  • User-1716253493 posted

    try this

    datediff('d', [from], NOW()) / 30 AS TEN
    
    
    

    simiilar with your DATEDIFF(DAY, from, GETDATE())/30 AS TEN
    This will working

    Sunday, December 9, 2012 8:06 PM
  • User3866881 posted

    TRIED THE SCRIPT IN GRID VIW TEN FIELD DISPLAYS dd/MM/yyyy AND NOT THE MONTH

    Hi again,

    Hans_v says right——Please refer this rule:

    The syntax for the DateDiff function is:

    DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

    interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

    Interval Explanation
    yyyy Year
    q Quarter
    m Month
    y Day of year
    d Day
    w Weekday
    ww Week
    h Hour
    n Minute
    s Second

    And what's more, can you show us how you write your query string of SQL in Access?

    Sunday, December 9, 2012 9:30 PM
  • User3866881 posted

    Baiju EP

    TRIED THE SCRIPT IN GRID VIW TEN FIELD DISPLAYS dd/MM/yyyy AND NOT THE MONTH

    Hello again,

    If you wanna see the differences between the two date and get the Month, please try this:

    DateDiff ("m", date1, date2)

    Sunday, December 9, 2012 9:32 PM
  • User810354248 posted

    THERE IS NO ERROR BUT THE COLOUMN TEN SHOWS 

    dd/MM/yyyy

     NO NUMERICALS

    IN MY TABLE FROM IS A DATE

    Monday, December 10, 2012 9:47 AM
  • User810354248 posted

    I WANT THE DATE DIFFRENCE IN MONTHS (EG FROM AMD TO TODAY/30) IT WILL SHOW HOW MANY MONTHS. THAT IS WHAT I WAS LOOKINH

    Monday, December 10, 2012 9:49 AM
  • User-1716253493 posted
    datediff('m', [from], NOW()) AS TEN

    Monday, December 10, 2012 9:56 AM
  • User810354248 posted

    NOT WORKING

    MY CODE IS AS UNDER

    SELECT [PNO],[RANK], [Name], [DOSEN], [UNIT],[APPOINTMENT],[FROM],[SPECI],Coursestr,
    datediff('m', [from], NOW()) AS TEN FROM [PersData]WHERE SPECI='COMD'"

    Monday, December 10, 2012 11:39 AM
  • User3866881 posted

    NOT WORKING

    Show us your screenshot, please

    Monday, December 10, 2012 8:04 PM
  • User810354248 posted

     my screen shot is here

    Tuesday, December 11, 2012 12:04 PM
  • User3866881 posted

    I cannot see that image, please submit this to the skydrive and them paste the relative url.

    Wednesday, December 12, 2012 5:32 AM
  • User810354248 posted

    i had shared it in 4shared.com i had uploaded in skydrive but not able to share

    http://www.4shared.com/photo/8w__dF-b/screen.html

    code use is abelow

    SELECT [PNO],[RANK], [Name], [DOSEN], [UNIT],[APPOINTMENT],[FROM],[SPECI],Coursestr,
    datediff('D', [from], NOW()) AS TEN FROM [PersData]WHERE SPECI='COMD'

    Wednesday, December 12, 2012 9:52 AM
  • User3866881 posted

    Hi again,

    You mean your "Ten" column is still of "dd/MM/yy"?

    Wednesday, December 12, 2012 8:08 PM
  • User810354248 posted

    yes

    Wednesday, December 12, 2012 9:04 PM
  • User3866881 posted

    yes

    What codes have you written?

    Thursday, December 13, 2012 12:16 AM
  • User-1716253493 posted

    What is your [from] datatype?

    Thursday, December 13, 2012 2:08 AM
  • User810354248 posted

    this is my code

    SELECT [PNO],[RANK], [Name], [DOSEN], [UNIT],[APPOINTMENT],[FROM],[SPECI],Coursestr,
                        datediff('D', [from], NOW()) AS TEN FROM [PersData]



    Thursday, December 13, 2012 10:48 AM
  • User810354248 posted

    Dear oned_gk

    My FROM field i s a Date/Time

    Thursday, December 13, 2012 10:50 AM
  • User-1199946673 posted

    NOT WORKING

    What do you mean by "Not working'?

    Your SQL string looks ok, but the ASP.NET qrystal ball isn't working, so if you don't tell us your desired result, perhaps with some sample data and desired output, we need to guess what you want?

    Thursday, December 13, 2012 1:34 PM
  • User810354248 posted

    DATABASE IS IN MS ACCESS

    TABLE NAMED PersData

    PNO RANK Name DOSEN UNIT APPOINTMENT FROM SPECI Coursestr
    1234 LDC KING 12/21/1999 1 HOSP DOCTOR(SUR) 9/19/2009 PH1 MBBS,SUR SPL
    2134 UDC JOHN 04/23/2001 2 HOSP ASST 1 12/31/2009 PH2 LAB TECH 
    1235 LDC PAL 12/21/1999 1 HOSP DIR 9/19/2009 PH1 MBBS,MD
    2154 UDC SIOM 04/23/2001 2 HOSP CLK 1 12/31/2009 PH2 MA
    QUERY I USED FOR THIS TABLE ABOVE IS 
    SELECT [PNO],[RANK], [Name], [DOSEN], [UNIT],[APPOINTMENT],[FROM],[SPECI],Coursestr,
                        datediff
    ('D', [from], NOW()) AS TEN FROM [PersData]
    I WANT TO CALCULATE AND DISPLAY HOW MUCH MONTH DID EACH STAFF SERVED IN TEN COLOUMN
    IN MY SQL DATATABE I USED THE FOLLOWING AND GETS CORRECT RESULT IN MONTHS
    DATEDIFF(DAY, FROM, GETDATE())/30 AS TEN
    Thursday, December 13, 2012 8:49 PM
  • User-1199946673 posted

    I WANT TO CALCULATE AND DISPLAY HOW MUCH MONTH DID EACH STAFF SERVED IN TEN COLOUMN

    I don't think I understand, but....

    IN MY SQL DATATABE I USED THE FOLLOWING AND GETS CORRECT RESULT IN MONTHS
    DATEDIFF(DAY, FROM, GETDATE())/30 AS TEN

    As already said, the equivalent of this in Access is:

    DateDiff('d', [FROM], Now()) / 30 AS TEN

    Friday, December 14, 2012 6:54 AM
  • User-1716253493 posted
    open your db using ms access. Try some query there. There is litle bit deferent SQL command between SQL in ms access and SQL in SQL Server.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 7:07 AM
  • User810354248 posted

    DEAR  oned_gk I TRIED YOUT=R TRICK AND GOT IT

    CODE I USED IS AS UNDER

    SELECT PersData.PNO, PersData.RANK, PersData.Name, PersData.DOSEN, PersData.UNIT, PersData.APPOINTMENT,PersData.FROM, PersData.SPECI,PersData.COURSESTR,DateDiff('d', [PersData.FROM], Now()) / 30 AS TEN
    FROM PersData

    AND IN GRIDVIEW USED DataFormatString={0:f0} FOR REMOVING DECIMALS

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 11:31 AM
  • User-1199946673 posted

    CODE I USED IS AS UNDER

    SELECT PersData.PNO, PersData.RANK, PersData.Name, PersData.DOSEN, PersData.UNIT, PersData.APPOINTMENT,PersData.FROM, PersData.SPECI,PersData.COURSESTR,DateDiff('d', [PersData.FROM], Now()) / 30 AS TEN
    FROM PersData

    which is exactly the same as:

    SELECT PNO, RANK, Name, DOSEN, UNIT, APPOINTMENT,[FROM], SPECI,COURSESTR,DateDiff('d', [FROM], Now()) / 30 AS TEN
     FROM PersData
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 14, 2012 12:29 PM