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.
Format([YourDatefield],"m/
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try the other format i posted above
where format([date],"yyyymm") < "201501"
where format([date],"yyyymm") < "201501"
ASKER
I want to delete the fields less than < 1/2015
Thank you.
Thank you.
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.
Both did not.
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
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
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"
I tried the one you posted as well.
Both did not work.
This is what I tried from you:
where format([date],"yyyymm") < "201501"
upload a copy of the db.
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.
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.
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.
I do thank you for your suggestions.
ASKER
I tried this
Where Format([Date],"m/yyyy") < "1/2015"
And got an error on syntax.