Avatar of exp vg
exp vg
 asked on

Access - Delete Dates Cells Formatted as Short Text

I have a [Date] field that is Short Text that was derived with the formula:

Format([YourDatefield],"m/yyyy")

From an original field [Dt].

I now need to remove from the [Date] field any dates prior to 1/2015, but am unable to successfully do this with < 1/2015 as the criteria.

Please advise.

Thank you.
Microsoft Access

Avatar of undefined
Last Comment
exp vg

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
exp vg

ASKER
Since the format of the date field I created was m/yyyy

I tried this

Where Format([Date],"m/yyyy") < "1/2015"

And got an error on syntax.
Rey Obrero (Capricorn1)

try the other format i posted above


where format([date],"yyyymm") < "201501"
exp vg

ASKER
I want to delete the fields less than < 1/2015

Thank you.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
exp vg

ASKER
Forgot to mention - I tried the first one first - and received the same syntax error. I then tried the one I created to see if that would work.

Both did not.
Rey Obrero (Capricorn1)

you can NOT delete a field only with criteria, you will be deleting the RECORD.

you can DELETE the values of a field. By setting the value to NULL or a empty string ""

what you posted is not the same as i have posted. Look carefully
exp vg

ASKER
Let me clarify - delete the record where this date criteria is met.

I tried the one you posted as well.

Both did not work.

This is what I tried from you:

where format([date],"yyyymm") < "201501"
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

upload a copy of the db.
exp vg

ASKER
Unfortunately I cannot - this is sensitive data.

I actually just thought of this, I back tracked, and as mentioned in my question post, the 1/2015 was created from an original [Dt] field - which is in Date format.

I tried

< 1/1/2015, since the records will be the same for the calculated [Date] field that is in Short Text format. However, the records with dates less than 1/1/2015 are still not deleted.

Hope this makes sense.
exp vg

ASKER
Got it to work by deleting from the source date file that the new date field was formatted off of.

I do thank you for your suggestions.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck