Converting binary string to different whole number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OMS - 21 Jan 2008 16:51 GMT

Hi,

I'm sure this is simple but am foggy today. I want to convert a string in a
query as follows:

10001 to 1
01000 to 2
00100 to 3
00010 to 4

What function should I use? I thought of Switch([FIELD]="10001","1"). Any
help would be greatly appreciated.

Thanks in advance.
OMS

Reply to this Message

John W. Vinson - 21 Jan 2008 17:46 GMT

>Hi,
>
[quoted text clipped - 11 lines]
>Thanks in advance.
>OMS

If it's only these four (rather odd) mappings, then Switch() would work;
remove the quotes around "1" if you want the result to be numeric, and of
course include all four pairings and allow for nonmatches:

Switch("10001", "1", "01000","2", "00100", "3", "00010", "4", True, Null)

If it's any more than that (you do have 32 possible strings!) then I'd create
a little translation table and just join it.

John W. Vinson [MVP]

Reply to this Message

OMS - 21 Jan 2008 18:58 GMT

Hi John,

I tried this but it returned no results. It looks like it should work. I am
trying to do this within a query expression. The original field is a text
field, if that helps. I realize now that perhaps I should have posted in the
access.queries newsgroup. I could make a conversion table but wanted to do
this within the query if possible. What am I missing?

SQL view:

SELECT [tbl_Quarterly].FIELD AS FIELD1, [tblQuarterly].EMP_NUM,
[tblQuarterly].FIELD
FROM [~tblQuarterly]
WHERE
((([tblQuarterly].SITE)=Switch("10001","1","01000","2","00100","3","00010","4",True,Null)));

Thanks.
OMS

>>Hi,
>>
[quoted text clipped - 24 lines]
>
> John W. Vinson [MVP]

Reply to this Message

Douglas J. Steele - 21 Jan 2008 19:55 GMT

Your syntax is wrong for the Switch function. It's supposed to be

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

where exrp-n is a boolean expression to be evaluated.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi John,
>
[quoted text clipped - 43 lines]
>>
>> John W. Vinson [MVP]

Reply to this Message

OMS - 21 Jan 2008 21:26 GMT

I saw that page too and tried as written with no luck (invalid syntax
message). Then tried inumerable combos. Perhaps it's because I'm trying to
do this within a query instead of a module. Even tried
Iif([FIELD]="10001,"1") and also tried Switch([FIELD]="10001","1") and those
didn't work (with and without ".

> Your syntax is wrong for the Switch function. It's supposed to be
>
[quoted text clipped - 56 lines]
>>>
>>> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 21 Jan 2008 22:36 GMT

>Hi John,
>
[quoted text clipped - 11 lines]
> WHERE
>((([tblQuarterly].SITE)=Switch("10001","1","01000","2","00100","3","00010","4",True,Null)));

Sorry for misleading you. The first argument of each pair needs to be a
logical, true or false expression.

I have no idea what the tilde in front of tblQuarterly is (other than a
typo?), for one thing. For another, what field contains the 10001? The code as
written will (once the Switch gets corrected) take an input of 10001 (from
some field or parameter not yet specified) and use it to construct a criterion
of "1" to search the Site field.

What are the relevant fields in tblQuarterly?
What user input is desired for this query?
Where will that user input come from: a prompt, a form control, some other
field in this table, some other table?

John W. Vinson [MVP]

Reply to this Message

Wayne-I-M - 21 Jan 2008 17:53 GMT

Are the strings always the same - if so you could use a simple nested IIf

If they are sometimes different can you give details

Signature

Wayne
Manchester, England.

> Hi,
>
[quoted text clipped - 11 lines]
> Thanks in advance.
> OMS

Reply to this Message

OMS - 21 Jan 2008 19:00 GMT

These strings are always the same. Just to note, I want to do this withing a
query. Here's the SQL:

SELECT [tbl_Quarterly].FIELD AS FIELD1, [tblQuarterly].EMP_NUM,
[tblQuarterly].FIELD
FROM [tblQuarterly]
WHERE
((([tblQuarterly].SITE)=Switch("10001","1","01000","2","00100","3","00010","4",True,Null)));

> Are the strings always the same - if so you could use a simple nested IIf
>
[quoted text clipped - 16 lines]
>> Thanks in advance.
>> OMS

Reply to this Message

Wayne-I-M - 21 Jan 2008 20:53 GMT

There are lots of ways to do what you need - if it were i would folow
Douglas' advice and create the table as this will allow you build in an
increase in data without haveing to recreate the formulas

Signature

Wayne
Manchester, England.

> These strings are always the same. Just to note, I want to do this withing a
> query. Here's the SQL:
[quoted text clipped - 25 lines]
> >> Thanks in advance.
> >> OMS

Reply to this Message

OMS - 24 Jan 2008 15:41 GMT

Thank you. I was trying to avoid another table but have taken Douglas'
advice. Sorry for the delayed repsonce.

OMS

> There are lots of ways to do what you need - if it were i would folow
> Douglas' advice and create the table as this will allow you build in an
[quoted text clipped - 33 lines]
>> >> Thanks in advance.
>> >> OMS

Reply to this Message

Pat Hartman - 25 Jan 2008 20:20 GMT

If you didn't want to create a translation table (and you shouldn't), then
you can use the code I posted to do the conversion for you. Convert it to a
public function if you want to use it in a query.

> Thank you. I was trying to avoid another table but have taken Douglas'
> advice. Sorry for the delayed repsonce.
[quoted text clipped - 38 lines]
>>> >> Thanks in advance.
>>> >> OMS

Reply to this Message

Pat Hartman - 21 Jan 2008 20:06 GMT

Here's some code I found at http://www.developerfusion.co.uk/show/3282/ with
a web search:

Function Bin2Dec(Num As String) As Long
Dim n As Integer
n = Len(Num) - 1 a = n
Do While n > -1
x = Mid(Num, ((a + 1) - n), 1)
Bin2Dec = IIf((x = "1"), Bin2Dec + (2 ^ (n)), Bin2Dec)
n = n - 1
Loop
End Function

Here're two others that explain the process but do not provide code:

http://www.permadi.com/tutorial/numBinToDec/index.html

http://www.wikihow.com/Convert-from-Binary-to-Decimal

> Hi,
>
[quoted text clipped - 11 lines]
> Thanks in advance.
> OMS

Reply to this Message

Krzysztof Pozorek [MVP] - 21 Jan 2008 20:26 GMT

(...)

> 10001 to 1
> 01000 to 2
[quoted text clipped - 3 lines]
> What function should I use? I thought of Switch([FIELD]="10001","1"). Any
> help would be greatly appreciated.

You can also use InStr function:

InStr([FIELD],"1")

K.P. MVP, Poland
www.access.vis.pl

Reply to this Message

John W. Vinson - 21 Jan 2008 22:36 GMT

>You can also use InStr function:
>
>InStr([FIELD],"1")

<snork!!!!>

Yep. With the provided data that's by far the simplest.

John W. Vinson [MVP]

Reply to this Message