Weird Date Problem

[Guest]

Hi there,

I am having a weird date problem and I just cannot figure out why.

I have some date data in a table which when I pull all of them and put them
in ascending order looks like this:

10/2/2006
10/20/2006
10/22/2006
10/24/2006
10/24/2006
10/29/2006
10/30/2006
10/5/2006

The problem record is the last one which will not sort correctly. I have
double and triple checked to make sure the data is in Short Date format.

For reports I simple cannot get the problem record (10/5/2006) to display
when i specify a date range that would normally include it. I am having this
problem with a few random date records in the database.

I have even reformatted it: : Format([DateRecord],"Short Date") to ensure
that it in indeed in the correct format and even used the same formatting yet
again in the criteria to ensure it is also in the same format. Yet a few of
the records simply do not want to cooperate.

Anyone have an idea why?

Thanks

 

[ruralguy via AccessMonster.com]

You are experiencing an ASCII Text sort. Don't format the field, just sort

Hi there,

I am having a weird date problem and I just cannot figure out why.

I have some date data in a table which when I pull all of them and put them
in ascending order looks like this:

10/2/2006
10/20/2006
10/22/2006
10/24/2006
10/24/2006
10/29/2006
10/30/2006
10/5/2006

The problem record is the last one which will not sort correctly. I have
double and triple checked to make sure the data is in Short Date format.

For reports I simple cannot get the problem record (10/5/2006) to display
when i specify a date range that would normally include it. I am having this
problem with a few random date records in the database.

I have even reformatted it: : Format([DateRecord],"Short Date") to ensure
that it in indeed in the correct format and even used the same formatting yet
again in the criteria to ensure it is also in the same format. Yet a few of
the records simply do not want to cooperate.

Anyone have an idea why?

Thanks

 

[Guest]

The issue was brought to my attention when there was no formatting at all in
it.

The formatting was me trying different things to get it to work correctly.

You are experiencing an ASCII Text sort. Don't format the field, just sort

Hi there,

I am having a weird date problem and I just cannot figure out why.

I have some date data in a table which when I pull all of them and put them
in ascending order looks like this:

10/2/2006
10/20/2006
10/22/2006
10/24/2006
10/24/2006
10/29/2006
10/30/2006
10/5/2006

The problem record is the last one which will not sort correctly. I have
double and triple checked to make sure the data is in Short Date format.

For reports I simple cannot get the problem record (10/5/2006) to display
when i specify a date range that would normally include it. I am having this
problem with a few random date records in the database.

I have even reformatted it: : Format([DateRecord],"Short Date") to ensure
that it in indeed in the correct format and even used the same formatting yet
again in the criteria to ensure it is also in the same format. Yet a few of
the records simply do not want to cooperate.

Anyone have an idea why?

Thanks

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Is the field type a Date/Time data type or Text?

The issue was brought to my attention when there was no formatting at all in
it.

The formatting was me trying different things to get it to work correctly.

You are experiencing an ASCII Text sort. Don't format the field, just sort
on it.

[quoted text clipped - 30 lines]

 

[Guest]

Yes it is. There was no reson for it to not be working which was why I was
posting on here.

But its all moot now, I converted it all to text and made a formatting
function to do it correctly.

Thanks for trying to help.

Is the field type a Date/Time data type or Text?

The issue was brought to my attention when there was no formatting at all in
it.

The formatting was me trying different things to get it to work correctly.

You are experiencing an ASCII Text sort. Don't format the field, just sort
on it.

[quoted text clipped - 30 lines]

Thanks

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Matthias Klaey]

Yes it is. There was no reson for it to not be working which was why I was
posting on here.

But its all moot now, I converted it all to text and made a formatting
function to do it correctly.

Thanks for trying to help.

Is the field type a Date/Time data type or Text?

The issue was brought to my attention when there was no formatting at all in
it.

The formatting was me trying different things to get it to work correctly.

You are experiencing an ASCII Text sort. Don't format the field, just sort
on it.
[quoted text clipped - 30 lines]

Thanks

[...]

Trying to clarify: If [DateRecord] is a Date/Time field in your table,
you can do something like this in the query underlying the report:

SELECT Format([DateRecord],"<your preferred format>" AS DisplayDate
ORDER BY [DateRecord];

Note that the ordering is on the "pure" field, not on the formatted
field. This should give you the correct order for any format that you
wish to display.

HTH
Matthias Kläy

 

[Dan]

I am having a weird date problem and I just cannot figure out why.

- Overheard by many people as my date spoke into her cell phone.