Return value from a table based on a text field

[brum_rich]

Return value from a table based on a text field (more help required)

I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.

I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).

I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?

Any help would be appreciated.

 

[mhartman]

Hello
Please see enclosed attachment for how to do this.
Regards
Mark

 

[brum_rich]

Matt

Thanks for the reply. However, that does not quite return the value I wanted. I need to display the field ID - for example in your Test56.mdb you have a field ID number 1-6. This is teh value that I would liek to return to the text box.

So for my example...

txtPreviousMonth would display Jul-06 (based on the Month function you gave me)

In the table I have records as follows:

Month/Year xx Month No xx Year No xx Month ID
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30

I need to return the MonthID value to the new text field, so in case of Jul-06 it would show 30