Access 2007

Want to convert the string (1970) to a date so i can sort a column in a
query based on newest date rather than the A-Z sort.


Best regards

dataH

If you are proposing to use the Date/Time data type in Access, you need a
real date. "1970" is not a real date, but an entire year.

What's wrong with 1970 -- what's wrong with the A-Z sort? What are you
getting that you don't expect/want?

More info, please.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dataH" <(E-Mail Removed)> wrote in message
news:%ksAl.23129$(E-Mail Removed)...
> Access 2007
>
> Want to convert the string (1970) to a date so i can sort a column in a
> query based on newest date rather than the A-Z sort.
>
>
> Best regards
>
> dataH
>

OK, you are correct. I was expecting something else.




Better example below:



Here is an example: I have a number field in a query. This field is not
formatted with 000,000 etc. For ease of reading, I would like to format
this field so 55555 becomes 55,555. The following causes these numbers to
sort A-Z rather than largest to smallest



Bldg: Format([NumberField],"#,##0")



500

501

55,000

56,000

600

601

6,000



Same with Currency formatting. My sale field is a number (no $ or ,) that
sorts largest to smallest which is what I want, however, the $ formatting
makes the sort A-Z causing the strange sort order.



SalePrice: FormatCurrency([Sale],0)





What conversion function or formatting should I use to retail correct large
to small sort.





Best regards



dataH





>

> Jeff Boyce

> Microsoft Office/Access MVP

>

> "dataH" <(E-Mail Removed)> wrote in message

> news:%ksAl.23129$(E-Mail Removed)...

> > Access 2007

> >

> > Want to convert the string (1970) to a date so i can sort a column in a

> > query based on newest date rather than the A-Z sort.

> >

> >

> > Best regards

> >

> > dataH

> >

>

>









"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you are proposing to use the Date/Time data type in Access, you need a
> real date. "1970" is not a real date, but an entire year.
>
> What's wrong with 1970 -- what's wrong with the A-Z sort? What are you
> getting that you don't expect/want?
>
> More info, please.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "dataH" <(E-Mail Removed)> wrote in message
> news:%ksAl.23129$(E-Mail Removed)...
>> Access 2007
>>
>> Want to convert the string (1970) to a date so i can sort a column in a
>> query based on newest date rather than the A-Z sort.
>>
>>
>> Best regards
>>
>> dataH
>>
>
>

I'm having trouble imagining ... could you post an example of how it is
sorting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dataH" <(E-Mail Removed)> wrote in message
news:yDtAl.22887$(E-Mail Removed)...
> OK, you are correct. I was expecting something else.
>
>
>
>
> Better example below:
>
>
>
> Here is an example: I have a number field in a query. This field is not
> formatted with 000,000 etc. For ease of reading, I would like to format
> this field so 55555 becomes 55,555. The following causes these numbers to
> sort A-Z rather than largest to smallest
>
>
>
> Bldg: Format([NumberField],"#,##0")
>
>
>
> 500
>
> 501
>
> 55,000
>
> 56,000
>
> 600
>
> 601
>
> 6,000
>
>
>
> Same with Currency formatting. My sale field is a number (no $ or ,) that
> sorts largest to smallest which is what I want, however, the $ formatting
> makes the sort A-Z causing the strange sort order.
>
>
>
> SalePrice: FormatCurrency([Sale],0)
>
>
>
>
>
> What conversion function or formatting should I use to retail correct
> large to small sort.
>
>
>
>
>
> Best regards
>
>
>
> dataH
>
>
>
>
>
>>
>
>> Jeff Boyce
>
>> Microsoft Office/Access MVP
>
>>
>
>> "dataH" <(E-Mail Removed)> wrote in message
>
>> news:%ksAl.23129$(E-Mail Removed)...
>
>> > Access 2007
>
>> >
>
>> > Want to convert the string (1970) to a date so i can sort a column in a
>
>> > query based on newest date rather than the A-Z sort.
>
>> >
>
>> >
>
>> > Best regards
>
>> >
>
>> > dataH
>
>> >
>
>>
>
>>
>
>
>
>
>
>
>
>
>
> "Jeff Boyce" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> If you are proposing to use the Date/Time data type in Access, you need a
>> real date. "1970" is not a real date, but an entire year.
>>
>> What's wrong with 1970 -- what's wrong with the A-Z sort? What are you
>> getting that you don't expect/want?
>>
>> More info, please.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "dataH" <(E-Mail Removed)> wrote in message
>> news:%ksAl.23129$(E-Mail Removed)...
>>> Access 2007
>>>
>>> Want to convert the string (1970) to a date so i can sort a column in a
>>> query based on newest date rather than the A-Z sort.
>>>
>>>
>>> Best regards
>>>
>>> dataH
>>>
>>
>>
>
>

