Date Problem now

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has the column for the date and it is in the format for Date/Text in mmm-yy format. I have queries that i run where you enter the period for a starting point and ending point. It is only working for my 2000 dates and not my 2001,2002 or 2003. An example is below
Between [Enter Current Month or beginning date for YTD(MMM-YY format)] And [Enter Current Month or Ending date for YTD(MMM-YY format)
When i enter jan-00 to dec-00 i get my numbers
but when i enter jan-03 to dec-03 i don't get anything
I have access 2002 and i run on windows 2000 pr
 
The format of your date/time field in the table has *nothing* to do with
what is stored.

Access always stores dates as numbers with the integer portion, the number
of days since Dec 30, 1899 and the decimal portion is the time as a fraction
of the day.

Formatting the field as mmm-yy merely dictates how it will be *displayed* to
you.

It is likely that the results you get for jan-00 to dec-00 are incorrect.

Anyway, in your query in an empty column put
Format([YourDateField], "mmm-yy")
and put your criteria Between..... beneath this.

--
Joan Wild
Microsoft Access MVP

Sknagel said:
I have a table that has the column for the date and it is in the format
Click to expand...
for Date/Text in mmm-yy format. I have queries that i run where you enter
the period for a starting point and ending point. It is only working for my
2000 dates and not my 2001,2002 or 2003. An example is below:
Between [Enter Current Month or beginning date for YTD(MMM-YY format)] And
Click to expand...
[Enter Current Month or Ending date for YTD(MMM-YY format)]
 
Dear Sknagel:

I see two problem areas here.

First, you need to think of BETWEEN as requiring that the software
must know how to put the values in order. As I understand you, the
field is text and is formatted mmm-yy. This would simply sort in
alphabetical order:

Apr
Aug
Dec
Feb
Jan
Jul
Jun
Mar
May
Oct
Nov
Sep

That's not the order of the months on a calendar! I recommend you
change the way months are designated to have 2 digit month numbers,
with leading zeros on the first 9 months. They will then sort
properly, which is a prerequisite to using BETWEEN or < or >.

Next look at how they will sort when a year is included:

01-00
01-01
01-02
01-03
01-04
02-00
02-01
02-02
02-03
03-00

The problem here is that all the January dates sort together, with
year as a secondary portion of the sort. Again, not like a real
calendar.

If you put the year first, that is if you use yy-mm format, then the
text values will sort properly. But you MUST have the leading zeroes
on the months and on the years for it to work.

Without a thorough review of what you have now, I'd say that this is
likely to be the cause of what you're seeing.

But, did you mother actually name you Sknagel? Or would you rather I
call you Steve?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Actually my name is sarah, sorry

ANd my query when i show the period field, use to show 2003 and 2002. I would run it and it got all of my nov-03 entries. Now it is pulling all of my entries for 2000 when i show that field in period. I have it formated as mmm-yy but actually when i importing it, it was a full date.
 
Back
Top