Right in Dmax 3

[lars7]

Hi,
I have the following code to give me the max of a "codeno" in a field but the first part of the "codeno" is text i,e, DIAG1000

Countmax = DMax("Right([Job Ref No],4)", "[Vac Board]")
Me.[Job Ref No] = Countmax + 1

my problem is that the number part has reached 9999 and if i change the code to:

Countmax = DMax("Right([Job Ref No],5)", "[Vac Board]")
Me.[Job Ref No] = Countmax + 1

I get a type mismatch.

Does anyone know how i can fix this.

Thanks in advance.

 

[nickdel]

you have the "Right" function in quotes which make's it part of th string.

Countmax = DMax(Right("[Job Ref No]", 5), "[Vac Board]")
    Me.[Job Ref No] = Countmax + 1

Cheers

Nick

 

[lars7]

Hi NickDel,

I'm getting a syntax error " Max(f No]) " with that code.

 

[nickdel]

Ok, sorry didn't test that before, try something like this instead:

Countmax = DMax("[Job Ref No]", "[Vac Board]")
Countmax = right(Countmax,5)
    Me.[Job Ref No] = Countmax + 1

Nick

 

[TomCologne]

Try this:

Countmax = DMax("Right([Job Ref No],5)", "Vac Board")

TomCologne

 

[lars7]

hi again,

I got the type mismatch with that one.

 

[nickdel]

Which one? Tom's or mine?

 

[TomCologne]

How's Countmax declared?

TomCologne

 

[lars7]

Hi,
I get the error with both.

I should say that this is not my database and I'm thinking I will have to find a new way of generating this reference number. Thanks for your replies.

 

[TheAceMan1]

HOW ARE YA lars7 . . .

Why not update existing data in the table to five digits and be done with it! Just pop the following into the [blue]SQL View[/blue] of a new query ([purple] be sure to backup the table or db before attempting this![/purple]) and run the query:

[blue]UPDATE [Vac Board] SET [Job Ref No] = Left([Job Ref No],Len([Job Ref No])-4) & "0" & Right([Job Ref No],4);[/blue]

[blue]Diag1000 becomes Diag01000[/blue] . . .

See Ya! . . . . . .

 

[Zion7]

lars, I believe your error is because your format is
changing "arbitrarily".

You MUST first check your length, to determine Right,4 or
Right 5.

Dim x As integer

x = IIF(Len([Job Ref No]) = 8,4,5)
Countmax = DMax("Right([Job Ref No],x)", "[Vac Board]")

BUT, what's wrong with Mid([Job Ref No],5)?

Blah, blah, blah... But, I personally suggest you
pay heed to AceMan's suggestion.
and if you anticipate that you will pass the 99999 mark,
then Modify Ace's code accordingly .

Ultimately, you should consider changing that field
to Integer, and concatenating "DIAG", just for viewing
purposes...
txtDiag.ControlSource = "DIAG" & [Job Ref No]

 

[lars7]

Hi Guys,

Sorry for the delayed reply.

Ace

Your suggestion would be a solution but there are related records in another table and adverts sent to the internet with those job reference numbers attached so I don't think my boss would go for that.

Zion

If he would only have gone for concatenation then where would be no problem now.
He had a table( on a server) with three fields, two fields barely populated and the other was an auto number that started at 6000 and then jumped numbers so that with only 358 records it reached 9999 and then locked so that it could not be updated anymore. He used this to generate the reference number.

I am just looking for a short term fix as our recruitment departments are about to merge and some of the others haven't been using the text prefix in there reference numbers so we will have to start afresh anyways and if it up to me it will be with DMax and concatenation.

 

[Zion7]

sorry lars7, are you saying that you do have a temp. fix then?
Or, are still in need (no suggestions above feasible?)

Would not Mid() work? or IIF()...

 

[TheAceMan1]

lars7 . . .

Sorry to get back so late. Here's a function that'll do the trick. Returns the full text with incremented value and takes care of the 5th digit . . .

[blue]Public Function NextDiag() As String
   Dim Ans As Long
   
   Ans = DMax("Val(Right([Job Ref No], 5))", "Vac Board")
   
   If Ans = 0 Then
      Ans = DMax("Val(Right([Job Ref No], 4))", "Vac Board")
   End If
   
   NextDiag = "Diag" & Ans + 1
   
End Function[/blue]

See Ya! . . . . . .

 

[PHV]

the first part of the "codeno" is text i,e, DIAG1000
Countmax = Val(DMax("Mid([Job Ref No],5)", "[Vac Board]"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

Howdy [blue]PHV[/blue] . . .

The first part returns zero (0) if there's non-numeric character in the 5th position from the right, else it returns the 5 digit numeric value . . . Hence the second part! . . . [blue]Try it! . . .[/blue]

See Ya! . . . . . .

 

[Zion7]

Yes AceMan but, why would there be a discrepancy if
starting from the left MID()?
Unless of course the prefix changes, which all these posts
have implied otherwise?

 

[TheAceMan1]

Zion7 . . .

I have not mentioned any descrepancy . . . I've just presented code that will do what you ask! If you want to modify it so it uses the [blue]Mid[/blue] function, the choice is yours!

Have you tried it?

See Ya! . . . . . .

 

[Zion7]

Oh, I'm sorry AceMan, I thought you were contesting
PHV's code.
...is it that you thought, he was contesting yours?
...I think his quote was making reference to Lars' 1st post.

I can tell right away, that yours will work, no doubt, but,
I thought PHV was just showing a more succinct and
reliable approach( will Right(JobRefNo,6) ever be required?)

 

[lars7]

Hi Guys,

Sorry again for the late reply but this old database is keeping me very busy.

I will test all the codes on Monday.

Ace:

I see with your code that the prefix would have to be "Diag" but it can change to the different departments that the vacancies are for i,e, "GWAC" "GPPSU" so the prefix can be 4 or 5 digits. Sorry if that wasn't made clear enough.