AUTOMATIC DATE/VALUE QUESTION...

[Guest]

I have my form set to insert the current date for each new record. I also
have a combo box from which users must manually select an assigned letter
that corresponds to each month:

JAN = A
FEB = B
MAR = C

...AND SO ON.

My users complain about having to manually select the LETTER from the combo
box...they want it automated when the date is inserted...Can someone help me
figure out how to make the field insert the letter value that corresponds
with the month in my date field???

 

[Douglas J. Steele]

Seems a reasonable request from your users!

Do you really need to store the letter? Since it's derivable from the date,
I don't think it should be in the table. Instead, create a query, and add a
computed field to that query to return the letter. The following calculation
should do that:

Chr(Month([DateField]) + 64)

The Chr function returns the character associated with a specific ASCII code
value. The ASCII value for A is 65, for B is 66, for C is 67 and so on, so
adding the month number to 64 will return the appropriate ASCII code.

 

[Rodger]

Do you need this information stored in the table if it is always going to be
A for January and you already know the date? So if the date is 1/29/2007
will that field always be A for January or could it be something else? The
reason I ask is because we could do something where the users never have to
do this again.

If you need this on a report we can do this . . . . . or do you want to
view this on a form. (If you want it on a form I need to think about that)

Create a table Called MONTH. Put all your months in the table. or at the
very least have two fields one with the number of the month and then the
value you need.

MON_ID Autonumber
MON_NUMBER 1
MON_VALUE A
MON_NAME January

Now make a query we want to grab the month number of the date . . . .

In the query is the function Month, see example below. This will pull out
the number associated with the month. When building the month table
remember to refer to January as 1 and not 01, just makes it easier.

Month([PHY_TERM_DATE]) AS myMonth

Remember to include the ID field from the table as well.

Now save the query you really only need those two fields.

Now create another query and include you original table, the query we just
created, and the MONTHS table. Now link the ID fields from the original
table to the query we created. Then link the myMonths field to the
MON_NUMBER (or what ever you call it) and then include the MON_VALUE and
there you go you can now use that to get value and do not have to have the
end user putting that number in.

HTH,
Rodger

 

[Rodger]

OK Doug's solution is MUCH easier!!!! Use his!!!!

Do you need this information stored in the table if it is always going to be
A for January and you already know the date? So if the date is 1/29/2007
will that field always be A for January or could it be something else? The
reason I ask is because we could do something where the users never have to
do this again.

If you need this on a report we can do this . . . . . or do you want to
view this on a form. (If you want it on a form I need to think about that)

Create a table Called MONTH. Put all your months in the table. or at the
very least have two fields one with the number of the month and then the
value you need.

MON_ID Autonumber
MON_NUMBER 1
MON_VALUE A
MON_NAME January

Now make a query we want to grab the month number of the date . . . .

In the query is the function Month, see example below. This will pull out
the number associated with the month. When building the month table
remember to refer to January as 1 and not 01, just makes it easier.

Month([PHY_TERM_DATE]) AS myMonth

Remember to include the ID field from the table as well.

Now save the query you really only need those two fields.

Now create another query and include you original table, the query we just
created, and the MONTHS table. Now link the ID fields from the original
table to the query we created. Then link the myMonths field to the
MON_NUMBER (or what ever you call it) and then include the MON_VALUE and
there you go you can now use that to get value and do not have to have the
end user putting that number in.

HTH,
Rodger

I have my form set to insert the current date for each new record. I also
have a combo box from which users must manually select an assigned letter
that corresponds to each month:

JAN = A
FEB = B
MAR = C

..AND SO ON.

My users complain about having to manually select the LETTER from the combo
box...they want it automated when the date is inserted...Can someone

help

me
figure out how to make the field insert the letter value that corresponds
with the month in my date field???