Date/Time field only saves to table as 12:00:00am

noboffinme

Registered User.
Local time
Today, 23:10
Joined
Nov 28, 2007
Messages
288
Hi

I've added some code to a database form I copied from this forums sample databases.

The database has a Start & Stop field & I want to save those times to a table, hence why I needed to add some code.

I've created a table & using my added code, the times save to the table OK but will only save as 12:00:00AM.

I checked the value of the Forms fields while the code runs & the time values are correct ie; they are not 12:00:00AM but the actual times like 1:34:23 for example.

I'veattached the file for ayone to check why the time on the fileds isn't saving to the table.

Thanks

Peter
 

Attachments

txt_Start.Value has value which Access treats as a number.

txt_Start.Value will have a value of less than 1 and greater than or equal to 0.

Format(txt_Start.Value, "mm/dd/yyyy") truncates to whole numbers leaving 0.

The Format function formats 0 as 12:00:00 AM and puts it in the table because your field type is string.

Don’t truncate the time…
Format(txt_Start.Value, "mm\/dd\/yyyy hh\:nn\:ss")

Better still, don’t use a field type of string but use a Date/Time field type as intended.
Store the unformatted date and the time and display it however you want.


Chris.
 
I agree with Chris. Use MSGbox to display the parameter of .execute command and you will see what is happening
 
Thanks ChrisO & Rabbie,

The format 'Format(txt_Start.Value, "mm\/dd\/yyyy hh\:mm\:ss")' did the trick!!

Also Rabbie, that's a handy tip to find the value although I used a breakpoint & hovered over the 'Start_Time' field variable in the code.

I've never seen the format before, how/ why does it work??

Peter
 
Peter.

Format(txt_Start.Value, "mm\/dd\/yyyy hh\:nn\:ss")

The back slashes are there to force a string literal interpretation of the forward slashes and colons. Without the back slashes the forward slashes and colons will be replaces with whatever is used in the computer’s regional settings.

If the computer uses a forward slash and colon in regional settings then the forward slash and colon in the format string get replaced with a forward slash and colon in the final formatted string.

Without the back slashes it is purely good luck that the computer is running with acceptable regional settings. But even if the computer is installed with acceptable regional settings that does not mean that the user will not change the defaults to something unacceptable.

Even if the programmer knew how to change the computer’s regional settings, to suite their program, it would be the height of arrogance to do so. That change would affect all programs currently running on the user’s computer.

We arrive at the point that the program should be immune to regional settings for its operation but still allow the influence of regional settings for display. These are two very distinct facets of the program’s operation; the technical operation and the user interface.

I think it would help to remember this if you remove the back slashes, switch your regional settings to French (Switzerland) and test it again. There is nothing like a failure to demonstrate a need.

There are many ways to force immunity to regional settings dates and times and people have their own preference. Some will use Allen Brown’s SQLDate() function, which is based on USA format, and others prefer the ISO format.

----------------

Now, it might have done the trick but it is only covering up a more serious flaw in your design.

You have three fields in your table, fld_start_time, fld_finish_time and fld_elapsed_time each of which is a Text field.

Basically speaking, fld_start_time and fld_finish_time should be Date/Time data type fields and fld_elapsed_time should not exist. You should be able to calculate the elapsed time from the start and finish times. I’m not as rigid as most people when it comes to storing calculated values but in this case I can not see a need. If we do store a calculated value we should have a valid reason to do so.

The fld_start_time and fld_finish_time fields should contain both the Date and the Time, not just the Time only. The reason for this is that the start and finish times might straddle midnight. If they do straddle midnight, and you only had the Time data, then the elapsed time calculation would be incorrect and could even be negative.

Since you are at a very early stage with this project I would recommend switching the fields to Date/Time data types, store the Date and the Time and do not store the elapsed time. The elapsed time can be calculated and displayed as and when needed.

Chris.
 
Hi

I've added some code to a database form I copied from this forums sample databases.

The database has a Start & Stop field & I want to save those times to a table, hence why I needed to add some code.

I've created a table & using my added code, the times save to the table OK but will only save as 12:00:00AM.

I checked the value of the Forms fields while the code runs & the time values are correct ie; they are not 12:00:00AM but the actual times like 1:34:23 for example.

I'veattached the file for ayone to check why the time on the fileds isn't saving to the table.

Thanks

Peter

Peter

Code:
Private Sub cmd_save_Click()
Dim Sql As String
 
