Need help with expression or VBA in a form for calculated query.

[MadTom]

I'm almost done with one form and need help with one more thing. I have one field that is a number uf and I want to convert it to a code in a query. The number would be between 1 and .000001 and would show a text box or label to show a code for that number.

The 3 digit code 104 written over ceramic capacitor indicates its value. The first two digits (10) of this code are first two digits of capacitor value and third digit (4) gives the number of zeroes to be added so as to get capacitor value in picofarads which is 10,0000 pF or 0.1 uF. I have two calculated fields one for Pf and the other for Nf.
Any help would be great!

[June7]

I am not an electronics engineer and not sure what you are talking about. Is that a typo for 10,0000 - too many zeros? You need to convert a number to the code '104'? The number is between 1 and .000001 uF?

[pbaldy]

I feel like it will get more complicated, but if it's always 3 digits you can use the Left() and Right() functions. It seems clunky but:

?Left("104", 2) & replace(space(Right("104",1)), " ", 0)
100000

or maybe

?Left("104", 2) ^ (Right("104",1) + 1)
100000

[MadTom]

I am not an electronics engineer and not sure what you are talking about. Is that a typo for 10,0000 - too many zeros? You need to convert a number to the code '104'? The number is between 1 and .000001 uF? What would be the code to represent 1?

1uf would be 105 in code. My numbers maybe off? https://www.electronics2000.co.uk/ca...calculator.php

[MadTom]

I feel like it will get more complicated, but if it's always 3 digits you can use the Left() and Right() functions. It seems clunky but:

?Left("104", 2) & replace(space(Right("104",1)), " ", 0)
100000

or maybe

?Left("104", 2) ^ (Right("104",1) + 1)
100000

Paul, I want to take the number 1.00 and convert it to the code 105, 1 would be 105. 105 = 10x10 to the 5th or 1,000,000 pf ,1,000 Nf, 1.0 Uf
Thanks

[June7]

That calculator says 100,000 pF = code 104 so you appear to have a misplaced comma in your example.

So, convert uF to pF then convert to code.

1 uF * 1000000 = 1000000 pF = code 105

.1 uF * 1000000 = 100000 pF = code 104

If you already have pF then use it in string manipulation.

Left([pF],2) & Len(Mid([pF],3))

[MadTom]

I added this =Left([Cap_Value],2) & Len(Mid([Cap_Value],3)) and I get code 10 for 1 entered in Cap_Value.

[June7]

Is that 1 pF or 1 uF?

If uF then must multiply by 1000000 to get pF then convert to the code. As shown in my previous post.

[MadTom]

Ok I added this =Left([Cap_Value]*1000000,2) & Len(Mid([Cap_Value]*1000000,3)) and all seems to work. Will check it more. Cap_Value is Uf.
Thanks for your help!

[MadTom]

Works great, only one thing I would change <= .000001 or >= 100000 would not show or would show "No Code" . Capacitor code only goes from 100 to 109. Any way to add to this

expression?

=Left([Cap_Value]*1000000,2) & Len(Mid([Cap_Value]*1000000,3))
Thanks!

[June7]

So if outside the range, want to return "No Code"? That web calculator will calc codes 100 to 999.

pF from 10 to 99999999999

uF from 0.00001 to 99999

So maybe

=IIf([Cap_Value]<0.00001 Or [Cap_Value]>99999, "No Code", Left([Cap_Value]*1000000,2) & Len(Mid([Cap_Value]*1000000,3)))

[MadTom]

=IIf([Cap_Value]<0.00001 Or [Cap_Value]>99999,"No Code",Left([Cap_Value]*1000000,2) & Len(Mid([Cap_Value]*1000000,3))) is what I'm using and it works great! I'm learning as I go and always breaking down code that others supply. Only so much you can google. I'm still working on more forms, but have learned a lot. This forum is the best!!!!
Thank you June7 for all your help!