Convert string to date

  • Thread starter Thread starter dataH
  • Start date Start date
D

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
 
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
 
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
Click to expand...
Microsoft Office/Access MVP
Click to expand...
 
I'm having trouble imagining ... could you post an example of how it is
sorting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dataH said:
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
Click to expand...
Microsoft Office/Access MVP
Click to expand...









Jeff Boyce said:
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
Click to expand...
Click to expand...
 
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 said:
I'm having trouble imagining ... could you post an example of how it is
sorting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dataH said:
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
Click to expand...
Microsoft Office/Access MVP



Access 2007

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


Best regards
Click to expand...









Jeff Boyce said:
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
Click to expand...
Click to expand...
Click to expand...
 
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)


dataH said:
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 said:
I'm having trouble imagining ... could you post an example of how it is
sorting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dataH said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
I'm having trouble imagining ... could you post an example of how it is
sorting?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dataH said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
Click to expand...

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.
Click to expand...


Excellent
dataH




Douglas J. Steele said:
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)


dataH said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
 
Back
Top