This example is from the currency formating and the resulting sort order

$1,123,000
$1,175,000
$1,183,500
$1,200
$1,200,000
$1,231,200
$1,250
$1,250,000
$1,277,500
$1,284,530
$1,290


"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I'm having trouble imagining ... could you post an example of how it is
> sorting?
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "dataH" <(E-Mail Removed)> wrote in message
> news:yDtAl.22887$(E-Mail Removed)...
>> OK, you are correct. I was expecting something else.
>>
>>
>>
>>
>> Better example below:
>>
>>
>>
>> Here is an example: I have a number field in a query. This field is not
>> formatted with 000,000 etc. For ease of reading, I would like to format
>> this field so 55555 becomes 55,555. The following causes these numbers
>> to sort A-Z rather than largest to smallest
>>
>>
>>
>> Bldg: Format([NumberField],"#,##0")
>>
>>
>>
>> 500
>>
>> 501
>>
>> 55,000
>>
>> 56,000
>>
>> 600
>>
>> 601
>>
>> 6,000
>>
>>
>>
>> Same with Currency formatting. My sale field is a number (no $ or ,)
>> that sorts largest to smallest which is what I want, however, the $
>> formatting makes the sort A-Z causing the strange sort order.
>>
>>
>>
>> SalePrice: FormatCurrency([Sale],0)
>>
>>
>>
>>
>>
>> What conversion function or formatting should I use to retail correct
>> large to small sort.
>>
>>
>>
>>
>>
>> Best regards
>>
>>
>>
>> dataH
>>
>>
>>
>>
>>
>>>
>>
>>> Jeff Boyce
>>
>>> Microsoft Office/Access MVP
>>
>>>
>>
>>> "dataH" <(E-Mail Removed)> wrote in message
>>
>>> news:%ksAl.23129$(E-Mail Removed)...
>>
>>> > Access 2007
>>
>>> >
>>
>>> > Want to convert the string (1970) to a date so i can sort a column in
>>> > a
>>
>>> > query based on newest date rather than the A-Z sort.
>>
>>> >
>>
>>> >
>>
>>> > Best regards
>>
>>> >
>>
>>> > dataH
>>
>>> >
>>
>>>
>>
>>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> "Jeff Boyce" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> If you are proposing to use the Date/Time data type in Access, you need
>>> a real date. "1970" is not a real date, but an entire year.
>>>
>>> What's wrong with 1970 -- what's wrong with the A-Z sort? What are you
>>> getting that you don't expect/want?
>>>
>>> More info, please.
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>> "dataH" <(E-Mail Removed)> wrote in message
>>> news:%ksAl.23129$(E-Mail Removed)...
>>>> Access 2007
>>>>
>>>> Want to convert the string (1970) to a date so i can sort a column in a
>>>> query based on newest date rather than the A-Z sort.
>>>>
>>>>
>>>> Best regards
>>>>
>>>> dataH
>>>>
>>>
>>>
>>
>>
>
>

Because you're using the Format function, the value is being converted to a
string. If you strictly set the Format property of the field, the value
wouldn't be converted. If you keep the function, you can add the field
unformatted to the query, and sort on it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"dataH" <(E-Mail Removed)> wrote in message
news:smuAl.22901$(E-Mail Removed)...
> This example is from the currency formating and the resulting sort order
>
> $1,123,000
> $1,175,000
> $1,183,500
> $1,200
> $1,200,000
> $1,231,200
> $1,250
> $1,250,000
> $1,277,500
> $1,284,530
> $1,290
>
>
> "Jeff Boyce" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> I'm having trouble imagining ... could you post an example of how it is
>> sorting?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "dataH" <(E-Mail Removed)> wrote in message
>> news:yDtAl.22887$(E-Mail Removed)...
>>> OK, you are correct. I was expecting something else.
>>>
>>>
>>>
>>>
>>> Better example below:
>>>
>>>
>>>
>>> Here is an example: I have a number field in a query. This field is
>>> not formatted with 000,000 etc. For ease of reading, I would like to
>>> format this field so 55555 becomes 55,555. The following causes these
>>> numbers to sort A-Z rather than largest to smallest
>>>
>>>
>>>
>>> Bldg: Format([NumberField],"#,##0")
>>>
>>>
>>>
>>> 500
>>>
>>> 501
>>>
>>> 55,000
>>>
>>> 56,000
>>>
>>> 600
>>>
>>> 601
>>>
>>> 6,000
>>>
>>>
>>>
>>> Same with Currency formatting. My sale field is a number (no $ or ,)
>>> that sorts largest to smallest which is what I want, however, the $
>>> formatting makes the sort A-Z causing the strange sort order.
>>>
>>>
>>>
>>> SalePrice: FormatCurrency([Sale],0)
>>>
>>>
>>>
>>>
>>>
>>> What conversion function or formatting should I use to retail correct
>>> large to small sort.

