Access's 'Left' function not working properly

[Keke Lee]

The Left function from Espression building not returning proper value. When
I tried to put character lenght = "3", or "5" or "7" they all return no
value. When I put "12" it returns all value of fields of character length
equals to 12. It works like a filter rather than an extract.

 

[Bob Barrows [MVP]]

The Left function from Espression building not returning proper
value. When I tried to put character lenght = "3", or "5" or "7"
they all return no value. When I put "12" it returns all value of
fields of character length equals to 12. It works like a filter
rather than an extract.

It works fine for me. You will have to show us how to reproduce this.

 

[Keke Lee]

This is a sample I have just created (other than the one in my production,
but I think my previous issue is still the same):

Table:
Date Qty
7/11/2008 987832.1
7/12/2008 3752
7/13/2008 35628.38
7/14/2008 645.3
7/15/2008 91
7/16/2008 8
7/17/2008 57645
7/18/2008 954389
7/19/2008 8798532
7/20/2008 8682.94
7/21/2008 354.68

Query:
Left([testing]![Qty],3)
Query Result:
Qty
91
8

(my comment: should I expect to return 11 results with each shows the 1st 3
char of each field)

Query:
Left([testing]![Qty],5)
Query Result:
Qty
3752
91
8
57645

(my comment: look like fields with char length =< 5 will return)

Query:
Left([testing]![Qty],12)
Query Result:
Qty
987832.1
3752
35628.38
91
8
57645
954389
8798532
8682.94
354.68

(my comment: all values return for length size exceeds the max)


I have never seen such issue when I compose formula in Excel however.
Thanks for the help

 

[Bob Barrows [MVP]]

This is a sample I have just created (other than the one in my
production, but I think my previous issue is still the same):

Table:
Date Qty
7/11/2008 987832.1
7/12/2008 3752
7/13/2008 35628.38
7/14/2008 645.3
7/15/2008 91
7/16/2008 8
7/17/2008 57645
7/18/2008 954389
7/19/2008 8798532
7/20/2008 8682.94
7/21/2008 354.68

Query:
Left([testing]![Qty],3)
Query Result:
Qty
91
8

Please switch your query to SQL View (View menu or toolbar button) and show
us the sql statement being generated by Access. These results are certainly
not correct.

What is [testing]? Is that a form? I'm a little puzzled why the bang (!)
operator is being used rather than a period. If I were writing this query, I
would say:
Left([testing].[Qty],3)

 

[Keke Lee]

[testing] is a table. It makes no difference whether if seting the command
with '.' or '!' and results are the same from noth. The SQL view shows:

'
SELECT testing.Qty
FROM testing
WHERE (((testing.Qty)=Left([testing]![Qty],7)));
'

Thanks

This is a sample I have just created (other than the one in my
production, but I think my previous issue is still the same):

Table:
Date Qty
7/11/2008 987832.1
7/12/2008 3752
7/13/2008 35628.38
7/14/2008 645.3
7/15/2008 91
7/16/2008 8
7/17/2008 57645
7/18/2008 954389
7/19/2008 8798532
7/20/2008 8682.94
7/21/2008 354.68

Query:
Left([testing]![Qty],3)
Query Result:
Qty
91
8

Please switch your query to SQL View (View menu or toolbar button) and show
us the sql statement being generated by Access. These results are certainly
not correct.

What is [testing]? Is that a form? I'm a little puzzled why the bang (!)
operator is being used rather than a period. If I were writing this query, I
would say:
Left([testing].[Qty],3)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Of course it's acting like a filter: you put the expression into the
Criteria field (making it show up in the WHERE clause. You should have put
the expression into the top row of a new column in the grid. Here is how it
should look in the sql:

SELECT testing.Qty,Left([testing].[Qty],3) As LeftTest
FROM testing

Paste that into SQL View then switch to Design View to see how you should
have entered it into the grid.

[testing] is a table. It makes no difference whether if seting the
command with '.' or '!' and results are the same from noth. The SQL
view shows:

'
SELECT testing.Qty
FROM testing
WHERE (((testing.Qty)=Left([testing]![Qty],7)));
'

Thanks

This is a sample I have just created (other than the one in my
production, but I think my previous issue is still the same):

Table:
Date Qty
7/11/2008 987832.1
7/12/2008 3752
7/13/2008 35628.38
7/14/2008 645.3
7/15/2008 91
7/16/2008 8
7/17/2008 57645
7/18/2008 954389
7/19/2008 8798532
7/20/2008 8682.94
7/21/2008 354.68

Query:
Left([testing]![Qty],3)
Query Result:
Qty
91
8

Please switch your query to SQL View (View menu or toolbar button)
and show us the sql statement being generated by Access. These
results are certainly not correct.

What is [testing]? Is that a form? I'm a little puzzled why the bang
(!) operator is being used rather than a period. If I were writing
this query, I would say:
Left([testing].[Qty],3)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

 

[Keke Lee]

Thank you!

Of course it's acting like a filter: you put the expression into the
Criteria field (making it show up in the WHERE clause. You should have put
the expression into the top row of a new column in the grid. Here is how it
should look in the sql:

SELECT testing.Qty,Left([testing].[Qty],3) As LeftTest
FROM testing

Paste that into SQL View then switch to Design View to see how you should
have entered it into the grid.

[testing] is a table. It makes no difference whether if seting the
command with '.' or '!' and results are the same from noth. The SQL
view shows:

'
SELECT testing.Qty
FROM testing
WHERE (((testing.Qty)=Left([testing]![Qty],7)));
'

Thanks

Keke Lee wrote:
This is a sample I have just created (other than the one in my
production, but I think my previous issue is still the same):

Table:
Date Qty
7/11/2008 987832.1
7/12/2008 3752
7/13/2008 35628.38
7/14/2008 645.3
7/15/2008 91
7/16/2008 8
7/17/2008 57645
7/18/2008 954389
7/19/2008 8798532
7/20/2008 8682.94
7/21/2008 354.68

Query:
Left([testing]![Qty],3)
Query Result:
Qty
91
8


Please switch your query to SQL View (View menu or toolbar button)
and show us the sql statement being generated by Access. These
results are certainly not correct.

What is [testing]? Is that a form? I'm a little puzzled why the bang
(!) operator is being used rather than a period. If I were writing
this query, I would say:
Left([testing].[Qty],3)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"