Merge two fields to one for ID

[fenhow]

Hi,

I have a form with three fields.

DateClosed, OrderID, and CloseNum

What I want to do is auto create the CloseNum by merging DateClosed and OrderID.

Is this possible?

Thanks.

Fen How

 

[mhartman]

Yes!

Make a query with those three fields in it. Then add in a fourth column and concatenate the three other fields to that column.

Regards
Mark

 

[Brianwarnock]

In the control source of CloseNum put =[DateClosed] & [OrderID]

Brian

 

[rainman89]

or if you dont want to have the / in the date

CloseNu=Format([DateClosed],"mmddyyyy") & [OrderID]

 

[fenhow]

Great,

This works;

CloseNu=Format([DateClosed],"mmddyyyy") & [OrderID]

How do I get the database to store it in the CloseNu table, not just show it on teh form?

Fen

 

[rainman89]

do you have a save button or anything of the sorts?

or you can put something in the afterupdate event of the form to set
CloseNu=Format([DateClosed],"mmddyyyy") & [OrderID]

or set the control source of the textbox to closenu and put that code in as the default value

many options

 

[pbaldy]

Why bother saving it when it's so easy to calculate on the fly, as you've already found?

 

[fenhow]

When I try this I am getting an errror..

Private Sub Form_AfterUpdate()
CloseNum = Format([DateClosed], "mmddyyyy") & [OrderID]
End Sub


I am trying to store the value of this;

CloseNum=Format([DateClosed],"mmddyyyy") & [OrderID]

In the table CloseNum..

Thanks...

 

[fenhow]

I guess I want to save it for reporting and future references... do you think I do not need to save it?

I am open for suggestion..

Fen

 

[rainman89]

what is stored in the table closenum? besides this number?

 

[pbaldy]

You're going to jump through hoops to save it, plus you'd then have to worry about catching if anyone edited either of the fields so you could update it. I say just use the formula you already have on forms and reports when you want to display the value and not bother with the saving/updating.

 

[missinglinq]

I agree with Paul about not storing it! The part about recalculating it every time you need it could get tiresome. What I'd do would be to delete the CloseNum field from the table, then base my forms/reports on a query of the underlying tables, and in the query have a calculated field CloseNum. In a blank field in the Query Design Grid enter:

CloseNum:Format([DateClosed],"mmddyyyy") & [OrderID]

Now you simply refer to this calculated field, CloseNum, in the query anytime you need it in a form or report!


As to this (which you really shouldn't be doing anyway)

When I try this I am getting an errror..

Private Sub Form_AfterUpdate()
CloseNum = Format([DateClosed], "mmddyyyy") & [OrderID]
End Sub

You didn't give us the error you were receiving, which makes remote diagnosis rather dicey, but in this situation, in the code module, the syntax would be:

Private Sub Form_AfterUpdate()
Me.CloseNum = Format(Me.DateClosed, "mmddyyyy") & Me.OrderID
End Sub

Linq