Combine 2 fields into one field

[spectraflame]

Is it possible to combine the information from 2 fields into a single field and store the result in a table?

I would like to be able to join the current date and the users name into a single field called "Last Accessed By".

Example:

Current Date field equals = 09/10/04
Users name field equals = Spectraflame
Last Accessed By field = 09/10/04 Spectraflame

Matthew

 

[bat17]

You could use the Dmax() function to find the highest number used by a department and just add 1 to it when you create your new record.
If this is to be used in a multi-user enviroment it may be better to store the last used number for each department in a lookup table that is updated whenever a number is used.

HTH

Peter

 

[spectraflame]

Well in addition to wanting to know the number of orders place on any given time period (which I can do), management would like to have the orders per department in consecutive order and they wish for the numbering to start over at the beginning of a new year.

This whole thing is just getting worse for me as the days go by. I appreciate all of your help and I will be posting more because I still have a great deal of questions that I cannot answer.

Thanks again,
Matthew

 

[bat17]

The Dmax() function can do all of that for you
You just need to have criteria that will check against department and year.

Peter

 

[spectraflame]

Not sure if I fully understand the DMAX function and if can work with the way I have things in my table. I am open to a better suggestion if there is one.

I have the following columns in the WorkOrdertbl:
- RecordID# - Auto Number
- WOType - Text - Combo box query to WOTypestbl

I am using the following code to give me the desired format for the order numbers on my forms and on my reports:

=Left([WOType],1) & Format([EntryDate],"yy") & " - " & [RecordID#]

The only way that I could figure out how to assign a number to each order was to use the RecordID#. Maybe this is not what I should be doing? Should I just be using the RecordID# to assign a number to each record and not for my actual service order number?

Can the DMAX function give me my results based on the manner in which I am populating my table?

Matthew

 

[bat17]

using dmax you will have a composite key for each record comprising of the record ID, the DeptID and the year. I would probably keep the Auto id in there as well as it is a lot easier to use for joins than trying to remeber to connect up all 2 fields each time.

you will need some thing like ( untested!)
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!RecordID = Nz(DMax("[RecordID]", "[tblData]", "[DeptID]=" & Me!deptID & " And Year([dDate])= year(date())")) + 1
End Sub

To add the ID for you, it assumes a field on the form called recordID that will hold the ID and the same field in the table to look it up in, the criteria means that it will will find the higest number it can find for that department in the current yer and add 1 to it. If it does not find a match then the NZ wrapper wil return 0 and it will all start off at number 1 again.


HTH

Peter

 

[spectraflame]

Will this composite key be an actual field in my table?

Matthew

 

[mdmilner]

http://www.mrexcel.com/board2/viewtopic.php?t=104826

This was the former thread. And yes, there's a DMAX example or two in it doing precisely as Bat17 is suggesting above. Only thing is the routine I wrote feels clunky to me - might be a better way to do it if anybody wants to take a crack at it i'd be interested in seeing the better option.

Nax

 

[bat17]

It will not be an actual field but you can assemble it as one in a report in a similar manner to how you were shown earlier.

Leave RecordID as a record identifier and create a new field for the new ID say, WoID

=Left([WOType],1) & Format([EntryDate],"yy") & " - " & [WoID]

Your DMax function would then look some thing like (if I have understood your layout!)
Me!WoID = Nz(DMax("[WoID]", "[tblData]", "[WOType]=" & Me!WOType & " And Year([EntryDate])= year(date())")) + 1

Peter

 

[spectraflame]

Would the DMAX statement then be placed in a code expression for the BEFORE event?

If so, would it look like this? This VB code stuff is still new to me.

Private Sub WOID_BeforeUpdate(Cancel As Integer)
Me!WOID = Nz(DMax("[WoID]", "[WorkOrdertbl]", "[WOType]=" & Me!WOType & " And Year([EntryDate])= year(date())")) + 1
End Sub

 

[bat17]

I would do it on the Form_BeforeInsert this will then add the ID as soon as someone starts typing in a new record.

Is this designed to be a multiuser form for data input as you might want a differnt strategy for that.

Peter