As Doug points out, the sorting is exactly correct, as you've requested.

Having sample data to look at makes it immediately obvious ... the sort is
alphanumeric, not numeric.

If you want a numeric sort, feed it numeric data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dataH" <(E-Mail Removed)> wrote in message
news:smuAl.22901$(E-Mail Removed)...
> This example is from the currency formating and the resulting sort order
>
> $1,123,000
> $1,175,000
> $1,183,500
> $1,200
> $1,200,000
> $1,231,200
> $1,250
> $1,250,000
> $1,277,500
> $1,284,530
> $1,290
>
>
> "Jeff Boyce" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> I'm having trouble imagining ... could you post an example of how it is
>> sorting?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "dataH" <(E-Mail Removed)> wrote in message
>> news:yDtAl.22887$(E-Mail Removed)...
>>> OK, you are correct. I was expecting something else.
>>>
>>>
>>>
>>>
>>> Better example below:
>>>
>>>
>>>
>>> Here is an example: I have a number field in a query. This field is
>>> not formatted with 000,000 etc. For ease of reading, I would like to
>>> format this field so 55555 becomes 55,555. The following causes these
>>> numbers to sort A-Z rather than largest to smallest
>>>
>>>
>>>
>>> Bldg: Format([NumberField],"#,##0")
>>>
>>>
>>>
>>> 500
>>>
>>> 501
>>>
>>> 55,000
>>>
>>> 56,000
>>>
>>> 600
>>>
>>> 601
>>>
>>> 6,000
>>>
>>>
>>>
>>> Same with Currency formatting. My sale field is a number (no $ or ,)
>>> that sorts largest to smallest which is what I want, however, the $
>>> formatting makes the sort A-Z causing the strange sort order.
>>>
>>>
>>>
>>> SalePrice: FormatCurrency([Sale],0)
>>>
>>>
>>>
>>>
>>>
>>> What conversion function or formatting should I use to retail correct
>>> large to small sort.
>>>
>>>
>>>
>>>
>>>
>>> Best regards
>>>
>>>
>>>
>>> dataH
>>>
>>>
>>>
>>>
>>>
>>>>
>>>
>>>> Jeff Boyce
>>>
>>>> Microsoft Office/Access MVP
>>>
>>>>
>>>
>>>> "dataH" <(E-Mail Removed)> wrote in message
>>>
>>>> news:%ksAl.23129$(E-Mail Removed)...
>>>
>>>> > Access 2007
>>>
>>>> >
>>>
>>>> > Want to convert the string (1970) to a date so i can sort a column in
>>>> > a
>>>
>>>> > query based on newest date rather than the A-Z sort.
>>>
>>>> >
>>>
>>>> >
>>>
>>>> > Best regards
>>>
>>>> >
>>>
>>>> > dataH
>>>
>>>> >
>>>
>>>>
>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Jeff Boyce" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> If you are proposing to use the Date/Time data type in Access, you need
>>>> a real date. "1970" is not a real date, but an entire year.
>>>>
>>>> What's wrong with 1970 -- what's wrong with the A-Z sort? What are you
>>>> getting that you don't expect/want?
>>>>
>>>> More info, please.
>>>>
>>>> Regards
>>>>
>>>> Jeff Boyce
>>>> Microsoft Office/Access MVP
>>>>
>>>> "dataH" <(E-Mail Removed)> wrote in message
>>>> news:%ksAl.23129$(E-Mail Removed)...
>>>>> Access 2007
>>>>>
>>>>> Want to convert the string (1970) to a date so i can sort a column in
>>>>> a query based on newest date rather than the A-Z sort.
>>>>>
>>>>>
>>>>> Best regards
>>>>>
>>>>> dataH
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

