Dale,
Do you have numbers only in every record or are there other characters or blanks?
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Feb 2016 16:46:09 +0000
Subject: Re: [MS_AccessPros] Convert Memo to Number in Query or Report
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, February 1, 2016 10:21 AM
Subject: Re: [MS_AccessPros] Convert Memo to Number in Query or Report
Do you have numbers only in every record or are there other characters or blanks?
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Feb 2016 16:46:09 +0000
Subject: Re: [MS_AccessPros] Convert Memo to Number in Query or Report
When I run the query with this it gives the error: This expression is typed incorrectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements....I had to add an extra parenthesis from the expression below because it kept saying invalid argument when I would click away from the expression. SortField: CLng(Left[Field_Value], 10)) but it still will not run now because of the above error
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, February 1, 2016 10:21 AM
Subject: Re: [MS_AccessPros] Convert Memo to Number in Query or Report
Dale-
Add an expression to a query on the table something like:
SortField: CLng(Left([Field_Value]), 10)
.. then sort on that.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 1, 2016, at 3:20 PM, tenn_tazz26@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I am using MS Access 2007 and have a few tables linked to an SQL database. I have read only access to the database so I cannot change how it stores the data. There is a table that has a field ([Field_Value]) which is being stored as Memo field instead of a number. I am trying to create a query that will sort desecending (Largest number to lowest) by this Field_Value but because it is a memo field when it gets to the numbers 10, 11, 12, etc it is not sorting those correctly because it is not treating them as true numbers. It should be in the order of 12, 11, 10, 9, 8 etc but it is doing it more like 9,8,7,6,5,4,3,2,1, 10, 11, 12 etc. How do I make the query or a report treat this field value as a number instead of memo. When I go to properties in the query it is blank for Format and I thought maybe that is where I can set it but things I have tried did not work. Any ideas?
Thanks,
Dale
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar