Hi guys. Is it possible to make a filed contain a random number between certain values, bassed on the contents of another field.

For example. If field contains "Item 1" then field 2 contains a random number between 0.01 and 0.09
and if fiel 1 contains "Item 2" then field 2 contain a random number between 1.01 and 1.09.

What i basicly mean is, if Item 1 is in field 1, auto insert a number to field 2 which is between 0.01 and 0.02


Jesus christ this is hard to explain, if anyone can help, please post. If this is confusing and looks like a retarded monkey has got hold of my keyboard, please tell me and I'll re-edit it

::Edit::
Re edited because that even confused me. I hope this example image helps.


Basicly I want the fields in Data 1 to be automated, depending on the contents of Appliance.

Next to the image is some VERY primitive pseudo code which i hope helps you see what i mean

Um... to what end?

Are you putting together some manner of catagoric price/stock number check?

It sounds like you're too far into the problem for "us" to catch up. But if you can define the objective ... perhapps "we" can work it backwords.

Just a Thought
Phonics Monkey

Sorry. I'm having troulbe explaining this. To me its a complicated problem to solve, and even more complicated to explain.

It is to do with a results sheet for tests carried out on computer equipment. To pass, a PC for example must have a reading of between 0.01 and 0.09.

Where as a monitor must for example be between 1.01 and 1.09

I would like the process to be automated, so that the database reads what the item is, looks up another table to see what the min/max values for that item are, and insert a random number between those values.

okay... in access, press Alt-F11, create a new module (VBA, btw)

then use the access object model code to do what you need, i do this all the time in excel, im sure access must support it

i dont have access installed atm, otherwise i'd give it a shot (use auto-complete to help you)

Okay... So if you're just looking for a random (e.g.non-specific) pass/fail value, why not just use a bool?
0 = failed
1 = passed

...A query could then be run on which items passed or failed.

The thing to remember is Access is a database that just holds the data, unlike excel where the cells can tweak each other. In access the information remains "Static" until a query changes or manipulates it into a desired result.

Aha, got it. But Ive done it in excel.

=IF(C2="PC",(RANDBETWEEN(0.04*100,0.15*100)/100),IF(C2="Monitor",(RANDBETWEEN(0.04*100,0.15*100)/100),IF(C2="Extension Lead",(RANDBETWEEN(0.09*100,0.4*100)/100),0)))

Thats the code i used

I had a whole post written about DB design and all that jazz, but I decided against it as you've solved the problem. However, no, in this case AutoNumber would not have worked for what you're doing as it is the primary id in this case. Autonumber is precisely that, an automatically incrementing number most often used as a primary ID for the table.