Results 1 to 6 of 6
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Date formatting SQL 2008 r2 need advice/best practices.

    Access 2007, SQL Server 2008 R2. ODBC connection. This is my first complex development with SQL Server. This database tracks Properties and Clients, and court dockets.



    All dates in the front end are entered YYYY/DD/MM. My client is good with this and is willing to change their method of entering dates. It seems to work seamlessly with the simple date format in SQL Server.

    However there are fields where only a Year is called for such as:

    Tax Year = YYYY

    Last Year Filed = YYYY

    Year Property Purchase Date = YYYY

    But I can't figure out how to handle this. Surely others have encountered this. My client is willing to go with entering like a 2014/01/01 as a generic date for these fields. But that seems clunky to me. I can't find any clear direction on this. I will take all input. However, if it involves T-SQL you'll need to be gentle with me.

    Fred

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If all you want is a year I'd just use a numeric format like smallint. You aren't really storing a "date", just a number.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    I hadn't thought of that. It might work. I can group by Property ID and still sort by Tax Year or whatever as it is a integer. I need to think about it. I would like to leave this open for a few days.

    Thanks Fred

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Fred. If you decide you need a date field, you could let the user enter year into an unbound textbox and then use code to incorporate that with a default month/day and put it into a hidden textbox bound to your date field using DateSerial(). It would be more work on you, but allow the client to just enter the year.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Access 2007 Front end and SQL Server 2008 back end.

    Leave the SQL Server Field as a DATE.

    SQL Server processes dates as YYYY-MM-DD

    If you enter only the year into the SQL Server Date field from Access, SQL Server automatically supplies the Day and Month as 01-01.

    So The Screen Year field input mask is 0000;;_

    The Screen validation Rule is: Is Null Or Not Alike "%[!0-9]%" And Len([DocketPropertyCurrentYR])=4

    The end user is restricted to the year, but after the field refreshes it shows the full SQL date as YYYY-DD-MM or 2014-01-01.

    In the Event Lost Focus do a me.refresh.

    So far it seems to work great.

    The end user only enters the year.
    Sql adds the DD-MM automatically. Which is ok in my situation.
    The value remains as a fully qualified Date field.
    No conversion between text and date required.

    Fred

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Thanks for the update. I've learned something new today, as I didn't know it would allow you to just enter the year.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-02-2014, 07:58 PM
  2. Replies: 4
    Last Post: 08-25-2014, 09:05 PM
  3. Formatting date to have leading zero for DAY
    By Praveenevg in forum Access
    Replies: 13
    Last Post: 08-20-2014, 01:27 PM
  4. Best Practices
    By Sinkerhawk in forum Access
    Replies: 2
    Last Post: 06-30-2011, 11:44 AM
  5. Formatting a date
    By NISMOJim in forum Queries
    Replies: 7
    Last Post: 09-17-2010, 05:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums