Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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

Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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

Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:42 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.