Combine fields in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
First thank you for any help you can give me.
This has really been driving me nuts.
I have a table that has these two fields
ECRNumber Number
RequestDate Date/Time

In my form, and probably on reports also. I want to create the following,
the SD- is hard coded.
SD-YY-000

The last 3 digits will increment like this 001, 002, 003.
Thanks for any and all help
 
strNextNumber = "SD" & Format(Year(Date),"YY") &
Format(Nz(DMax([ECRNumber],"TableNameHere"),0) + 1, "000")
 
Hi Klatuu,
Thank you so much for your help. Do I put this in as the control on my text
box?
I tryed putting it on my button but I get an invalid use of null error.

Thanks again!
Tammy

Klatuu said:
strNextNumber = "SD" & Format(Year(Date),"YY") &
Format(Nz(DMax([ECRNumber],"TableNameHere"),0) + 1, "000")

Tammy said:
Hi,
First thank you for any help you can give me.
This has really been driving me nuts.
I have a table that has these two fields
ECRNumber Number
RequestDate Date/Time

In my form, and probably on reports also. I want to create the following,
the SD- is hard coded.
SD-YY-000

The last 3 digits will increment like this 001, 002, 003.
Thanks for any and all help
Click to expand...
Click to expand...
 
Hi Bob,
Thank you so much it works like a charm.
You did have a bracket missing :-)

Thanks,
Tammy

Bob Miller said:
Is the ECRNumber the 001, 002, 003 that you add in the SD-?
If so, you could create a query to base all reports and forms on with a
field SD:"SD-" & Format(RequestDate],"yy") & "-" & [ECRNumber]
Tammy said:
Hi,
First thank you for any help you can give me.
This has really been driving me nuts.
I have a table that has these two fields
ECRNumber Number
RequestDate Date/Time

In my form, and probably on reports also. I want to create the
following,
the SD- is hard coded.
SD-YY-000

The last 3 digits will increment like this 001, 002, 003.
Thanks for any and all help
Click to expand...
Click to expand...
 
Now that 2006 has come around, I need for the increment to start over at 001.
Any idea how I can do this?

Tammy said:
Hi Bob,
Thank you so much it works like a charm.
You did have a bracket missing :-)

Thanks,
Tammy

Bob Miller said:
Is the ECRNumber the 001, 002, 003 that you add in the SD-?
If so, you could create a query to base all reports and forms on with a
field SD:"SD-" & Format(RequestDate],"yy") & "-" & [ECRNumber]
Tammy said:
Hi,
First thank you for any help you can give me.
This has really been driving me nuts.
I have a table that has these two fields
ECRNumber Number
RequestDate Date/Time

In my form, and probably on reports also. I want to create the
following,
the SD- is hard coded.
SD-YY-000

The last 3 digits will increment like this 001, 002, 003.
Thanks for any and all help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top