Selasa, 02 Februari 2016

RE: [MS_AccessPros] Convert Memo to Number in Query or Report


You're right, Duane. Thanks for correcting that.


---In, <> wrote :

I think the () are a bit wonky. Try:
(I have to add a ton of spaces to figure out what's where)

SortField: CLng(    Left(      Nz([Field_Value], "0") , 10 )    )
SortField: CLng(Left(Nz([Field_Value],"0"),10))

Duane Hookom, MVP
MS Access

Date: Mon, 1 Feb 2016 13:41:01 -0800
Subject: RE: [MS_AccessPros] Convert Memo to Number in Query or Report

Might also check for nulls.

SortField: CLng(Left(Nz([Field_Value], "0")), 10)

-Bill Mosca

---In, <> wrote :

Do you have numbers only in every record or are there other characters or blanks?
Duane Hookom, MVP
MS Access

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 [MS_Access_Professionals]" <>
Sent: Monday, February 1, 2016 10:21 AM
Subject: Re: [MS_AccessPros] Convert Memo to Number in Query or Report


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, [MS_Access_Professionals] <> 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?




Posted by:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)



Tidak ada komentar:

Posting Komentar