Find File Name That Is Closest Match

[DMT Dave]

Hi Guy's is there a method to find a file name that is closest match to a Date ? I have searched into this and found Application.FileSearch but unsure if this is an access function or other MS office function

I am trying to find an image where the date is the right part of the file name but less than the CollectedDate

So an image is taken the file is auto called:

strDealer & " " & Format(Now(),"dd-mm-yyyy") & ".jpg"

example: we now have a file called DMT Dave-01-06-22.jpg

The product is collected days perhaps weeks afterwards (DB field CollectedDate) 07-06-22

So now want to find the image called DMT Dave-01-06-22 and use the Access NameAs Option to Name As DMT Dave-07-06-22 ? (01-06-22 is image date) to be renamed as (07-06-22 is the collected date)

NameAs Option I have used before it is finding the image that has a Maximum date on the right of file name that is Less than the collected date I Guess ?

Below is possibly a better way of understanding what I am trying to do

Lets Say the 2nd file down that has the image date of 11-04-22 was collected on 25-04-22, when I Double Click on the CollectedDate Field, the file i am looking to find and rename can't be the top file named 17-05-22 as the collected date field is 25-04-22 (prior to collected date)

Nor can the file to find be the file prior (29-03-22) as 11-04-22 is the closest match prior to collection date

I haven't started any code work for this

Hope i have made sense , forgive me if not

[Welshgasman]

Use Dmax() ?

[CJ_London]

because you are storing dates in dd/mm/yy text format, don't thing dmax will work - dates need to be stored in the format yyyy-mm-dd

you will need code to convert dd/mm/yy to yyyy-mm-dd or at least yymmdd for each file to determine the 'max'. Perhaps made more difficult if the rest of the filename can vary is size as indicated in your image.

You might be able to use your datemodified value although would be far easier to change your date formats to a sortable name

[orange]

?? Dave you are using Now() to generate a date with time, but formatting it to use only the Date???
Do you only have 1 image file per Date?
When you change a filename, you may end up with multiple files (at least duplicates) with same date. If you change filenames in the "wrong order", you risk changing an unintended file (as I read your process).

I'd backup your folder(s) before starting name changing, especially if this data is as important as your post suggests.

As Ajax suggested you may be able to use the file's DateLastModified. But you may also want to review your current process(es) to name files (and related documentation) to reduce this "best guess" approach.

[DMT Dave]

Hi guys thanks for your input,

don't think WGM Dmax will work as we are looking for a file name outside of access, stand to be corrected though....

Orange, yes only x 1 image per date definitely if that helps to find the correct method

Just though though, am i better having a temp table perhaps tblImageDate, when the image is generated add the name of the image file that has got the date formatted as: field1 (FileName) orange 04-06-22.jpg stored from initial image, perhaps another text field Status and auto have Rename in that field ?

Then when we tick the check box for collection (collectedDate) is then updated for all items on the image

Then will WGM option of DMax or DLookup File Name and do query Update to collected date

Call the file to match orange 04-06-22

Name As FileName From Table

Delete that record in the images table ?

Again guys if i am barking up the wrong tree then apologies

[DMT Dave]

Brief Process is

1: Image is stored in Oranges folder today called orange 07-06-22.jpg
2: Orange collects on 15-06-22
3: Check boxes are checked for this collection and collected date on database is 15/06/2022

Oranges Folder has got files named

orange 02-01-22
orange 28-01-22
orange 07-06-22 (we need to rename this file from 07-06-22 to 15-06-22)
orange 16-06-22 (can't be this file as the date is greater than the collection date of 15-06-22

Hope this makes sense

[CJ_London]

curious as to why you want to rename the file - surely just have a table with filename (including the creation date) and date collected fields. Renaming the file doesn't tell you anything, certainly not whether an image has been collected or not.

And what happens if you create an image on one day, another on the second day and the customer collects one or the other or both on the third day?

[Welshgasman]

Sorry I thought you were holding the details in a table.

Hi guys thanks for your input,

don't think WGM Dmax will work as we are looking for a file name outside of access, stand to be corrected though....

Orange, yes only x 1 image per date definitely if that helps to find the correct method

Just though though, am i better having a temp table perhaps tblImageDate, when the image is generated add the name of the image file that has got the date formatted as: field1 (FileName) orange 04-06-22.jpg stored from initial image, perhaps another text field Status and auto have Rename in that field ?

Then when we tick the check box for collection (collectedDate) is then updated for all items on the image

Then will WGM option of DMax or DLookup File Name and do query Update to collected date

Call the file to match orange 04-06-22

Name As FileName From Table

Delete that record in the images table ?

Again guys if i am barking up the wrong tree then apologies

[Gicu]

You need to populate an array of the file names in your source folder (https://www.codevba.com/office/loop_...m#.Yp-EJHbMI2w), sort it descending by created or modified date (https://stackoverflow.com/questions/...104206#5104206) then loop and get the first file that has the date less than your collection date; now issue the Name command to rename it.

Cheers,

[DMT Dave]

Hi guys, thank you all,

Ajax, the reason is, if i get an enquiry like: was this collected ?


I can then double click on the collected date from db then:

add the image along with all other collection details ie: products and add the image to the email that was collected so i was trying to match which image to add to mail body and it would be the image that is less than but closest to collection date

Hope that clarifies but i think i will look at Vlads and WGM suggestions also, perhaps a temp table with these names then i can find the file that matches either image date and/or collected date from 2 fields in the temp table

Will check out all of your comments and suggestions though.

thankyou very much for input

[CJ_London]

Ajax, the reason is, if i get an enquiry like: was this collected ?


I can then double click on the collected date from db then:

add the image along with all other collection details

so from my suggestion

'just have a table with filename (including the creation date) and date collected fields' why can't you 'match' on the filename rather than the date? If collection hasn't occurred, the collection date will be blank

Just seems your solution is more complex (as outlined in post #3) and prone to errors as suggested in the other part of my last post

Anyway - your project so up to you. I'll disengage from this thread