Format value to become 001

[Guest]

Hi,

I have set the default of my text field on a form to "Max+1" as follows:

="RV-" & (Val(Left((Right((DMax("[RV No]","[T_RECEIVED]")),6)),3))+1) & "-"
& (Format(Now(),"yy"))

Which works fine, except I get the result as "RV-5-04" instead of desired
"RV-005-04".

I guess I have to apply some formatting to it to become "005", but I have no
idea how to do it.

Can anybody please help me?

Thank you so much in advance.
Lana

 

[Ken Snell [MVP]]

="RV-" & Format(Val(Left((Right((DMax("[RV
No]","[T_RECEIVED]")),6)),3))+1),"000") & "-"
& (Format(Now(),"yy"))

 

[Guest]

Thank you Ken!

Works perfectly after I added second "(" after "Format" !!!

Thank you so much!

Lana

="RV-" & Format(Val(Left((Right((DMax("[RV
No]","[T_RECEIVED]")),6)),3))+1),"000") & "-"
& (Format(Now(),"yy"))

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have set the default of my text field on a form to "Max+1" as follows:

="RV-" & (Val(Left((Right((DMax("[RV No]","[T_RECEIVED]")),6)),3))+1) & "-"
& (Format(Now(),"yy"))

Which works fine, except I get the result as "RV-5-04" instead of desired
"RV-005-04".

I guess I have to apply some formatting to it to become "005", but I have no
idea how to do it.

Can anybody please help me?

Thank you so much in advance.
Lana

 

[Ken Snell [MVP]]

< g > Sorry 'bout the missing (.


--

Ken Snell
<MS ACCESS MVP>

Thank you Ken!

Works perfectly after I added second "(" after "Format" !!!

Thank you so much!

Lana

="RV-" & Format(Val(Left((Right((DMax("[RV
No]","[T_RECEIVED]")),6)),3))+1),"000") & "-"
& (Format(Now(),"yy"))

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have set the default of my text field on a form to "Max+1" as follows:

="RV-" & (Val(Left((Right((DMax("[RV No]","[T_RECEIVED]")),6)),3))+1)

&
"-"

& (Format(Now(),"yy"))

Which works fine, except I get the result as "RV-5-04" instead of desired
"RV-005-04".

I guess I have to apply some formatting to it to become "005", but I

have
no

idea how to do it.

Can anybody please help me?

Thank you so much in advance.
Lana