Complex Calculated Field - HELLLLPPP!!

[tmgstudio]

Greetings everyone. I am not an Access Guru so please take me request with a grain of salt.

I am the marketing admin and web developer for the company I work for (co-op telcom). I have built a simple Access database that interfaces with a legacy service order system we utilize company wide. The database simply tracks marketing jobs we work and publishes them as service orders to the system. The problem I have is that I have to format the job number very specifically to fit the legacy systems expected input requirements and I haven not been able to figure out how to do this.

Below is an example of what a series of job numbers must look like. Note the date info preceding each example

Jobs entered on April 20, 2007

070420-01
070420-02
070420-03

Jobs entered on April 21, 2007

070421-01
070421-02
070421-03

The structure of the string is YYMMDD-'Sequential number restarting each day'

The date portion is no problem and nor is the dash. Where my problem lies is in the sequential number restarting each day.

Any thoughts on how to do this. I am guessing this should be some kind of VB or if then statement, but I have no idea where to begin.

To show that I appreciate the help anyone can give with regard to this mater, I am offering a free 1 year hosting package (200M disk space/2 Gigs of bandwidth/mysql/php/unlimited email addy's w/hsphere control panel) to the first person who can help me with this problem.

Thank you for your time and assistance.

 

[Alc]

You could carry out a comparison on the last two characters before the dash (i.e. use the 'Instr' function to locate where the dash is).
li_Position = Instr(stringname,"-")

Strip off everything to the right of the two numbers (using 'Left' and the length of the string minus the number found above), then use the 'Right' function again to get just the last two numbers.
str_1 = Left(Len(str_Original)-li_Position)
str_2 = Right(str_1,2)

Compare these numbers to the current date (fusing Format(now, "DD"), I believe - double check this).

If the one value matches the other, increase the sequence by one.
If the values differ, revert to 1 and start again.

If str_2 = Format(Now(),"DD") Then
increase the sequence by one.
else
revert to 1 and start again.
end if

Hope that makes sense.

 

[tmgstudio]

Ok ... forgive me. I am a newbie to Access and I wish that I understood how to put all of that together, but I am afraid that I am at a loss.

I have created a field in the "job" table for my database called JobNumber. This field is set as a text Data Type. My understanding is that I would create an expression which performs the function I am looking for. Is this correct?

I would then enter this expression in the "Default Value" area of the field properties under the General tab?

Though I believe in the idea of teaching a man to fish and he will never go hungry, I am afraid that right now, learning how to fish will mean baiting a hook that I cannot handle. Any hand holding would be wonderful.

 

[tim419762]

If you have a form set up to view the data in the table, you could write a function that would return this value. I think you'd have to use a form, as I don't believe that you can write a custom function and have it be the default value in a table, but you can in a form.

Create a new Module, name it whatever you'd like, and then write the function. The function would look something like this:

Public Function GetNewNumber() As String
Dim dblNumber As Double
dblNumber = DCount("[JobNumber]", "Job", "Left([JobNumber],6) = '" & Format(Date, "yy") & Format(Date, "mm") & Format(Date, "dd") & "'") + 1

If dblNumber < 10 Then
GetNewNumber = Format(Date, "yy") & Format(Date, "mm") & Format(Date, "dd") & "-0" & dblNumber
Else
GetNewNumber = Format(Date, "yy") & Format(Date, "mm") & Format(Date, "dd") & "-" & dblNumber
End If

End Function

In the DCount line, replace the two references to 'JobNumber' with the field name, and the one reference to 'Job' with the table name.

Then, in the form, create a control that has its controlsource set to the 'JobNumber' field. Then, in the Default Value, type GetNewNumber()

This should set it so that whenever you create a new record in the form, the GetNewNumber function is called and it should calculate the latest value.

 

[WayneRyan]

TMG,

Use your form's BeforeInsert event:

Me.YourKeyField = Format(Nz(DMax("Mid([YourKeyField], 8)", _
                                 "YourTable", _
                                 "Left(YourKeyField, 6) = Format(Date, "YYMMDD")), 0) + 1), "00")

btw, it'd be a bit easier for you if you broke the "key" down to two seperate fields - dtmDateEntered and intSequence

Then:

dtmDateEntered defaults to Date, and

Me.intSequence = Nz(DMax("intSequence", "YourTable", "[dtmDateEntered] = Date"), 0) + 1

Then for display --> Format(dtmDateEntered, "YYMMDD") & "-" & Format(intSequence, "00")

Much less complicated (and more easily allows for > 99 entries).

hth,
Wayne