pretty straightforward?!?

[Garcian]

hi, im creating a prototype model that rates a particular PC's power consumption and i have a text field (named: txt_utilisation) that has a (utilisation) percentage calculated from other text fields in the form. this is fine.

Now, when i get the utilisation percentage; lets say 14 (%), i want another text field in the form to rate this number and simply display a message that says either - 'Green', 'Amber' or 'Red'.

for example:

1. If the number in txt_utilisation is lower than 30 - then i want another text field to output the word 'Green' (so In this new text field, logically it should be something like this:- IF [txt_utilisation] < '30' THEN = 'Green')

2. Also, If the number in txt_utilisation is between 30 and 60 - it should output 'amber'

3. and then If the number in txt_utilisation is greater than 60 - it should output 'red'.

this seems pretty straightforward in logical terms but i cannot get it to work in my form - i only get the message #Name.

any help would be greatly appreciated.


- - - - - - - - - - - - - - - - - -

also and as a sidenote, i have a combo box where the user can select there chosen PC specifications but there is some kind of glitch or fault on my behalf where it lists 1813 results whereas there is only 3 - bizzarely these 3 are repeated to get the 1813! how do i stop this?

 

[MarkK]

1) Do a search for the IIF() function, also known as 'Immediate If'. Looks like just a syntactical issue, if that's even a word. But IIF() is cryptic and not that readable particularly when nested which you'd probably need to do.
2) Alternatively, do the calculation in VBA using a Select Case statement. This is more efficient, more readable, but requires some VBA coding skills.

 

[jsv2002]

Did something similar to rate a risk assessment I used the following in the forms on current event procedure:

Dim strlow As String
Dim strMed As String
Dim strHigh As String

strlow = "low"
strMed = "Medium"
strHigh = "High"

If [Score] <= 7 Then
Me.RANK = strlow
ElseIf [Score] >= 8 And [Score] <= 15 Then
Me.RANK = strMed
ElseIf [Score] > 15 And [Score] <= 25 Then
Me.RANK = strHigh
Else
MsgBox ("Please ensure the values you have entered are correct")

Me.Severity.Undo
Me.Likelihood.Undo
Me.Score.Undo

End If

You could probably use something similar

Good luck John

PS. Also used the conditional formatting function to change the text box background colour depending on the value assigned.

 

[Garcian]

i apologise, i should have emphasised that i am a complete layman in this area and as such, i do not understand your advice?

is it possible that you could paraphrase.

thanks

 

[jsv2002]

Could you attach an example of your current DB would be easier to put the code in then walk you through the steps of how it is done?

 

[Garcian]

 

[jsv2002]

No I meant zip the database up and post it so I/we could modify the code and post it back sorry if I wasn't clear.

 

[Garcian]

i think i have attached it successfully

 

[jsv2002]

Ok there are a couple of problems with your table/query structure that is why you are showing 1818 records the query is creating every possible variation as a record then when you use the unbound combo boxes to filter it is creating yet another record in the underlying table.

You really need to do some research on database structure as this isn't a quick fix could put the code in for your selection but you really need to address the structure first.

Good luck John

 

[Garcian]

im trying to learn how not to make these mistakes again..... where did i go wrong for example. i feel it is within Qry_GreenApp?

 

[jsv2002]

These are things we all learn as we go along mainly the limitations of what you can/cannot do with a relational database be it access or any other. Yes the problem is with the query but I think it is also with the concept of what you are trying to do.

I assume you don't actually want to "create" new records just use the form as a look up if you like to compare systems yes?

You can do this by creating a seperate table for your "results" create one reccord then on forms data properties set Allow Additions to No.

There are many more experienced people than me on site who can probably explain this better but searching the forums for database structure and downloding some of the sample databases, studying thier structure should help I have posted a few in zip format and so have many others.

If I get the time over the next couple of days will try to modify the one you have posted to do what I think you want but can't make any promises as things get a bit mad sometimes...

Sorry can't be of more immediate help John