Question Filter a sub string value

[amerifax]

In dbase I could filter a field with this command.

at(substr(sc,1,2),'28,30,40,45,51,66,67')<>0

The fieldor column called "sc" has a value such as 67-251, 40-123,13-144 13-251, 40-117 and many more. I only need to consider the first two characters for my filter, such as 67, 13 or 40

How would I do this in Access 2007?

Bob

 

[jdraw]

see the Mid function or LEFT

Mid(sc,1,2) sc position 1 for length 2

Left(sc,2) left mose 2 positions of sc

 

[boblarson]

In Access you would need to make a field in the query like jdraw said, using one of those and then in the criteria you would use your In statement.

 

[amerifax]

OK I did give this a try. When I put Mid(sc,1,2) it creates "Field1". When I try putting in the criteria such as 40 or 67 or 51 I am not getting any results at all. I even tried it like this:

Mid(sc,1,2)=40

I again get no results. In the criteria how am I supposed to be listing it, am I missing something in my statement?

In the design view I even tried selecting Field1 as one of my fields and tried to set the criteria that way still with no results. I think I am just missing something minor in the statement or the criteria...

Thanks again for your help so far!

Bob

 

[boblarson]

You would create a column in your query like this:

 

[amerifax]

I attached a picture of what my window looks like...when I try to switch back to "Datasheet View" I get a pop-up window that says:

"Enter Parameter Value"
PERMIT!Left("sc",2)
"Blank text box"
OK and Cancel Buttons

What is it looking for? I tried entering In(40,45,66,67), In(40), 40, nothing is working. I feel like I am so close but its just not getting over that hump.

Thanks for all your patience and help!

Bob

 

[boblarson]

You don't put sc in quotes. It would go in brackets and since I didn't know you had another table with the same field name in it, you would need:

Expr1:Left([Permit].[sc],2)

 

[amerifax]

Bob,
I have tried that also and am just not having any luck. I am sure it is something simple I am missing but I just can't get any of these to display any data in my table...this is so frustrating!

Bob

 

[boblarson]

Can you post a copy of the database with any sensitive data either removed or scrambled (or just replaced with bogus data)?

 

[amerifax]

The data, 500 records each, I'm sending is live. Permit is actually 350,000 and Builder is 20,000 records. There are both converted to Access from dBase IV.

Hang on to the data, I might have a couple more questions.

Thanks for the help.

Bob

 

[amerifax]

I could not see how to link to files at the same time.

Thanks again

Bob

 

[boblarson]

I could not see how to link to files at the same time.

Just an FYI for the future - just select both of them together in Windows Explorer and right-click on them and select SEND TO > COMPRESSED FOLDER and then you'll have a single zip file with both of them in it.

 

[boblarson]

First thing up - it isn't converted from the data - it is LINKED to the data. So, without the dbf files, the databases are useless. I have to run so it may be a while before I check in again.

 

[amerifax]

It took me the week end to figure the export but I think I have it.

Bob

 

[boblarson]

Nope, it isn't working because the tables are linked dbf files. You need to include the dbf files not just the Access databases. Or you have to IMPORT the tables into the database instead of linking.

 

[amerifax]

I am not sure what I am doing wrong. This is the procedure I am using and maybe you can tell me where I am going wrong.

I start with a blank Access Database (builder.accdb). When it open is believe it has a table called Table1. I close that table. I click on External Data, More, dBase. When I start the import I have 2 options, one is to bring the source data into a new table in the current database and the other is to load a link to the data source by creating a linked table.

When I am done and save my file I cannot find any leftover links to dbase. Everything I look at tells me the file is an Access File.

I appreciate all your help but if you could tell me where I am going wrong, I would really appreciate it.

Bob

 

[boblarson]

Sorry, it did work. Somehow I thought I had overwritten the originals with the new files but it didn't. So I was opening the wrong ones. My bad.

 

[amerifax]

OK Thanks for letting me know..I really didn't know how else to possibly do it!

Bob

 

[boblarson]

Okay, here you go - you'll have to hold shift down when opening Builder (as I did it in this one) so then you can use Linked Table Manager to relink to Permit. I have two queries. The first is to create the column for linking and includes the criteria. The second is the Builder table linked to the query of Permit.

 

[amerifax]

When we try to open the Permit table we are getting an error that it is looking for L:\temp\permit\permit.accdb

We don't have an L: so that must be yours.

I'm thinking we need to find this "Linked Table Manager" do you know how else I can do this.

I opened Permit first, then I held down the Shift Key when I opened Builder. I can see the queries in the list but cannot access them because of the join problem.

Bob