By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,688 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,688 IT Pros & Developers. It's quick & easy.

Incrementing the value of a column containg mixed character and numerical values

P: 8
Dear experts

how we can automaticaly increment the column value which contains values like 07-05-ivan-A-0001. the final integer part alone have to be incremented.

Thanks in advance
May 31 '07 #1
Share this Question
Share on Google+
17 Replies

theaybaras
P: 52
Dear experts

how we can automaticaly increment the column value which contains values like 07-05-ivan-A-0001. the final integer part alone have to be incremented.

Thanks in advance
Hi jsredhar,

A quick question... how is this column value generated? is it entered as 07-05-ivan-A-0001, or is 0001 the autonumber, and 07-05-ivan-A entered into separate fields? Depending on your structure and what you're wanting to store in the table, I'll have different things to say.

All the best

theAybaras
May 31 '07 #2

P: 8
Hi jsredhar,

A quick question... how is this column value generated? is it entered as 07-05-ivan-A-0001, or is 0001 the autonumber, and 07-05-ivan-A entered into separate fields? Depending on your structure and what you're wanting to store in the table, I'll have different things to say.

All the best

theAybaras
Dear theAybaras

the first portion (07) is generated from year(date) second part (05) is generated from using month(date) and the third part is the username which can be retrieved from session variable of ASP and the fourth part 'A 'is to be entered by the user and the final part(0001) is predefined for the very first entry. following are the syntax

<%=strYear%>&"-"&<%=strMonth%>&"-"&Session("username")&"-"&textboxvalue(A)&"-"&0001

This is how i have generated it for the very first entry so this will store as 07-05-ivan-A-0001 in Projectcode field of my Project table There is no separate field for integer part alone.

Thanks
May 31 '07 #3

theaybaras
P: 52
Dear theAybaras

the first portion (07) is generated from year(date) second part (05) is generated from using month(date) and the third part is the username which can be retrieved from session variable of ASP and the fourth part 'A 'is to be entered by the user and the final part(0001) is predefined for the very first entry. following are the syntax

<%=strYear%>&"-"&<%=strMonth%>&"-"&Session("username")&"-"&textboxvalue(A)&"-"&0001

This is how i have generated it for the very first entry so this will store as 07-05-ivan-A-0001 in Projectcode field of my Project table There is no separate field for integer part alone.

Thanks
okay, I'll play with some things and get back to you! We'll see if anyone who is much more expert than me posts too :)

theAybaras
May 31 '07 #4

P: 8
okay, I'll play with some things and get back to you! We'll see if anyone who is much more expert than me posts too :)

theAybaras
Dear theAybaras

thanks and waiting for the solution
May 31 '07 #5

theaybaras
P: 52
Dear theAybaras

thanks and waiting for the solution
Hi js... just letting you know I'll be longer than I thought, days off turned into being called in for emergency work for a day or two...

I'll keep working on it as I can, if anyone else sees this and has an idea, I hope they'll share it with us!

theAybaras
May 31 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Dear theAybaras

the first portion (07) is generated from year(date) second part (05) is generated from using month(date) and the third part is the username which can be retrieved from session variable of ASP and the fourth part 'A 'is to be entered by the user and the final part(0001) is predefined for the very first entry. following are the syntax

<%=strYear%>&"-"&<%=strMonth%>&"-"&Session("username")&"-"&textboxvalue(A)&"-"&0001

This is how i have generated it for the very first entry so this will store as 07-05-ivan-A-0001 in Projectcode field of my Project table There is no separate field for integer part alone.

Thanks
Try using a DMax function as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DMax("CLng(Right([Projectcode],4))","TableName") + 1
Jun 1 '07 #7

P: 8
Try using a DMax function as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DMax("CLng(Right([Projectcode],4))","TableName") + 1
Dear mmccarthy the datatype of the Projectcode field is set to text, when we use the above method it returns following error "Datatype mismatch in criteria expression".

Thanks for your response. Waiting for the solution.

jsredhar
Jun 1 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry,

I've tested it out and can't get the expression to evaluate in the correct order. Try this instead. You can adapt it to include your other criteria
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As DAO.database
  3. Dim rs As DAO.Recordset
  4. Dim num As Long
  5.     Set db = CurrentDb
  6.     Set rs = db.OpenRecordset("SELECT Max(Right([Projectcode], 4)) As MaxNum FROM TableName")
  7.  
  8.     num = rs!MaxNum + 1
  9.  
  10.     rs.Close
  11.     Set rs = Nothing
  12.     Set db = Nothing
  13.  
Jun 1 '07 #9

theaybaras
P: 52
Nice work, indeed, mmccarthy! I'm really slow and buggy with vb, (painfully so) but this is the track I was on! makes me feel warm and fuzzy inside ;)

I hope this works for you js... since mmccarthy is on your side, I'll leave this in those very capable hands :) Perhaps she could take a look at your other question if you still need help with that.?
Jun 1 '07 #10

P: 8
Sorry,

I've tested it out and can't get the expression to evaluate in the correct order. Try this instead. You can adapt it to include your other criteria
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As DAO.database
  3. Dim rs As DAO.Recordset
  4. Dim num As Long
  5.     Set db = CurrentDb
  6.     Set rs = db.OpenRecordset("SELECT Max(Right([Projectcode], 4)) As MaxNum FROM TableName")
  7.  
  8.     num = rs!MaxNum + 1
  9.  
  10.     rs.Close
  11.     Set rs = Nothing
  12.     Set db = Nothing
  13.  
Dear mmccarthy

It's wonderful and it works fine,I really wants to thank you for the fine solution.

With thanks once again

jsredhar
Jun 3 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Dear mmccarthy

It's wonderful and it works fine,I really wants to thank you for the fine solution.

With thanks once again

jsredhar
You're welcome.

Glad it's working for you.

Mary
Jun 3 '07 #12

hyperpau
Expert 100+
P: 184
Sorry,

I've tested it out and can't get the expression to evaluate in the correct order. Try this instead. You can adapt it to include your other criteria
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As DAO.database
  3. Dim rs As DAO.Recordset
  4. Dim num As Long
  5.     Set db = CurrentDb
  6.     Set rs = db.OpenRecordset("SELECT Max(Right([Projectcode], 4)) As MaxNum FROM TableName")
  7.  
  8.     num = rs!MaxNum + 1
  9.  
  10.     rs.Close
  11.     Set rs = Nothing
  12.     Set db = Nothing
  13.  
Dear mmccarthy,

I think this is the answer I have been looking for.
1) Where do i paste this code? would it be on the form_load() event?
2) what do i replace the [Projectcode], 4 part?
3) the num portion the name of my field?
4) Would this be triggered only if it is a new record?

Thanks in advance
Jun 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,770
Dear mmccarthy,

I think this is the answer I have been looking for.
1) Where do i paste this code? would it be on the form_load() event?
2) what do i replace the [Projectcode], 4 part?
3) the num portion the name of my field?
4) Would this be triggered only if it is a new record?

Thanks in advance
  1. That depends on where you want to use it.
  2. This question seems strange - you put your field in. If you ask this question why do you think this code fits your situation :confused:
  3. No answer as it's no question.
  4. No. We are getting right off track here I think.
If this doesn't help, then I suggest that this thread is not the place to ask the question.
Jun 14 '07 #14

hyperpau
Expert 100+
P: 184
  1. That depends on where you want to use it.
  2. This question seems strange - you put your field in. If you ask this question why do you think this code fits your situation :confused:
  3. No answer as it's no question.
  4. No. We are getting right off track here I think.
If this doesn't help, then I suggest that this thread is not the place to ask the question.

I've been trying to find a way to have my own customized Autonumber as the receipt number. But I want it to just generate an new incremented number if it is a newrecord. I've tried the DMax function before but it just doesn't work.

It does not increment the numbers. My number field is formatted as
"HPRec 07"00. Then I put the DMax + 1 function on the forma load event.
But evertyime a go to a new record, it doesn not increment, it just always shows HPRec 0700. and since this is an indexed field, access shows error.

my function is as follows.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. If Me.NewRecord Then
  4.  
  5. Me![ReceiveID] = DMax("[ReceiveID]", "tblReceive") + 1
  6.  
  7. Else
  8.  
  9. Me![ReceiveID] = Me![ReceiveID]
  10.  
  11. End If
  12.  
  13. End Sub
Jun 15 '07 #15

NeoPa
Expert Mod 15k+
P: 31,770
Please read post #14 for my first attempt to say this.

Now let me be even clearer :
This is not your thread.
This is not an extension of the original question.
You have hijacked this thread.
You need to post this question in its own thread.

In case this is still not clear enough, if you continue to try to subvert this thread for your own requirements, I will lock this thread and delete your post.

MODERATOR.
Jun 17 '07 #16

P: 8
new to access but I think this is what I've been looking for to get my numbers to work. Where do I insert the code at?
Jun 20 '07 #17

NeoPa
Expert Mod 15k+
P: 31,770
Sorry,

I've tested it out and can't get the expression to evaluate in the correct order. Try this instead. You can adapt it to include your other criteria
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As DAO.database
  3. Dim rs As DAO.Recordset
  4. Dim num As Long
  5.     Set db = CurrentDb
  6.     Set rs = db.OpenRecordset("SELECT Max(Right([Projectcode], 4)) As MaxNum FROM TableName")
  7.  
  8.     num = rs!MaxNum + 1
  9.  
  10.     rs.Close
  11.     Set rs = Nothing
  12.     Set db = Nothing
  13.  
If you're talking about Mary's code (her post copied for convenience), then that would depend on what you would like to do with it.
I'm afraid that's a questuion for another thread. This thread is finished as the question has been answered (satisfactorily I hope).
Feel free to post a link to this thread from your new one if you feel that would help.
Jun 21 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.