Dmax with criteria not working for me

[xollef]

Hello

I want to create an incrementing number in this format
YY_001
YY_002
Where YY is the year, thereby order 28 this year looks like this 04_028

I lookup the value with a dmax function:
And i get this to work

workid = DMax("[work_id]", "orders", "[work_id] Like '04_???'")

But this wont work, it gives the error null value:

Dim workid, search_criteria, year_abbreviation As String
Dim year As Date

year = Date
year_abbreviation = Right(year, 2)
search_criteria = year_abbreviation & "_???"
workid = DMax("[work_id]", "orders", "[work_id] Like '&search_criteria&'")

Why doesnt it find any records the second dmax function?

 

[xollef]

Oops

Maybe it was because i asked it here that I figured it out this fast.

I got it working like this:

Dim workid, search_criteria, year_abbreviation As String
Dim year As Date

year = Date
year_abbreviation = Right(year, 2)
search_criteria = year_abbreviation & "_???"
workid = DMax("[work_id]", "orders", "[work_id] Like '"&search_criteria&"'")

A couple of brackets missing, just thought to write here the solution if somone else has the same problem.

 

[scriverb]

Give this a try:

year_abbreviation = Right(year, 2)
search_criteria = year_abbreviation & "_"
workid = DMax("[work_id]", "orders", "[work_id] Like '" & search_criteria & "*'")

Let me know if this works for you. If not post back with the results.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]