Query Question

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

You have a reference problem. It should have returned A.
Open your VBA Editor. Click on Tools-References, and look for a reference
to Visual Basic for Applications. It needs to be there. Also look for any
references that show "Missing", they need to be fixed.

"Lucien" wrote:

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

The reference to Visual Basic for Applications is there and it is checked.
I scrolled down the entire list of references and none show "missing".



"Klatuu" wrote:

You have a reference problem. It should have returned A.
Open your VBA Editor. Click on Tools-References, and look for a reference
to Visual Basic for Applications. It needs to be there. Also look for any
references that show "Missing", they need to be fixed.

"Lucien" wrote:

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

Take a look at this site. It may be a reference problem or it may be a
database corruption problem.

These sites have info on references:
http://allenbrowne.com/ser-38.html
http://www.accessmvp.com/djsteele/Ac...nceErrors.html

A decompile can sometimes clear up weird problems, look he
http://www.granite.ab.ca/access/decompile.htm

Good Luck and let me know how it works out.



"Lucien" wrote:

The reference to Visual Basic for Applications is there and it is checked.
I scrolled down the entire list of references and none show "missing".



"Klatuu" wrote:

You have a reference problem. It should have returned A.
Open your VBA Editor. Click on Tools-References, and look for a reference
to Visual Basic for Applications. It needs to be there. Also look for any
references that show "Missing", they need to be fixed.

"Lucien" wrote:

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

I went through your instructions again and found that I am missing the
following reference:

MISSING: Microsoft Office Web Components Function Library

How do I fix this reference?



"Klatuu" wrote:

Take a look at this site. It may be a reference problem or it may be a
database corruption problem.

These sites have info on references:
http://allenbrowne.com/ser-38.html
http://www.accessmvp.com/djsteele/Ac...nceErrors.html

A decompile can sometimes clear up weird problems, look he
http://www.granite.ab.ca/access/decompile.htm

Good Luck and let me know how it works out.



"Lucien" wrote:

The reference to Visual Basic for Applications is there and it is checked.
I scrolled down the entire list of references and none show "missing".



"Klatuu" wrote:

You have a reference problem. It should have returned A.
Open your VBA Editor. Click on Tools-References, and look for a reference
to Visual Basic for Applications. It needs to be there. Also look for any
references that show "Missing", they need to be fixed.

"Lucien" wrote:

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

That library should not be the problem unless you are accessing the web
through Access. To include a libray, check the box on the left. If it can't
find the reference, you will have to browse for it. One of the sites I sent
you may have the name of the file you need to reference.

"Lucien" wrote:

I went through your instructions again and found that I am missing the
following reference:

MISSING: Microsoft Office Web Components Function Library

How do I fix this reference?



"Klatuu" wrote:

Take a look at this site. It may be a reference problem or it may be a
database corruption problem.

These sites have info on references:
http://allenbrowne.com/ser-38.html
http://www.accessmvp.com/djsteele/Ac...nceErrors.html

A decompile can sometimes clear up weird problems, look he
http://www.granite.ab.ca/access/decompile.htm

Good Luck and let me know how it works out.



"Lucien" wrote:

The reference to Visual Basic for Applications is there and it is checked.
I scrolled down the entire list of references and none show "missing".



"Klatuu" wrote:

You have a reference problem. It should have returned A.
Open your VBA Editor. Click on Tools-References, and look for a reference
to Visual Basic for Applications. It needs to be there. Also look for any
references that show "Missing", they need to be fixed.

"Lucien" wrote:

Oksy, this is the message when I entered ?Left("ABC",1) in VBA:

Print Left("ABC", 1)



"Klatuu" wrote:

Did you test to see if the Left function is working for you at all as I
suggested in an earlier Post? If not, please do. This will tell if you have
a VBA reference problem:

In case you don't have the instructions, here they are again. Try this and
post back:

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.



"Lucien" wrote:

Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:

"Undefined function 'Left' in expression"



"Klatuu" wrote:

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.