Date conversion to character

[rockco]

I am trying to set a default value in a 4 char column using the following code but am receiving a run time error.

IIf(Month(Date())>=10,Str$((Year(Date())+1)),Str$(Year(Date())))

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.

Thanks

 

[SydneyGeek]

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.

Denis

 

[rockco]

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

 

[SydneyGeek]

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.

Denis

 

[Ian Mac]

ADVERTISEMENT

Hello,

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.

Regards,

 

[Norie]

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

IIf(Month(Date())>=10,Year(Date())+1,Year(Date()))

 

[rockco]

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.