Outlook 2007 form into access field format

Status
Not open for further replies.
B

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!
 
S

Sue Mosher [MVP]

Take a look at the Format() function.

Sue Mosher

"bear" <swin_1234[at]yahoo[dot]com> wrote in message

news:OSLHTK1VKHA.1792@TK2MSFTNGP04.phx.gbl...
> 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!

>
 
B

bear

Thank you for response.

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

Sue Mosher [MVP]

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

to refer to.

Sue Mosher

"bear" <swin_1234[at]yahoo[dot]com> wrote in message

news:e$mpAENWKHA.4780@TK2MSFTNGP05.phx.gbl...
> Thank you for response.

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

>
 
B

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.
 
S

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?

Sue Mosher

"bear" <swin_1234[at]yahoo[dot]com> wrote in message

news:O$llUkNWKHA.4140@TK2MSFTNGP06.phx.gbl...
> 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.
 
B

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.
 
K

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

________________

"bear" <swin_1234[at]yahoo[dot]com> wrote in message

news:O$llUkNWKHA.4140@TK2MSFTNGP06.phx.gbl...
> 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.
 
B

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.
 
K

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

________________

"bear" <swin_1234[at]yahoo[dot]com> wrote in message

news:euxHBOPWKHA.1280@TK2MSFTNGP04.phx.gbl...
> 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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J Can Click to Drag Custom Form Field But Cannot Drop When Designing in Outlook 2007 Outlook VBA and Custom Forms 2
L Outlook 2007 Contact Form Code Not Working Using Outlook 20
P Task form created in outlook 2007 Outlook VBA and Custom Forms 2
L Outlook 2007 Custom Form and Toolbar Not Working Using Outlook 5
L Outlook 2007 Contact Form Webpage Using Outlook 23
J New Outlook 2007 form - try to send and get error Operation Failed Outlook VBA and Custom Forms 11
Z outlook 2007 - opening a contact from the search results doesn't use the custom form Using Outlook 10
L Outlook 2007 Macro to Run a Script From a Contact Form Using Outlook 41
L Outlook 2007 Contact Form - Note Fields Using Outlook 25
D Outlook 2007 Custom Form - Dual Addresses and Mailing Address Using Outlook 6
D Customizing Tasks form in Outlook 2007 Using Outlook 4
R Clean out the "Other Calendars" form Outlook 2007 sp2 Using Outlook 1
M open outlook contacts in access 2007 form Using Outlook 1
M Phone Message form in outlook 2007. How to start? Outlook VBA and Custom Forms 2
K Publish form to folder - Outlook 2007 Outlook VBA and Custom Forms 1
F Outlook 2003 form cannot be loaded in 2007 Outlook VBA and Custom Forms 1
B Importing Contacts Does Not Use Defined Custom Form in Outlook 2007 Outlook VBA and Custom Forms 1
R Changing the default message form in Outlook 2007 Outlook VBA and Custom Forms 1
J Creating form in Outlook 2007 Outlook VBA and Custom Forms 1
S Outlook 2007 Contacts Form - how to link to other contacts? Outlook VBA and Custom Forms 2
D Form Outlook 2007 Outlook VBA and Custom Forms 4
J Extra text on an Outlook 2007 custom form Outlook VBA and Custom Forms 2
K Accessing contact's custom properties in a Outlook 2007 form region Outlook VBA and Custom Forms 1
S Make field searchable in custom form in outlook 2007 Outlook VBA and Custom Forms 1
T Outlook 2007 Form Template Icon Outlook VBA and Custom Forms 1
T Outlook 2007 Email Form Outlook VBA and Custom Forms 8
S Outlook 2007: automating form with macro Outlook VBA and Custom Forms 2
J Outlook 2007 using custom form created for Outlook 2003 Outlook VBA and Custom Forms 2
B Outlook 2007 Custom Form does not display in 2003 Outlook VBA and Custom Forms 3
S Outlook 2007 crash linked to gdiplus.dll Using Outlook 0
S Outlook 2007 - Automatic purge fail Using Outlook 0
J outlook 2007 doesn't let me choose which .pst to search Using Outlook 2
D Outlook 2007 vs. Outlook 2010 -- ToDo Bar Using Outlook 0
D Outlook 2007 on 365 Using Outlook.com accounts in Outlook 2
S Macro for other actions - Outlook 2007 Outlook VBA and Custom Forms 23
S Verwendung von Outlook 2007 Using Outlook 0
A Arthur needs help with 2007 Outlook e-mail Using Outlook.com accounts in Outlook 3
M PST import from Outlook 2007 to 2010 - Address Book contacts all in 1 group Using Outlook 4
S outlook 2007 calendar search Using Outlook 6
B Migrate Outlook 2007 to Office 365 Using Outlook 3
X I have met my waterloo trying to resolve embedded graphics problem with outlook 2007 and now 2016 Using Outlook 1
R Outlook 2007 only loads some appointments Using Outlook 0
C Move Outlook 2007 to new PC with Outlook 365 Using Outlook 3
J Outlook 2007 Hide Messages Option not Available Using Outlook 2
S Outlook 2007 Calendar instant search problem. Windows 7 Using Outlook 4
S Outlook 2007 Calendar instant search problem. Windows 7 Using Outlook 0
B Server errors Outlook 2007 Using Outlook 1
S Reboot of frozen windows7 results in changed outlook 2007 settings Using Outlook 1
S Outlook 2007 printing wrong email address at top of page Using Outlook 8
M Configure outlook 2007 to accept digital signatures Using Outlook 2

Similar threads

Top