Query for Left(String,5) - Access 2007

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

Thanks John
I didn't realize you could edit the sql in the view?
I pasted your sql into a copy my query but I'm getting syntax errors on the
where clause. I think it's a paren issue but I haven't been able to figure it
out yet.

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

oohn,
I spoke too soon. I found the syntax issue. One too many parens at the
beginning. Took that out and worked like a charm. GOt the results I needed.

Thanks So much!

Thanks to Klatuu as well.

You were both very helpful

"JohnH" wrote:

Thanks John
I didn't realize you could edit the sql in the view?
I pasted your sql into a copy my query but I'm getting syntax errors on the
where clause. I think it's a paren issue but I haven't been able to figure it
out yet.

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

I would look for a null value in one (or more) of the Boston.Boston
values. If that is the case, the query is going to try to link every
record in the NAME table to that record (or records) in the Boston table.

So, are there any records in Boston table where the Boston field is
blank (null)? If so, we need to work around that.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

MY CPU is running @ 100% as soon as I try to do anything inside of or with
the result list. I've let it run for 10 minutes or more to see if it will
come back but so far it has not. Not sure why when I run the query it
prompts for a zip value. I can leave it blank and it runs. It prompts for a
zip value when I attempt to export to Excel as well and thats when it locks
up and goes to 100% CPU usage.

Interesting?

"JohnH" wrote:

Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

No Null values. There are 64 records in Boston and all have valid zipcodes.
Boston is the only field in the table. It is a Linked Excel file if that
means anything.

Thanks
John


"John Spencer" wrote:

I would look for a null value in one (or more) of the Boston.Boston
values. If that is the case, the query is going to try to link every
record in the NAME table to that record (or records) in the Boston table.

So, are there any records in Boston table where the Boston field is
blank (null)? If so, we need to work around that.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

I did try createding an access table from the linked exxel file and using it
instead of the linked excel file only because I can't think of anything
else, but same issue. As soon as I try to go to the bottom of the result list
and/or export the results to excel I lock up with 100%cpu usage until I end
task. Just for kicks I tried my usual metod of putting the like "*12345*" in
the name.zip criteia and that works fine except that I have to manually put
the zips in and can only put in so many before the query is too big and I
have to run it multiple times to get all the zips in...yuk.

Thanks
John


"JohnH" wrote:

No Null values. There are 64 records in Boston and all have valid zipcodes.
Boston is the only field in the table. It is a Linked Excel file if that
means anything.

Thanks
John


"John Spencer" wrote:

I would look for a null value in one (or more) of the Boston.Boston
values. If that is the case, the query is going to try to link every
record in the NAME table to that record (or records) in the Boston table.

So, are there any records in Boston table where the Boston field is
blank (null)? If so, we need to work around that.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Well John it sort of worked.
The query runs and I see my data but if I try to do something like go to the
end of the list or export it to Excel Access locks up and I have to
Taskmanager end Access to get out.

Not sure what that's about?

Thanks
John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote:

Hi John, wanted you and Klatuu know I solved my problem. I can do the query
using design view if I simply don't do a join between the zip table and the
name table. I just put the cireteria in the where clause that you and Klatuu
said to use and tha did the trick.I'll bet you two just assumed I was doing
that already. Sorry it took so long for the light to dawn.

Thanks again for your help.

John

"John Spencer" wrote:

Well, you certainly can edit the SQL view. I do it all the time and
often I start there.

The Query design view actually creates SQL. It is just a user interface
that simplifies the process of writing the SQL.

If you open the sql view you should be able to paste the code I posted
into it. You might want to make a backup copy of your current query first.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


JohnH wrote:
Thanks John,
Where do I enter sql code to create a query? I've never used anything other
then the design view for creating queries. I've never fully understood the
point of the sql view since you can't edit it? I suspect I can edit sql
somewhere?

Thanks
The-other-John

"John Spencer" wrote: