Combine fields in a form

T

Tammy

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
 
K

Klatuu

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

Bob Miller

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
field SD:"SD-" & Format(RequestDate],"yy") & "-" & [ECRNumber]
 
T

Tammy

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...
 
T

Tammy

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...
 
T

Tammy

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

numbering in a form 2
preserving numbering in a SEQ after inserting a new field 3
Delete all data after a symbol in a field 0
Combine text values from many records into a single textbox 2
Data Combination 1
combine two fields in form to create one field in table 20
Using a combo box to open a form 0
Insert row(s) with vba 3

Top