How to Automatically Fill In a Field Based Upon Data in an Other Field

[Markgc]

I have an MS database for my radio logs. I have a field named Frequency and a second field named Band. The value that is entered into Band is mathematically related to the frequency value. Here are some of the relationships. There are quite a few more

Frequency >1.8 or <2.0 then put 160M into Band field
Frequency >3.5 or <4.0 then put 80M into Band field
Frequency >7.0 or <7.350 then put 40M into Band field
Frequency >10.1 or <10.150 then put 30M into Band field

Is there a way to have the BAND value automatically filled in when a value is entered into the Frequency field. So if I enter 3.753 in the Frequency field I would like a value of 80M to be added automatically to the Band field

I have updated the database using the update querry so far.


thanks Mark

 

[VRoscioli]

If the band is based solely on the frequency, why not eliminate the Band field and instead create a frequency-band lookup table?


-V

 

[PHV]

Use a lookup table with at least 3 fields:[tt]
FreqMin FreqMax Band
1.8 2.0 160M
3.5 4.0 80M
7.0 7.35 40M
10.1 10.15 30M[/tt]

No Band value for a frequency of, say, 2.5 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Markgc]

I am exporting this database as an xls file and then in Excel I am manipulating the data and exporting it from Excel as a tab deliminated file in ADIF format and I think that I need a BAND entry in this format for use in LOTW (Logs of the World) - aren't you glad that you asked lol. So it seemed easier to create it in MS Access especially as I couldn't get the IF statement to work in Excel

http://www.hosenose.com/adif/adif.html

http://www.storm.ca/~ve3iay/spreadsh.html

Mark

 

[VRoscioli]

Export the results of a query that joins your main table to the lookup table on Main.Frequency >= Lookup.MinFrequency AND Main.Frequency <= Lookup.MaxFrequency


-V

 

[Markgc]

OK I shall have to read up about lookup tables. I am a fumbling amateur with Access.

These are ham radio bands,so there are specific short wave frequencies and bands of operation. Yes there is no operation on 2.5 MHz for amateur stations.

A few more are 14.0 to 14.350 20M - 20 meters

21.00 to 21.450 15M


etc

 

[Markgc]

If I make a lookup table will this cause the Band value to appear in the Band window of the entry form after the frequency value has been added.

Sorry to ask these questions but I am an electrical engineer with an Que Access book in front of me.

Mark