how do i combine a number and a string? plus increment....

G

Guest

Hi!
Can u help me how to combine string and number?
Ex.
CommodityType ProductNo ItemCode
MED 0001 --> MED-0001

AND HOW TO INCREMENT EACH PRODUCT NUMBER BASED ON ITS COMMODITY TYPE?

RecordNo ComID ItemCode Nomenclature Description Brand
1 AGR AGR-0001 Dog Food For Canine Dogs Alpo
2 AGR AGR-0002 Hog Feeds Pre-Starter Purina
3 AGR AGR-0003 Molasses For Horse Generic
4 ITS ITS-0001 Hard Disk 40 GB Seagate
5 ITS ITS-0002 Memory Module 512 MB Generic
6 AGR AGR-0004 Dewormer For Piglets B-Meg
7 MED MED-0001 Paracetamol Tablet, 250 mg Decolgen


pls?
thanx
 
M

madhivanan2001

Select max(mid(ItemCode,5,len(ItemCode)))+1 from table group by ComId

MAdhivanan
 
J

John Vinson

Hi!
Can u help me how to combine string and number?
Ex.
CommodityType ProductNo ItemCode
MED 0001 --> MED-0001

AND HOW TO INCREMENT EACH PRODUCT NUMBER BASED ON ITS COMMODITY TYPE?

RecordNo ComID ItemCode Nomenclature Description Brand
1 AGR AGR-0001 Dog Food For Canine Dogs Alpo
2 AGR AGR-0002 Hog Feeds Pre-Starter Purina
3 AGR AGR-0003 Molasses For Horse Generic
4 ITS ITS-0001 Hard Disk 40 GB Seagate
5 ITS ITS-0002 Memory Module 512 MB Generic
6 AGR AGR-0004 Dewormer For Piglets B-Meg
7 MED MED-0001 Paracetamol Tablet, 250 mg Decolgen
Click to expand...

The ProductNo field should NOT contain the CommodityType value. I'd
suggest instead using two separate fields, a ComID and an Integer
numeric ProductNo. You can concatenate them for display purposes with
a calculated field in a Query:

ShowItemCode: [ComID] & "-" & Format([ProductNo], "0000")

To increment the ProductNo, you can use VBA code in the Form you use
to update the table (and yes, you MUST use a form; table datasheets
are much too limited). I'd suggest having a Combo Box cboComID to
insert the ComID value (from a separate table of commodity types); in
its AfterUpdate event put code like:

Private Sub cboComID_AfterUpdate()
If IsNull(Me!txtProductNo) Then ' don't step on existing data
Me!txtProductNo = NZ(DMax("[ProductNo]", "[YourTableName]", _
"[ComID] = '" & Me!cboComID & "'")) + 1
End If
End Sub

You probably should also have code in the combo's BeforeUpdate event
to cancel the change if there is already a ProductNo assigned,
otherwise you'll risk errors (changing AGR-0003 to ITS-0003 would
probably NOT be desirable!)

John W. Vinson[MVP]
 
Top