General discussion

Locked

Autochange month

By lwwilliams ·
How can I tell access to change the month automatically on the first day of the month. (Example: Change the 8 in this text =>(R06081213)to 9. Eight being the month of August and 9 the month of Sept. The last four numbers would need to be changed also. The number would then look like this =>(R06090001).

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by StreamLogic In reply to Autochange month

LWwiliiams,

In MS Access, you can use very similar functions to VBScript. These are much different that what you will find on SQL Server.

If the numbers will always be in the same format, you can try:

SELECT Replace("R06081213",Mid("R06081213",4,2),"09") AS Expr1;

Replace() finds data in a string and replaces it/
Mid() selects data in the middle of a string from a starting point, and length.

For changing the year, you can use LEFT() and RIGHT() rather than MID()to search from the left or right of the string.

Hope this helps,
Curtis

Curtis Hughes
StreamLogic Inc.
http://www.StreamLogicInc.com

Collapse -

by ewingdweller In reply to Autochange month

Continuing the previous answer, you may need to use a function similar to the following:

SELECT Replace("R06081213",Mid("R06081213",4,2),"090001") AS Expr1;

Based on your question, you probably need a more robust function using FOR/NEXT looping. Seems like you could just do something similar to the following psuedo code:

rst.Reports = SetRecordsetFilter()
[note: see Access Help for instructions
on that!]

For i = 1 to rst.RecordCount
origName=[ReportNm];
[ReportNm] = Left(origName,4) & (9000+i);
next;

Collapse -

by ewingdweller In reply to

Oops.

The first function would be
SELECT Replace("R06081213",Mid("R06081213",4,6),"090001") AS Expr1;

Collapse -

by lwwilliams In reply to Autochange month

Thank you both for your answers. I'll giv it a try.

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums