Converting binary string to different whole number

[OMS]

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

 

[John W. Vinson]

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

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]

 

[Wayne-I-M]

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


If they are sometimes different can you give details

 

[OMS]

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,

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

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]

 

[OMS]

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


If they are sometimes different can you give details

--
Wayne
Manchester, England.

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

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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,

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

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]

 

[Pat Hartman]

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

 

[Krzysztof Pozorek [MVP]]

(...)

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.

You can also use InStr function:

InStr([FIELD],"1")

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

 

[Wayne-I-M]

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


--
Wayne
Manchester, England.

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


If they are sometimes different can you give details

--
Wayne
Manchester, England.

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

 

[OMS]

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

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

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

On Mon, 21 Jan 2008 10:51:17 -0600, "OMS"
<[email protected]>
wrote:

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



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]

 

[John W. Vinson]

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)));

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]

 

[John W. Vinson]

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]

 

[OMS]

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
increase in data without haveing to recreate the formulas


--
Wayne
Manchester, England.

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


If they are sometimes different can you give details

--
Wayne
Manchester, England.



:

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

 

[Pat Hartman]

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.

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
increase in data without haveing to recreate the formulas


--
Wayne
Manchester, England.

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


If they are sometimes different can you give details

--
Wayne
Manchester, England.



:

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