Date fields and how to eliminate some of them

[toby]

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

 

[John W. Vinson]

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

Is this actually a Date/Time field (with a format like ddmmmyyyy to make it
display as above)? Or is it a Text field containing what you see as a date,
but what Access will see as a meaningless 9-character text string? Take a look
at the Table in design view and see what's in the datatype property.

If it's a date field, use a criterion

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:]+1, 1)

If you literally mean the current month (i.e. February 2008 if you run the
query today, March 2008 if you run it next week), then use

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date())+1, 1)

If it's a text field you'll need some additional rigamarole to convert it to a
date which Access can understand.

 

[fredg]

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

Base the report upon a query that returns all of the records.

Add a new column to the query.
NewColumn:Format([DateField],"mmm yyyy")
Set it's criteria to
Format(Date(),"mmm yyyy")

When the report runs, only data for the current month will display.
I believe that is what you asked for.

A better solution would be to allow the user to enter the month and
year, so that you have the flexibility to show other months beside
just the current one.

In this case set the criteria for this NewColumn to:
[Enter the month and year as mmm yyyy]

Still better would be to have the user enter the month and year from a
form. Post back if that would be more suitable for you.

 

[toby]

It's actually a text field now that I've looked. and when I attempt to change
it to date/time format I get this message:

"Microsoft Access cannot change the data type. There isn't enough disc space
or memory."

Okay, so I copy the database to my hard drive where I know there is enough
space. same message

so, would you be able to show me how to format this text field so that I
only get the current month? many thanks.

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

Is this actually a Date/Time field (with a format like ddmmmyyyy to make it
display as above)? Or is it a Text field containing what you see as a date,
but what Access will see as a meaningless 9-character text string? Take a look
at the Table in design view and see what's in the datatype property.

If it's a date field, use a criterion

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:]+1, 1)

If you literally mean the current month (i.e. February 2008 if you run the
query today, March 2008 if you run it next week), then use

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date())+1, 1)

If it's a text field you'll need some additional rigamarole to convert it to a
date which Access can understand.

 

[toby]

Thanks fredg.....is what you wrote below based on the assumption that this is
a date formatted field? I'm only guessing because I tried your solutions and
they don't work (unless I'm doing something wrong, which is always a
possibility). for some reason, I cannot change this field to a date type.
so it remains a text field.

do you have any other suggestions based on a text field? many thanks.

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

Base the report upon a query that returns all of the records.

Add a new column to the query.
NewColumn:Format([DateField],"mmm yyyy")
Set it's criteria to
Format(Date(),"mmm yyyy")

When the report runs, only data for the current month will display.
I believe that is what you asked for.

A better solution would be to allow the user to enter the month and
year, so that you have the flexibility to show other months beside
just the current one.

In this case set the criteria for this NewColumn to:
[Enter the month and year as mmm yyyy]

Still better would be to have the user enter the month and year from a
form. Post back if that would be more suitable for you.

 

[toby]

Update of sorts.....I exported the table in the original database that
contains the "date" field as a text type thinking I could change the type
that way. Apparently not as now there is nothing in that field! I guess
I'll delete this test database. I was hoping that would work....

I'll keep checking here today to see if there are any solutions. thanks in
advance again for any help!

 

[John W. Vinson]

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

I'd suggest importing the data as text; adding a new Date/Time datatype field
(DON'T confuse the field *datatype* with the field's *format*, they are
completely different properties!). If you do indeed have data in the text
field you can then run an Update query updating the date/time field to

CDate(Format([textdate], "@@-@@@-@@@@"))

Put a criterion of

LIKE "##???####"

on the text field so you won't get errors from attempting to convert NULL or
unreadable dates.

 

[toby]

Okay, so when I attempt this I get a message like "not enough memory or disc
space to undo the data changes this action query is about to make". so I
thought it was my RAM, so I rebooted. still get that message. is that
normal? I hesitate to run this query even though I've made a copy. I'm just
nervous when I get a message like this. any advice would be greatly
appreciated. thanks....

I have a date field that shows up like this:

04nov2007

I need to filter out those that do not fall within the current month. If
the report is for November 2007, I want only Nov 2007 dates in there. My
report contains dates before Nov 2007. How can I get them out of my report?
I'm hoping there is an easy way to do this in Access.

Thanks in advance for any help!

toby

I'd suggest importing the data as text; adding a new Date/Time datatype field
(DON'T confuse the field *datatype* with the field's *format*, they are
completely different properties!). If you do indeed have data in the text
field you can then run an Update query updating the date/time field to

CDate(Format([textdate], "@@-@@@-@@@@"))

Put a criterion of

LIKE "##???####"

on the text field so you won't get errors from attempting to convert NULL or
unreadable dates.

 

[John W. Vinson]

Okay, so when I attempt this I get a message like "not enough memory or disc
space to undo the data changes this action query is about to make". so I
thought it was my RAM, so I rebooted. still get that message. is that
normal? I hesitate to run this query even though I've made a copy. I'm just
nervous when I get a message like this. any advice would be greatly
appreciated. thanks....

Fear not. Access will attempt to save all the changes so that you can roll
back an update, but if you have bazillions of rows it won't be able to do so.
Good on ya for keeping a backup (VERY good idea!!) - just trust it, and let
the update query run.

 

[toby]

well, this one is nearly 200,000 rows. does that qualify as bazillions?

 

[John W. Vinson]

well, this one is nearly 200,000 rows. does that qualify as bazillions?

Yes, in this context! It certainly won't be able to store all fields of all
those rows in memory. The update query will still run; you just won't be able
to say "OOPS!" and hit the Esc key to "un-run" it.