January 26, 2008 at 2:41 pm
Hi
Sorry i have posted this message in another section, when really it should probably be in here.
Anyway, I have Access front end database on a SQL server back end.
I have come up with a problem, that i just cannot resolve despite the help pages or searching.
i wish to log the time of day of an event on a record, in 24 hour format (00:00), simple as that.
Then to link the table thru to the front end database and then be able to edit / update the time in the front end on a form.
currently i have the record in sql server table as a smalldatetime datatype and short time format. all is well.
when i go to access and link the table to the front end, no matter what i do the record shows as 01/01/1900 23:59:00 format rendering it uneditable.
Would somebody be so kind as to point in the right direction or advise me where i am going wrong.
Many Thanks
January 27, 2008 at 2:40 am
In SQL a short date time is a date and a time. SQL tables have no concept of formats.
My access is rather rusty, but maybe you could link the table through and put the format mask on the access form. Or make the form field unbound and write an after-update event for it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2008 at 2:44 am
Many Thanks for your reply.
i have decided to hold the time and date in sql as a number with decimal, and do all the manipulations and editing in access front end, which i think is as per your first suggestion.
Regards
Ross
January 27, 2008 at 5:01 am
Almost. I would suggest you leave the datetime as a datetime in SQL. That way you can use all the date time functions and you don't need to worry about weird stuff like 35 hours and 71 minutes and the like.
Do the display manipulation in access.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2008 at 5:09 am
Hi
Many Thanks for your time in assisting me.
I dont see how i can have the date as a datetime datetype in sql, as that is part of the initial problem, i was / am having, when the linked access table shows the value
Regards
Ross
January 27, 2008 at 5:58 am
You said you were going to store it as a numeric and do all the manipulations and editing in the front end. Do the same, just have it as a datetime in SQL. You can set the date portion to the 0 date (1900-01-01).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2008 at 6:13 am
Hi
Sorry, i maybetopping out at this level.
i copied the values across to another column in the respective sql table, went to the access FE, and updated the table link to the sql table. i then created a query and used CVDate(date_value), the calculated date is now 2 days later.
is this something to do with setting the date portion to 0. if so how do i do that?
Many thanks for your help
Ross
January 27, 2008 at 9:47 am
There's no reason to be doing all of this. SQL datetime fields CAN be edited in Access, nevermind the formatting that's applied to it. They're stored in a similar way on the data side (essentially a numerical representation of the date and time in terms of days as a base unit.)
What is the issue you're running into - as of right now you seem to be hunting in the wrong area.
Assuming you're using Access forms as the UI piece, you should be looking at data entry masks AND display formats...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 27, 2008 at 10:23 am
i have a table of records in sql which acts as the BE, in which i store the take off and landing times of an aircraft.
in this table i want to store a take off time and date, and a landing time and date.
i also need to store an accumalative flight hours figure formatted to hh,hhh:nn, but thats another issue for the time being.
using access xp as the FE, i want to be able to edit these dates and times to make any corrections.
the problem i have been having is that i cant seem to find a way of editing thru the linked table in an access form.
i have managed it this afternoon when i changed the sql take off time and date column to a float datatype, but since i have changed it back to a datetime datatype, i am having problems again
Thanks for your reply and help
January 27, 2008 at 11:52 am
Hi Gail, Matt
by jove, i have managed it.
Thank you very much for your inputs, i took onboard you help and have persevered, to the point where the time and date works.
However, now i have the how to set up the accumaltive hours field, so that it i can input upto 999,999 hours 59 minutes.
Any help in that problem would be very much appreciated as well.
Ross
January 27, 2008 at 12:45 pm
Assuming you store the duration (flight time) as a datetime as well, then
cast(cast(duration as decimal(18,4))*24 as int) --this would return your hours
datediff(day,0,duration)*24+datepart(hour,duration) -- method #2 for returning hours
datepart(minute,duration) -- this would return the minutes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 27, 2008 at 12:58 pm
Hi Matt
Thanks
Unfortunately i cant use the duration as sometimes i dont know initially the start and finsih times, in which case if i dont the finsih time, i default the time to 23:59.
However, i may know the total flight hours, which thru some verification code, i can check that it rises as the date value increases
In access i would like to entry in the format of hhh,hhh:nn
Thanks for your assistance today, much appreciated
Regards
Ross
January 28, 2008 at 4:42 am
Are you using linked tables? If so, I believe that Access will be making the correct mapping between datatypes.
Have you verified your regional settings for date and time?
Regards Ramon
Regards Ramon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply