Outlook 2007 form into access field format

[bear]

Hello
I have this code:

Set prp = ups.Find("Start1")
If TypeName(prp) <> "Nothing" Then
If prp.Value <> 0 Then
rst!Appointmentstarttime = prp.Value
End If
End If

Data is taken and imported into access table.
Problem:

Data imported in this format: 4:13:00 AM
I need that format to be converted into 4:13

Tried different things, non work.

Any help would be greatly appreciated!

..
Submitted using http://www.outlookforums.com

 

[Sue Mosher [MVP]]

Take a look at the Format() function.

 

[bear]

Thank you for response.

Can you give me an example of what might syntax look like in my example?

 

[Sue Mosher [MVP]]

The syntax for what? Please be specific. I don't have your original message
to refer to.

 

[bear]

This is what I have:

Set prp = ups.Find("Start1")
If TypeName(prp) <> "Nothing" Then
If prp.Value <> 0 Then
rst!Appointmentstarttime = prp.Value
End If
End If

I tried:

Set prp = ups.Find("Start1")
If TypeName(prp) <> "Nothing" Then
If prp.Value <> 0 Then
rst!Appointmentstarttime = Format(prp.Value, Date)
End If
End If

That gives out right format but something like 1999/4000/6000

Where should I put format fucntion?

Thank you.
Submitted using http://www.outlookforums.com

 

[Sue Mosher [MVP]]

Sorry, but I don't know what your variables refer to or what format you're
trying to achieve from what raw data value. Perhaps you should consult the
Help topic for the Format() function.

Also, if this is date/time data you're working with, why do you need to
format it at all?

 

[bear]

This is the top part

Option Explicit

Private ins As Outlook.Inspector
Private itm As Object
Private con As Outlook.AppointmentItem
Private appAccess As Access.Application
Private fso As Scripting.FileSystemObject
Private fld As Scripting.Folder
Private strAccessPath As String
Private dbe As DAO.DBEngine
Private strDBName As String
Private strDBNameAndPath As String
Private wks As DAO.Workspace
Private dbs As DAO.Database
Private rst As DAO.Recordset
Private ups As Outlook.UserProperties
Private fil As Scripting.File
Private prp As Outlook.UserProperty
Private msg As Outlook.MailItem


Public Sub SaveContactToAccess()

On Error GoTo ErrorHandler

Set ins = Application.ActiveInspector
Set itm = ins.CurrentItem

If itm.Class <> olAppointment Then
MsgBox "The active Inspector is not a contact item; exiting"
GoTo ErrorHandlerExit
Else
Set con = itm

Set appAccess = CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(acSysCmdAccessDir)
strAccessPath = strAccessPath & "Outlook Data\"
Debug.Print "Access database path: " & strAccessPath

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strAccessPath)
Set dbe = CreateObject("DAO.DBEngine.36")
strDBName = "../../../../form.mdb"
strDBNameAndPath = strAccessPath & strDBName
Debug.Print "Database name: " & strDBNameAndPath

Set fil = fso.GetFile(strDBNameAndPath)
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBNameAndPath)

Set rst = dbs.OpenRecordset("Form")
rst.AddNew

Set ups = con.UserProperties

Set prp = ups.Find("TransportDate2")
If TypeName(prp) <> "Nothing" Then
If prp.Value <> 0 Then
rst!TransportDate = Format(prp.Value, Date)
End If
End If.
Submitted using http://www.outlookforums.com

 

[Karl Timmermans]

This is not an Outlook issue. See MS Access help re: <Format Property -
Date/Time Data Type> - set your Access field definition accordingly (in your
case = <Short Time> for hh:nn format as per your original post).

What you're asking about is the Access "display" format which is different
then how datetime values are stored internally. If you want to
programmatically control the output format of a datetime field regardless of
the MS Access field format display setting - apply a <custom format> as
described in the help file.

Karl
______________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer/DataPorter/Exporter/Toolkit
"Contact import/export/data management tools for Outlook '2000/2007"
http://www.contactgenie.com

 

[bear]

Thank you for the lead.
The goal is to have format handled on import.
I am not sure what help file you were talking about in relation to custom fields.
Submitted using http://www.outlookforums.com

 

[Karl Timmermans]

#1 - Would suggest that you use newsreader when responding to the messages
to retain a thread of what the conversation is about. anyone reading your
response would have no idea as to what this is in reference to ....in any
case

#2 - Re: "what help file" - since your subject is in reference to "into
access field format" - am assuming you are familair with MS Access and
therefore not sure how differently to state the first sentence in my
original response: " See MS Access help re: <Format Property - Date/Time
Data Type> - set your Access field definition accordingly at teh table level
(in your case = <Short Time> for hh:nn format as per your original post).
Alternative is to search Outlook VBA help for "Format".

#3 - This has absolutely nothing to do with outlook custom fields (or
Outlook in general for that matter) but everything to do with dealing with
the <DateTime> field type. You're question relates to the < display> format
since your original/question asks how to show <4:13> instead of <4:13:00
AM> - The question/issue would be the same if you were inserting another MS
Access table datetime field instead of an Outlook <datetime> field.

For reference sake - the DATETIME field is stored internally as a "numeric
value" which gets tanslated and displayed according to the "format" rules
established (or to be more specific - it would never internally contain the
characters AM or PM). The <format date> rules (wherever set/used) govern how
any datetime gets displayed (or what gets extracted from the field). What
you <can> do is limit the datetime value elements inserted into a <datetime>
field (i.e. date only with a 00:00:00 time value, no date with time only
etc) but that is completely outside the scope of the original question.

Karl
______________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer/DataPorter/Exporter/Toolkit
"Contact import/export/data management tools for Outlook '2000/2007"
http://www.contactgenie.com