DataH,

PMFJI. I assume the purpose of this query is to serve as the record source
for a report or a form.

You don't need to use a Format *function* in the query to convert the data.
You can use the Format *property* of the textbox on the form or report to
display the data as you wish. If you do that, the sorting will be correct.

--

Steve Schapel, Microsoft Access MVP


"dataH" <(E-Mail Removed)> wrote in message
news:smuAl.22901$(E-Mail Removed)...
> This example is from the currency formating and the resulting sort order
>
> $1,123,000
> $1,175,000
> $1,183,500
> $1,200
> $1,200,000
> $1,231,200
> $1,250
> $1,250,000
> $1,277,500
> $1,284,530
> $1,290
>

Thanks Doug, this raises additional questions.

Understood:
Because you're using the Format function, the value is being converted to a
> string. If you strictly set the Format property of the field, the value
> wouldn't be converted

However, the choice for currency includes 2 places right of the decimal. Is
there a way to set zero places right of the decimal?

Need Clearification
If you keep the function, you can add the field
> unformatted to the query, and sort on it.


Excellent
dataH




"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> Because you're using the Format function, the value is being converted to
> a string. If you strictly set the Format property of the field, the value
> wouldn't be converted. If you keep the function, you can add the field
> unformatted to the query, and sort on it.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "dataH" <(E-Mail Removed)> wrote in message
> news:smuAl.22901$(E-Mail Removed)...
>> This example is from the currency formating and the resulting sort order
>>
>> $1,123,000
>> $1,175,000
>> $1,183,500
>> $1,200
>> $1,200,000
>> $1,231,200
>> $1,250
>> $1,250,000
>> $1,277,500
>> $1,284,530
>> $1,290
>>
>>
>> "Jeff Boyce" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> I'm having trouble imagining ... could you post an example of how it is
>>> sorting?
>>>
>>> Regards
>>>
>>> Jeff Boyce
>>> Microsoft Office/Access MVP
>>>
>>> "dataH" <(E-Mail Removed)> wrote in message
>>> news:yDtAl.22887$(E-Mail Removed)...
>>>> OK, you are correct. I was expecting something else.
>>>>
>>>>
>>>>
>>>>
>>>> Better example below:
>>>>
>>>>
>>>>
>>>> Here is an example: I have a number field in a query. This field is
>>>> not formatted with 000,000 etc. For ease of reading, I would like to
>>>> format this field so 55555 becomes 55,555. The following causes these
>>>> numbers to sort A-Z rather than largest to smallest
>>>>
>>>>
>>>>
>>>> Bldg: Format([NumberField],"#,##0")
>>>>
>>>>
>>>>
>>>> 500
>>>>
>>>> 501
>>>>
>>>> 55,000
>>>>
>>>> 56,000
>>>>
>>>> 600
>>>>
>>>> 601
>>>>
>>>> 6,000
>>>>
>>>>
>>>>
>>>> Same with Currency formatting. My sale field is a number (no $ or ,)
>>>> that sorts largest to smallest which is what I want, however, the $
>>>> formatting makes the sort A-Z causing the strange sort order.
>>>>
>>>>
>>>>
>>>> SalePrice: FormatCurrency([Sale],0)
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> What conversion function or formatting should I use to retail correct
>>>> large to small sort.
>
>

Thanks for all the help, a true wealth of knowledge

I'm using this information in a datasheet resulting from the query result,
which i can then copy and paste to a spreadsheet all formated.
dataH





"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> DataH,
>
> PMFJI. I assume the purpose of this query is to serve as the record
> source for a report or a form.
>
> You don't need to use a Format *function* in the query to convert the
> data. You can use the Format *property* of the textbox on the form or
> report to display the data as you wish. If you do that, the sorting will
> be correct.
>
> --
>
> Steve Schapel, Microsoft Access MVP
>
>
> "dataH" <(E-Mail Removed)> wrote in message
> news:smuAl.22901$(E-Mail Removed)...
>> This example is from the currency formating and the resulting sort order
>>
>> $1,123,000
>> $1,175,000
>> $1,183,500
>> $1,200
>> $1,200,000
>> $1,231,200
>> $1,250
>> $1,250,000
>> $1,277,500
>> $1,284,530
>> $1,290
>>
>
>