Worse than a complete novice

[Paul Martin]

Yes, i am worse than a complete novice, i haven't a clue about VBA within
Access other than i am told that i will need it to achieve what i need to
do.

What i have now is an Update Query that present a message box when it run
asking me to enter a number. That number is an even number in the range 2 to
12 representing a month. The correct number to enter is the month [eg 9 now]
as i run the query plus 3. I have to cope with year ends eg 11 + 3 = 2 [for
Febraury]

I need to automate the selection of the correct number. The problem seems to
be that all the variation of Date in Access are not suprisingly in date
format whilst the value required is in numerical format.

The only suggestion that i have received from colleagues at work is to
create a look up table along the lines of: -

If date NOW is between 1st September and 30th September the answer is 12
" " " " 1st November and 30th November the
answer if 2

and so on until all the options are covered. The specified dates will always
be the odd months so if the date now was an even month an error could be
returned.

I have never written VB or VBA beyond a small exercise that change the
colour of the text in a box so i would greatly appreciate any help from the
experts out there. I would love to have the time to learn to code in VBA but
the object of the excercise for me is to get a membership database working
properley.

Thanks in anticipation

Paul

 

[Michel Walsh]

Hi,

That should be a simple expression like


Choose( Month( date_and_time_field ) , resultForJanuary,
resultForFebruary, ...., resultForDecember)


where resultForJanuary is the result you want to see if the month of the
given date_and_time_field is January. As example, resultForSeptember is the
value 12 and resultForNovember is 2. I could not tell if, say, for October,
if is it 12, or 2, or 1, but I assume you can supply those 12 values.



Hoping it may help,
Vanderghast, Access MVP