CurrentDb.Execute "INSERT INTO tbl_Performance  (fld_team_member,fld_task, fld_start_time, fld_finish_time, fld_elapsed_time) VALUES ('" & cbo_team_member.Value & "', '" & cbo_task.Value & "', #" & Format(txt_Start.Value, "mm/dd/yyyy") & "#, #" & Format(txt_Finish.Value, "mm/dd/yyyy") & "#, #" & Format(txt_Elapsed.Value, "mm/dd/yyyy") & "#)"

MsgBox "Results saved"

End Sub

Your code is incorrect.
(Dim Sql As String) is not even required. It does nothing.

The easiest way to solve the problem is to make the Form bound to the Table. Either that or fix the code. I would prefer to bind the Form which is the standard method of Access.

I think this is the answer you seek.
 
noboffinme

Just a bit of advise outside of your question.

Your Naming conventions could be better. There is no need for fld as it is a bit repeative and uses valuable space. ID should be PerformanceID or as I would PerformancePK.

Also use Date/Time not Text as field data type.
 
Thanks again ChrisO,

I did originally have the fields saved as Date/Time but not on the one I uploaded.

& also Thanks Rainlover for the tip on the SQL line, I'll remove it, Cheers

Peter
 
noboffinme

I would be interested in knowing two thing.

First, what do you intend to do with this Form.

Second, can you post a Link to where you go the original template that you created your from from.
 
Last edited:
OK, The database was located in this forum under 'Sample databases' (search under 'Timer') & I used it to help a friend who wants to create a performance monitor.

:)
 
http://www.access-programmers.co.uk/forums/showthread.php?t=100777&highlight=timer

Hmmmm. (That’s me being polite.)

I think you may have got yourself off to a bad start with that demo and I will suggest why.

1. It contains unneeded references.
2. It contains unneeded variables.
3. It contains unneeded debug comments.
4. Private Sub Command5_Click() is a bad name.
5. Private Sub Command5_Click() contains code which is not formatted correctly.
6. The Timer Interval is running at 15 milliseconds but the displayed time is only updated every second. Technically that is not a problem but it is too slow for milliseconds and about 60 times too fast for seconds.

That demo appears only to be demo of how to use the API function GetTickCount() and was not intended to be any particular final solution.

I think it is required to first define the required result before attempting to use an apparently simple solution you found somewhere.

Therefore I think the question boils down to this. Given you want to time something; what do you want to time and how do you want to time it? The technicalities of how it is done is unimportant at this stage.

Chris.
 
Hi ChrisO,

I did make the changes you suggested for my version such as the date/time fields and the calculation of the elapsed using Finish - Start times.

Otherwise, I think the reason for the time code was to allow people to make changes on the form without stopping the timer - (I think)...

Anyway, always keen to learn, so could you advise or post an example of how you would do this?

I simply want a Form to select a Staff Member & a Task & then start a timer that goes until I stop it (obviously) & records the date/time for Start, Finish & Elapsed time.

Then a report can be created to summarise the table for work done on each area by the Staff members. Thanks
 
Thanks ChrisO,

This is really good!

Cheers.

Might have to do an update ... :)
 
ChrisO

Does it come in any other colours? :D :) :o
 
Very good Chris.

But where do you stop.
 
Very good. I like it.

You should put your name in the footer of the form so you can be acknowledged as writer. Or something like that.
 
Last edited:
I really have no need to do that, it would only make it slightly more difficult for an experienced developer to remove.

If protection of ideas is required, which I agree with, then the real protection comes in other ways.

Example 1:
A suffix of PK to primary keys and FK to foreign keys should be recognised as your idea by any experienced developer.

Example 2:
For me, no references and an attempt to become immune to regional settings should also be recognised by any experienced developer.

I do try to put a flower box in my code which states “Please leave any trademarks or credits in place.” But that statement is not for my benefit. That is for any of the code I have obtained elsewhere and used, usually API calls. The actual credit for the code is located within the specific code.

So, experienced developers may try to rip off code by removing things which give credit to others. But they will have trouble looking for overall intent, method and style in the code. They also need to watch for deliberate spelling mistakes in comments, objects and variable names.

Another way to rip off code is to have something on a site which was posted on the internet. I have seen it many times where a site has a demo which no one on the site can support. The demo then simply becomes a case of “Look here; aren’t we magnificent?” and therefore scores brownie points for its very existence. No need to mention site names here but some are conspicuous.

The reason I started a SkyDrive site was specifically to hold the latest version of my stuff off my computer and let SkyDrive automatically time stamp it in an externally verifiable manner.

All in all though, I can see my code a mile off and I don’t need to take specific credit for it.

Chris.
 

Users who are viewing this thread

Back
Top Bottom