Date conversion to character


New Member
Nov 23, 2005
I am trying to set a default value in a 4 char column using the following code but am receiving a run time error.


The intent is to create a federal fiscal year based on the month the row is created.

If the month >= 10 then get the year from the current date and add one otherwise get the year from the current date.

The value will be place in a char column.

I've tried using the Str and Str$ functions. Not sure of the difference between them if there is any.


Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
If you're trying to put this in a table, that's your problem. Tables are not designed for performing calculations. You'd be better off using a query and creating a calculated field there.
If that still throws the error, you most likely have a missing reference to one of the function libraries. Has this database been updated from 97 to a higher version?
To check, open any module in Design view and select Tools | References. Go down the list to see if any are flagged as missing. If so, clear the selection and scroll down the list until you see the same library name (it will most likely have a higher verison number). Check this, click OK and check it out.

Upvote 0
Denis, thanks for the reply.

I've done some reading --that always helps-- I think I can accomplish the default table value by using a macro tied to the column, or that failing a macro tied to the form column that populates this table column.

Will the macro also work or is the only way, as you suggested, through a Query?

Thanks again
Upvote 0
It depends on whether you want the value to recalculate when the source data changes. Queries update dynamically, whereas the macro (or code) option places a static, unchanging value in the table.

Upvote 0

Any calculated fields in the actual table will cause you problems later on down the line and that's promise.

As Denis suggests using a static value needs trapping if you change any data later on.
As a preference I would also avoid Macros in the Access sense and write a custom function.
Do your calulations in a Query, this way they:

a) are always current
b) (most importantly) buffer the data you are working with helping hugely with record locking.

Just note to add about possible missing references.
It is a good idea to use Now() rather than Date().
Date() or just Date is a real swine when there are missing references and Now() works throughout Access versions.

Upvote 0
I've just tried this as the default value for a text field in a table. It worked, returning 2006 for today.

Upvote 0
Thanks all for your replies.

The original question dealt with having a default value populate a text or char column. The federal fiscal year should not change once calculated. This should be a static value once placed in the table which is why I was trying to use the default value rather than use a calculation in a querry. The idea of the macro was another way I though a default value could be established.

Many other systems deal with dates and time as a numeric value; according to the last post, there is no need to convert the numeric value to a text or char in order to store in a text or char column, is that correct?

Thanks to all of you for your time.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back