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

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

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

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

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

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

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

David