Post reply

Access/SQL Server date/time data type problem

  • December 3, 2003 at 8:51 pm

    #65165

    I am using Access XP and SQL Server 2000. My problem has to do with the way that SQL Server and Access represent the date/time data type.

    When creating the table in SQL Server I'm forced to used the datetime data type (as SQL Server does not have seperate data types for each of them). My problem is that when I create a form in Access, I want to show the time and date in different controls. However, SQL Server returns both the time and the date. It format it strangely such that the date is hidden (if the data entered is time). It's there, if you move the cursor across, but hidden by default. Thirdly, when I create a report based on the table it outputs the time with the figure '899' before it (date outputs correctly).

    I would be grateful for any advice people could offer.

  • December 4, 2003 at 2:23 am

    #484564

    Yes, there is only a datetime data type in SQL Server.

    However, you've already figured out that you can use two controls to separate this information.

    Will this information be updatable by your user?

    If so, you will run into some problems (and some extra code) getting it all together to update your column.

    If this information will not be updatable, you can simply set the 'Activated' property of both control to 'NO', and the 'Locked' property to 'YES'.

    This way these controls will never get the focus and you don't run into these presentation issues.

    Your report problem I am not able to replicate. It works fine here.

    Did you set both reports control to the desired format?

    HTH

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 4, 2003 at 7:50 am

    #484612

    if you dont want to write any code. Try to use 2 Fields ( one for the date an the other for the time) then on the front end make sure that the input mask is setup accordingly and you will have to show both on the report. If you need to perform ranges searches on queries using the date and the time components you can always combine them in a view or calculated Filed

    HTH


    * Noel

  • December 4, 2003 at 6:57 pm

    #484739

    I forgot to put input masks on the report. I thought that if you did it to the form, it would be sufficient.

    Thanks to both of you for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply