Web Development

Ask something
Discussion Locked

Autochange month

0 helpful
4 replies

by lwwilliams

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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).

4 replies

by streamlogic

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

by ewingdweller

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;

by ewingdweller

Oops.

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

This discussion is closed to new replies.