Date Format

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

Guest

I currently have a date field formatted as number, ex.110690.
I want to create a query that just searches all the records by the year.
Is there a way that access can exclude the first 4 digits and only query by
the last 2?
 
I currently have a date field formatted as number, ex.110690.
I want to create a query that just searches all the records by the year.
Is there a way that access can exclude the first 4 digits and only query by
the last 2?
Click to expand...

What's the datatype of the field? If it's a Date/Time field the format
is actually irrelevant - it's not stored as 110690, it's stored as
33183.0000000000, since November 6, 1990 was 33183 days after the date
start point for Access (December 30, 1899).

If you want all records in 1990, and it's a date/time field, use a
criterion of

BETWEEN #1/1/1990# AND #12/31/1990#

or, more flexibly,
= DateSerial([Enter year:], 1, 1) AND < DateSerial([Enter year:] + 1, 1, 1)
Click to expand...

If you're storing your "dates" in a Number or Text field instead of a
date field, post back and explain.

John W. Vinson[MVP]
 
Add a field to the query design grid (by typing into the top of an empty
column) that uses the Right function (DateField is your date field):

YearOnly: Right([DateField],2)

Then sort by that field. You will probably want to put the new field to the
left side of the query grid, and sort by DateField also. That way it will
sort by the new field first, then by DateField.

Also, take a look at Left and Mid functions in Help, not for this situation
necessarily, but it's good to know about them.
 
Yes, the date is stored as a number. I don't have control of the actual
properties, but it's formatted as that. I know that if it were setup as a
date/time, it would be easier, but its setup this way, and I can't change it.
Just need to work with it.
 
What do you mean by right function?

BruceM said:
Add a field to the query design grid (by typing into the top of an empty
column) that uses the Right function (DateField is your date field):

YearOnly: Right([DateField],2)

Then sort by that field. You will probably want to put the new field to the
left side of the query grid, and sort by DateField also. That way it will
sort by the new field first, then by DateField.

Also, take a look at Left and Mid functions in Help, not for this situation
necessarily, but it's good to know about them.

Metalteck said:
I currently have a date field formatted as number, ex.110690.
I want to create a query that just searches all the records by the year.
Is there a way that access can exclude the first 4 digits and only query by
the last 2?
Click to expand...
Click to expand...
 
He showed you what he meant by "right function" in the fourth line of his
post.

--
Rick B



Metalteck said:
What do you mean by right function?

BruceM said:
Add a field to the query design grid (by typing into the top of an empty
column) that uses the Right function (DateField is your date field):

YearOnly: Right([DateField],2)

Then sort by that field. You will probably want to put the new field to the
left side of the query grid, and sort by DateField also. That way it will
sort by the new field first, then by DateField.

Also, take a look at Left and Mid functions in Help, not for this situation
necessarily, but it's good to know about them.

Metalteck said:
I currently have a date field formatted as number, ex.110690.
I want to create a query that just searches all the records by the year.
Is there a way that access can exclude the first 4 digits and only query by
the last 2?
Click to expand...
Click to expand...
Click to expand...
 
Ok, I see

I use this, but it asks me for the desired year I want. When I put the year,
it puts that as the number for all fields. for ex.
SLADMT YearOnly
110690 05
121689 05
112487 05
 
My original response assumed that 110690 stood for November 6, 1990 (or maybe
June 11, 1990, but 1990 in either case). If 110690 does not stand for either
of these dates, what does it stand for? The Right function as I wrote it
will lop off the rightmost two digits from the number.
 
110690 does stand for November 6,1990. But it is stored as number, and when I
use your function, it asks me to enter the datefield. If I enter 05, it will
look like this

SLADMT YearOnly
110690 05
121689 05
112487 05
112487 05
 
I currently have a date field formatted as number, ex.110690.
I want to create a query that just searches all the records by the year.
Is there a way that access can exclude the first 4 digits and only query by
the last 2?
Click to expand...

If (as noted downthread) the field is a Number/Long Integer, you can
extract the last two digits using the MOD operator. Let's say the name
of your field is Datefield (you don't say, so none of us know). Try
putting

YearNum: [Datefield] MOD 100

This will be an integer number containing the last two digits of your
number field.

This date format is, of course, not Y2K compliant. Is 101500 October
15 2000? or 1900? What about 101510 - 2010 or 1910?

John W. Vinson[MVP]
 
In table design view, the data type for the field containing 110690 is
Number? If so, did you base a query on the table, and place the formula I
suggested into the top row of an empty column in the query design grid? (You
need to be in query desgn view.) If so, what happened when you switched from
design view to datasheet view for the query?
 
Back
Top