Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 18th, 2004, 06:38 AM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Convert Part of String to Date

Help

I have a form that identifies several quality control tasks that were not performed. The first two fields are TaskID and TaskDate. TaskID is a Key Field and has 15 characters that identifies which task was not completed. The first 4 characters are the first 4 letters of the building where the task was not performed, the next 8 characters is the short date of the quality control flaw and the last two characters are the number of the quality control flaw if we discover more than one for a particular building on the same day.

Here is an example of how the field would look like if we discovered a third quality control flaw in the cafeteria on October 18, 2004 - CAFE10/18/04-3

The second field, TaskDate, is the date of the quality control flaw. Since I am already entering the date as part of a string in TaskID, how can I convert the date part of the string to an Access date and automatically place it in TaskDate after I have finished entering the TaskID so my data entry person does not have to duplicate her effort by entering the date twice?

Also, I want to be able keep the table sorted by TaskDate.

Thanks for your assistance,

David

 
Old October 18th, 2004, 12:50 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   Since you are already entering the date in this field, there is no reason to put it in another table field. You may generate it in a query, or in a report.

Put a Function in a Module and do something like...

Function NewDate()

Dim rstTable As Recordset
    Dim db As Database
    Dim stNewDate As String
    Dim stTaskName As String
    Dim stTaskJob As String
    Dim stTaskMonth As String
    Dim stTaskDay As String
    Dim stTaskYear As String
    Dim stNewArray As Variant
    Dim stNewTable As String

    stNewTable = "Table"
    stNewArray = Array(3)

    Set db = CurrentDb()
    Set rstTable = db.OpenRecordset("Table", dbOpenDynaset)


    rstTable.MoveFirst
    rstTable.Find "[ID] = " & Reports![rptTaskDateReport].[ID]

    stNewArray = Split(rstTable!TaskID, "/")
    stTaskName = stNewArray(0)
    stTaskDay = stNewArray(1)
    stTaskJob = stNewArray(2)

    stTaskMonth = Right(stTaskName, 2)
    stTaskYear = Left(stTaskJob, 2)

    stNewDate = stTaskMonth & "/" & stTaskDay & "/" & stTaskYear

    NewDate = stNewDate

 And then put =NewDate() as the Control SOurce on a text box on the form or report. Note that the form or report name must be specified in the function.

You may just want to figure out a way to use the arrays to solve the problem another way.

I can get this to work in some databases, but in the sample one I was working on, it wouldn't work.

I hope this helps.

mmcdonal
 
Old October 18th, 2004, 12:58 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

When I used to following code in VBScript, I got the output "10/18/04":

    Dim stNewDate
    Dim stTaskName
    Dim stTaskJob
    Dim stTaskMonth
    Dim stTaskDay
    Dim stTaskYear
    Dim stNewArray
    Dim rstTable

    rstTable = "CAFE10/18/04-3"
    stNewArray = Array(3)


    stNewArray = Split(rstTable, "/")
    stTaskName = stNewArray(0)
    stTaskDay = stNewArray(1)
    stTaskJob = stNewArray(2)

    stTaskMonth = Right(stTaskName, 2)
    stTaskYear = Left(stTaskJob, 2)

    stNewDate = stTaskMonth & "/" & stTaskDay & "/" & stTaskYear

    MsgBox stNewDate

To make this "10/18/2004" just add this for stNewDate:

stNewDate = stTaskMonth & "/" & stTaskDay & "/" & "20" & stTaskYear

I hope the clue is somewhere in here for you. In any event, there is no need to store this in the table since it is already there.



mmcdonal
 
Old October 18th, 2004, 01:53 PM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mmcdonal,

I will attempt this.

One follow-up question: will this procedure convert the string to a date? I see your logic in not putting a seperate date in the table since it is already in the string field. However, a few of my reports will need to search the table based on a date range. I do see how I could manipulate the data in a report based on your example, but I still need to be able to see that date segment of the string as date field.

David

 
Old October 19th, 2004, 07:41 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

David, note that mccdonal's code returns a type string, not date. You should declare the return variable as a date, dtmNewDate, and its components (month, day, and year) as type integer. Then

intTaskDay = CInt(stNewArray(1))
intTaskMonth = CInt(Right(stTaskName, 2))
intTaskYear = CInt(Left(stTaskJob, 2))

dtmNewDate = DateSerial(intTaskYear, intTaskMonth, intTaskDay)

This will give you a true date, not a string. But remember since the date is buried in the Task ID, you shouldn't have to STORE the date at all. If you need to see it, just display it using this function mccdonal gave you, but storing it would be a waste of space since it's already in the Task ID.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old October 20th, 2004, 01:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Thanks for the follow up on the data type. If there will be further calculations, it will definitely need this.

I was also thinking that instead of generating a date from this key, use an invisible key (auto) and then generate this case number from the date and some other look up to "CAFE" and the other names.

This is a more elegant solution.


mmcdonal
 
Old October 20th, 2004, 03:31 PM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Greg and mmcdonal. With your help, I got it.

David





Similar Threads
Thread Thread Starter Forum Replies Last Post
how to convert string to date tgopal Javascript 2 March 18th, 2008 03:33 AM
convert string to date collie SQL Server 2000 4 March 15th, 2007 03:50 PM
convert string to date deeptibg SQL Server DTS 1 December 22nd, 2005 08:47 PM
Conversion part of string to date MRvLuijpen Access 5 May 18th, 2004 02:31 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.