Using Substring command in Access

[amerifax]

In dBase I use a substring to pull multiple cities that I want to query. To pull these I actually use a substring to only look at the first 2characters of a 5 character field. The expression looks like this:
at(substr(sc,1,2),'14,20,28,30,40,45,51,64,66,67)<>0. What this does is it goes to the field sc and finds the first position and selects 2 characters (sc,1,2). from that 1st position.

How would I do something similar in Access without having to select each city seperately? I am using the field SC and I only need to use the first 2 characters of the SC field to pull the data I need.

Any help would be really appreciated.

Bob

 

[dkinley]

Left([FieldName],2)

-dK

 

[gemma-the-husky]

in access vba you have

left(string,numberofchars)
right(string,numberofchars)
mid(string,startat,numberofchars)

and instr(function to search fro a particular sequence)

 

[raskew]

Hi -

Use a combination of the Left() function and the In () operator. Here's a working example that pulls only those items where Left([Item], 2) is in "PA" or "CA".
You should be able to adapt it to your requirements.

SELECT Left([Item],2) AS Expr1, tblDevProcess.Item
FROM tblDevProcess
WHERE (((Left([Item],2)) In ("PA","CA")));

HTH - Bob

 

[amerifax]

I am doing this in a query. I was trying to put this in the Critera. With your example, that doesn't look like something that I would set in the Criteria. Where would I enter that code? I was thinking in the SQL View but then what do I do with what is in there already. It is listing out the fields I have selected for my query (SELECT DISTINCTROW...)

I am really new to all of this (If you couldn't tell).

Bob

 

[amerifax]

I also tried using the LEFT command and that didn't seem to be working so then I tried MID. I was entering this in the criteria and it gave me a "HAVING" error.

Then I entered -

Mid$([SC],1,2)

in the field part and it worked for me, kinda. It changed it to - Expr1: Mid$([SC],1,2) - and displayed only the first 2 characters of the field SC. What I want it to do is show be the whole field SC but set a criteria for the first 2 characters of sc equal to 66 or 67. So that I see only records with the first 2 characters of sc equal to 66 or 67.

Is that maybe a better explanation?

Heather (for Bob)

 

[amerifax]

Thanks so far for all of your help.

What I did was created a new query. Then in the criteria for the field SC I entered - Mid([sc],1,2)="60".

This returned only records that had 60 as the first 2 characters.

When we run criteria like this, we usually have 7-8 values (like in my original example). How would I make this work with multiple criteria such as 40,45,66,67?

Thank you again!

Bob (and Heather)

 

[dkinley]

Hmmm. Don't know why the Left function doesn't work for you.


I am not sure if you are doing this in SQL or the Access QBE. I am not so hot in SQL but in the QBE you can set a column to the following ...

SomeName: Mid([sc],1,2)

Even though "SomeName: Left([sc],w)" should work. =]

And then in the criteria you can Or them ...

40 Or 50 Or 60.

You can then click on SQL View to give you the SQL version of this.

Hope that helps.

-dK

 

[amerifax]

dK,

Thanks that did work. We are working in the QBE.

I just have one other thing. Is there a way to do this without losing the original value of SC. I know it is still there and its just a query, but in some cases we might still want to view the actual SC not just the 2 characters of it. Is that possible?

Thanks again.

Bob (and Heather)

 

[dkinley]

Oh, you don't lose the original value ...

By declaring "SomeName: ... " you are setting a variable (type variant) in the query. Just a use, present and dispose sort of thing.

Now, if you want to use the actual SC value, just drag it down to another column in the QBE. Just because it is in the query doesn't mean you have to use it or not, but it will be present in that query should you need to use it in the data source for a form, report, or another query.

The "SomeName: ..." bit is just something to use to filter your query. It is just a means to an end. You don't have to bind it to a control on a form, report, or another query. Use the actual SC value.

hehe .. After rereading my own words, I have to ask, does that make sense?

-dK

 

[raskew]

Hi again -

I, too, don't understand why your Left() function is not working.

This modified version of the original example displays just the selected (complete) Items, sorted by date.

SELECT tblDevProcess.Item
FROM tblDevProcess
WHERE (((Left([Item],2)) In ("PA","CA","DE")))
ORDER BY tblDevProcess.startTime;

In QBE, there are 3 fields:
-Item
-StartTime
-Left([Item],2)

Only the Item Show box is checked so only field Item is visible.

In the criteria cell of Left([Item],2): In ("PA","CA","DE")

Note that if what you're displaying is an actual number field, you'd omit the "".

Field StartTime is set to: Sort By: Ascending.

If you're still having problems, please post a small sample and I'll see if I can fix it.

HTH - Bob

 

[raskew]

Here's another working example based on Northwind's Employees table.

SELECT Employees.*
FROM Employees
WHERE (((Left([LastName],2)) In ("BU","CA","DA")))
ORDER BY [LastName];

You can test it by copying/pasting to a new query in a DB containing the Employees table.

Bob

 

[amerifax]

>>raskew<<

Thanks for 200%.

You solved my problem exactly EXACTYLY how I needed it. This code will be used in most every thing I do.

Where does the other 90% come in? Since I have been dabiling with Access fopr 6 months with no luck I have been looking for a simularity to Visual dBase command line. Do one could understand what I was looking for. You gave it to be, SQL View. I did'nt even know how to open it. Know I see how I can use a combination of Query and SQL view. I will now beable to charge forward with Access.

Thanks again.

FWIW - For whats it's worth
Access
WHERE (((Left([sc],2)) In("14","20","28","30","40","45","51","59","64","66","67")))

dBase command
at(substr(sc,1,2),'14,20,28,30,40,45,51,59,64,66,67')<>0

By giving the dBase code does that help anybody come up with a solution as we have here?

Bob

 

[raskew]

Glad it worked for you.

Where does the other 90% come in?

Not sure what else you're looking for. Please expand.

By giving the dBase code does that help anybody come up with a solution as we have here?

Your original explanation, along with the code sample, was more than enough.

Best Wishes,

Bob

 

[amerifax]

I had mentioned that you helped me 200%. Most always when someone is happy they say thanks for giving me 110%.

The 90% is showing me how I get my dBase feel, (dBase="Command Line" Access = SQL View" that I'm used to. I was lost with only pictures, Design View, to work with. I left many messages, on many different forums, trying to get the answers to this problem with no responses.

So I'm very grateful to you for showing me the total basis that Access uses in regard to code. I'm sure there's more to it but at least I'm off and running or should I say Jogging.

Bob

Thanks again Bob

 

[amerifax]

This might seem like an old topic which it is. But I've come a long way and can now finally worked with this code and actually make it work for me. So here is a very delayed Thank you for your help.

Bob