How to extract a number from a field in Access

Hello,

I need to extract a number from a Binary field using MS Access. The number can be anywhere in the field but will always begin with 40001- and have an aditional 3 character number and 2 character number sepparated by a -. "40001-xxx-xx".

Can anyone affor any assistance?

Thank you,
Paul

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

unknown

I've been unable to do it in Access so far, but this query will work if you paste it into the Query Tool in VISUAL. I'm assuming you're using the PART_BINARY Table.

SELECT PART_ID, CAST(CAST(BITS as BINARY) as VARCHAR) as 'TEXT' FROM PART_BINARY
WHERE CAST(CAST(BITS as BINARY) as VARCHAR) like '%40001-___-__%'

unknown

Thanks Jeremy. Actually, I'm pinging the Service Table binary for this number. When I tried your statement in VQ the result was the number and some text after it. Not just the number. Also, I need to export the result to Excel and there's no way to do that in VQ. I can querry to get a list in Access but I need to extract just that number out into another column or copy it out into Excel.

Thanks,
Paul

tross

This is an access query for the parts table. Tweak it for the service table, but it should do the trick.

good luck - Dave

SELECT dbo_PART_BINARY.PART_ID, Mid(StrConv([BITS],64),Instr(StrConv([BITS],64),'40001'),12) as myString
FROM dbo_PART_BINARY
WHERE (((StrConv([BITS],64)) Like '*40001*'));

unknown

Thanks Dave. I think I need better luck, it's not working. I changed PART to SERVICE and keep getting invalid syntax errors.

"The syntax of the subquery in this expression is incorrect."

Paul

tross

Interesting, it works for me. Check your spacing, spelling and/or make sure all the brackets are where they are supposed to be. Maybe the copy / paste missed something.
Also, I wrote it quick in Access 2007 accessing a Visual SQL db. What version of access are you using?

unknown

I do the following in SQL not sure if itll work in ACCESS.

CASE (UNICODE(CAST(CAST( dbo.OPERATION_BINARY.BITS AS VARBINARY(4000)) AS NVARCHAR(4000))) - ASCII(LEFT(CAST( dbo.OPERATION_BINARY.BITS AS VARBINARY(4000)),
1))) WHEN 0 THEN CAST(CAST( dbo.OPERATION_BINARY.BITS AS VARBINARY(8000)) AS NVARCHAR(4000)) ELSE CAST(CAST( dbo.OPERATION_BINARY.BITS AS VARBINARY(8000)) AS VARCHAR(8000)) END AS "Specs"

unknown

We're using Sql Server and I am using Access 2010 but I haven't had any other issues with other query's since the upgrade.

And, using SQL is not in my list of accomplishments.

unknown

Perhaps you didn't make all the changes needed to pull from the service_binary table. I am also using Access 2010 and Dave's query worked fine for me. Try pasting this:

SELECT SERVICE_ID, Mid(StrConv([BITS],64),Instr(StrConv([BITS],64),'40001'),12) as myString
FROM dbo_SERVICE_BINARY
WHERE (((StrConv([BITS],64)) Like '*40001*'));

tross

You cannot use cast or convert in MS Access. If you can't get that to work, but have the rights to create a macro in Visual, paste the following code then edit anything in CAPITAL letters that starts with ENTER SOMETHING to meet your environment. This should query from Visual's vb script and dump the results to excel.

Dim rs
Dim sql
Dim conn

conn = "Provider=sqloledb; Data Source=ENTER SERVER NAME; Initial Catalog=" & DATABASE & "; User Id=ENTER USER NAME; Password=ENTER PASSWORD;"
set rs = CreateObject("ADODB.Recordset")

sql = "Select Service_ID,SUBSTRING(Convert(VarChar(8000),Convert(Binary(8000),BITS)),PATINDEX('%40001%', Convert(VarChar(8000),Convert(Binary(8000),BITS)) ),12) as BITS FROM Service_BINARY WHERE Convert(VarChar(8000),Convert(Binary(8000),BITS)) like '%40001%'"

rs.open sql, conn

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
Dim x
x=1
Do until rs.eof
objExcel.cells(x,1).value = rs.fields("Service_ID").value
objExcel.cells(x,2).value = rs.fields("BITS").value
x=x+1
rs.movenext
loop

unknown

Pasted axactly as you have it. Same message.

"The syntax of the subquery in this expression is incorrect."
"Check the subquery's sintax and enclose the Subquery in parentheses."

Then when I click 'Ok" the word SELECT is highlighted.

I try to modify it but no luck there either.

If I pull the text out and paste into excell, would that be easier?

unknown

Dave, thanks for the macro. Now if I just new what to do with it. I guess I'm in the wrong room and should go down the hall to "Queries for Dummies". Don't suppose you could point me in the right direction for pasting this thing? As usual, if I can't figure it out, it's way over my head. Macro's are not my thing but I can copy and paste most anything if I know where to paste it.

Paul

unknown

Sounds like doing the report in Synoptix would be a lot easier and faster than Access, for those users who are not adept at macros, especially in Access which is not as user friendly as other apps.

Copyright © 2025 Infor. All rights reserved.