Convert string to date

[dataH]

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]

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]

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

 

[Jeff Boyce]

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

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]

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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



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

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

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

 

[Jeff Boyce]

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

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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





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

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

 

[Steve Schapel]

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.

 

[dataH]

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

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

(no private e-mails, please)

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

 

[dataH]

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

 

[dataH]

OK, the property sheet format line will take a custom format ($#,##0) rather
than field line in the query where I've been trying to place it. .

Thanks to all
dataH