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".
:
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.
:
Oksy, this is the message when I entered ?Left("ABC",1) in VBA:
Print Left("ABC", 1)
:
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.
:
Well, I tried Duane's and Karl's suggestions and I am still getting the same
error message:
"Undefined function 'Left' in expression"
:
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.
:
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
--
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"));
:
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.
:
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?
:
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
:
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.