Date/Time format 4

[newguy86]

I have a date/time stamp that is generated using the following format:

"yymmdd-hhnnss"

As I understand it, this format should produce a value that looks similar to this:

091127-074523

The issue that I am having is the value transmits differently depending on where I display it when I use this same format.

For example, if I look at the same value in the table and in an auto-generated email this is what I see:
-Table => 091127-74523
-Email => 091127-074523

This is causing me some issues because if someone takes the value from the email and tries to search for it in the table then they will not find that record without removing the zero immediately after the dash.

If anyone can provide any assistance, it would be greatly appreciated.

Also, almost forgot to mention that I am using Access 2003.

Thanks,


Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[PWise]

Is this a txt value in the table?

How do you auto-generate the Email?

 

[newguy86]

The field's data type in the table is date/time.

Whenever I need to have an email auto-generated in Access I just make sure that the Outlook Object Library is included under "References" and I run this VBA code:

Sub SubjectLineText()
    Dim olApp As Outlook.Application
    Dim olmail As MailItem
    Dim acForm As Access.Form
    Set olApp = New Outlook.Application
    Set olmail = olApp.CreateItem(olMailItem)
    Set acForm = [Form Name]
    olmail.Display   'This will display the email for the user instead of blindly sending.
    olmail.To = "[Email Address]"  'The email account this will be sent to.
    olmail.Subject = "Request # " & Format(acForm.RequestNumber, "yymmdd-hnnss") & " ~ " & acForm.GDSTeam & " ~ " & "Due " & acForm.DueDate  'Subject line text that will automatically populate in the email.
    olmail.Body = "[The body of your message]"
End Sub

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[PHV]

You want this ?
Format(acForm.RequestNumber, "yymmdd-hnnss")
or this ?
Format(acForm.RequestNumber, "yymmdd-hhnnss")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newguy86]

Thank you PHV! That is exactly what I am using.

The problem is that I want two numbers for the hour to always display in the table no matter what time the record was created. In other words, I am using this format "yymmdd-hhnnss" because the "hh" part of that format should always display two numbers for the hour even if the value is less than 10.

But what I see in the table when using that format ("yymmdd-hhnnss") is something like 091127-74523 instead of 091127-074523. (Notice the missing zero right after the dash.)

The problem is with the table and not with the email that is generated.

Any assistance would be greatly appreciated.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

My apologies PHV. I misunderstood what you were saying.

I would prefer to use this format - "yymmdd-hhnnss".

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[Golom]

I would prefer to use this format - "yymmdd-hhnnss".

But you are using

olmail.Subject = "Request # " & Format(acForm.RequestNumber, "yymmdd-[red]hnnss[/red]")

I think that's what PHV was saying.

 

[SkipVought]

Well what's preventing you from using that format?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

I am using that format in the email so that it always matches what is being generated in the table. What I want to do is get the table to generate that field correctly.

Let me re-explain what is happening. In the table I have a field with a Date/Time data type and the format that I use to display that field is "yymmdd-hhnnss" but when a value is generated for that field, that value will be missing the leading "0" immediately after the dash (where the hour should be displayed) if the time is before 10am.

In other words, the format that I am using in the table is "yymmdd-hhnnss" but when a value generates in that field, that value actually displays as if its formatted as "yymmdd-hnnss".

So with the format that I have setup for that field in the table ("yymmdd-hhnnss") I expect to get this result:

091127-074523

but this is what is actually displaying in the table:

091127-74523

Something is causing the first "0" after the "-" and before the "7" to disappear. And I cannot figure out where the issue is because the only thing that I can think of that would cause this would be the Format property of this field in the table. Which is formatted correctly.

Any assistance on this would be greatly appreciated.

Thanks,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

To answer SkipVought's question, the table is what's preventing me from using that format. Because I cannot get the field in the table to display with that format, I cannot use the format that I want when I auto-generate the email.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

Thank you to everyone who assisted with this issue. I figured out that this issue is caused by the regional settings in Windows.

Apparently, if your regional date/time settings in Windows are not setup to display the hours with leading zeros then you will not see any leading zeros in a timestamp that is generated in Access.

Thanks again guys for all of your assistance.

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[newguy86]

Although, this leads me to another question. Is it possible to adjust the regional settings on a users computer through Access VBA?

Any information would be greatly appreciated.

Thanks,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[Golom]

Have a look at this link.

Note however that it is generally considered bad behavior for a program to alter a user's settings.

 

[SkipVought]

The formatting of a date/time field in a table is irrelevant.

The format is only what you see.

The underlying VALUE is a NUMBER.

That NUMBER can be formatted ANY WAY YOU LIKE.

Do not confuse the formatting of the date/time field with the Format function, which returns a STRING, that is not a date/time value.

So I would ASSUME from the preceeding discussion that acForm.RequestNumber is a NUMBER representing a Date/Time value in your table. After all, it APPEARS to be a NUMBER, although, it escapes me why it would not be called some sort of 'date.'

olmail.Subject = "Request # " & Format(acForm.Request[b]Number[/b], "yymmdd-hnnss")

Is that what we are working with? Or is acForm.RequestNumber some other kind of animal?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

Golom,
Thank you for the link, that is exactly what I was looking for. And I understand what you mean by altering a users settings being bad behavior for a program. The more I thought about it the more it became clear how much of a hassel it would be to do something like that.

SkipVought,
The attached code for generating an email was not the issue, the issue was in table. That code was meerly an illustration to answer pwise's question from earlier.

And acForm.RequestNumber is not called something to the effect of 'date' because it is not being presented as a date field.

Let me explain, I have built a worklog db that will store every add/update/delete request that we recieve and I needed a primary key that will issue request id's incrementally but on a scale larger than what the AutoNumber feature could handle. So what I came up with was using a timestamp (formated as "yymmdd-hhnnss") as the request id for every new record that is created so that even if the db gets flooded with new requests it will still keep generating new request id's.

I think I have answered everyone's questions but if I missed one let me know.

Thanks,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[MajP]

The format that you apply to the timestamp is meaningless. The date 11/9/2009 7:45:23 AM is stored as the number
40126.3231828704

The format is meaniningless. The following are all equal
091109-074523
110909 07:45:23
07:45:23 9 Nov Monday 2009
11/09/09 074523
40126.3231828704

And any other format. If you save the time stamp as a string then
091109-074523 is different than 11/09/09 074523

So using a specific format on a date time stamp for a primary key is not logical. It does not do anything, unless you are saving it into a text field.
So your primary key should be text not a date field formatted in a specific manner.

To me that would be awfully confusing to see a PK in one query as 091109-074523 and in another query the same record would show the pk as 11/09/09 074523.

I have never done this so I will ask the larger audience. Is using a pure timestamp as a PK a good design?

 

[JoeAtWork]

For your purposes you should simply use a text field as MajP suggests. I would be wary of this design for a PK, as it is not impossible that two records could be created within the same second, especially if the records are generated in a loop or you have multiple users.

Please banish the thought of ever changing the regional settings to suit the idiosyncrasies of your program. It places your program in the classification of "malware" and my professional opinion would be that the programmer must be a rank amateur at best or someone purposely trying to do harm to my computer at worst.

 

[newguy86]

MajP,
Your suggestion is great! Unfortunately, I first tried making the field in the table a text field that would autogenerate a number incrementally and use that as the primary key, but I was unable to do so without using the autonumber feature (which is exactly what I am trying to avoid). So if you, or anyone else, have something that works and would satisfy my needs then I would be more than happy to implement it into our program.

JoeAtWork,
I understand what you are talking about and I wouldn't casually implement/suggest this setup because of the same reasons that you have already brought up. And your right, it is possible for two records in the table to be created at the same time but the chances of that happening in this program are pretty much next to null. And that's mainly because these requests will always be entered by hand and the number of users will be no more than about 50 or 60. So the worst that could happen is that two users would log a new request at the same time and the system would throw an error for either one or both of them and they would have to just close the form and make another attempt at logging the request.

Again, if anyone has any suggestions on how I could use a text field and generate a number incrementally without using the autonumber feature then I am all ears.

Thanks,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[MajP]

You may look into the FAQS there are several posts about autogenerating your own keys. Some of these are elaborate especially if working in a multiuser environment. But at the very simple build a text primary key field. Set the following as the default value in your text key field.

= format(now(),"yymmdd-hnnss")

 

[newguy86]

MajP,
Dude, thanks a million!

Not only does that suggestion satisfy my needs it also resolved the original issue that I had with the leading zeros dropping off.

I guess sometimes the simplist things can have the most profound affect.

Thanks again,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!