How to pass results from mid function to datediff?

[Sgt_Utz]

It looks like it has something to do with the join. If I delete the "personnel" table and just use the "info" table (one with the attachments) in the query it works, even though the "info" table has a null record for attachments..

- I have all 3 checked: "Enforce Referential Integrity", "Cascade Update Related Fields", "Cascade Delete Related Fields".
- Join Type: "1: Only include rows where the joined fields from both tables are equal."

Hoping it's a noob mistake that's easily ID'd.

[June7]

Are both ID fields autonumber type? Cannot join on two autonumber fields.

If these tables have 1-to-1 relationship, might as well be 1 table.

If this is supposed to be 1-to-many relationship then need a number type field in tblInfo to save tlbPersonnel ID as a foreign key.

[Sgt_Utz]

Are both ID fields autonumber type? Cannot join on two autonumber fields.

If these tables have 1-to-1 relationship, might as well be 1 table.

If this is supposed to be 1-to-many relationship then need a number type field in tblInfo to save tlbPersonnel ID as a foreign key.

The personnel table is autonumber while the info is number (default cleared). Foreign key for info is the autonumber from the personnel.

It was originally 1 to many but then realized it needed to be 1 to 1. I'll probably combine them soon.

[June7]

I cannot replicate the issue. So if the cause is the table join (although I don't understand why), perhaps your original expression in query would work if tblPersonnel is removed.

I determined the array variable and DateSerial are not necessary. DateDiff() recognizes "2017-09-09" string as a valid date.

Public Function Foo(InVal As Variant) As Integer
If Not IsNull(InVal) Then
    Foo = DateDiff("d", Mid(InVal, InStrRev(InVal, " ") + 1, InStrRev(InVal, ".") - InStrRev(InVal, " ") - 1), Date())
End If
End Function

[Sgt_Utz]

Haha you may have unknowingly fixed it for me. I noticed your code above says "Public Function Foo(InVal As Variant) As Integer" as opposed to the original code which was "Public Function Foo(InVal As String) As Integer". Changing String to Variant seems to have fixed the issue.

From what I've seen on variant, it can accept strings, null and empty. String can't accept null, so I'm guessing it errors out before the function has the chance to do anything with it.

I cannot replicate the issue. So if the cause is the table join (although I don't understand why), perhaps your original expression in query would work if tblPersonnel is removed.

I determined the array variable and DateSerial are not necessary. DateDiff() recognizes "2017-09-09" string as a valid date.

Public Function Foo(InVal As Variant) As Integer
If Not IsNull(InVal) Then
    Foo = DateDiff("d", Mid(InVal, InStrRev(InVal, " ") + 1, InStrRev(InVal, ".") - InStrRev(InVal, " ") - 1), Date())
End If
End Function

[June7]

That was an intentional change I made in post 13. Guess I could have highlighted it. Glad it's working now. Is it working with the tables joined?

[Sgt_Utz]

Oh wow I didn't realize you made that change in post 13. yes it's working now

That was an intentional change I made in post 13. Guess I could have highlighted it. Glad it's working now. Is it working with the tables joined?

[Sgt_Utz]

Extra post... would be nice to be able to delete my own posts like many forums.

[June7]

Even in a 1-to-many relationship, embedding files into table would still need an attachment or OLEObject field (I've never used OLEObject field or controls).

Recommendation is to store objects in external folder and save file path\name into a text field. That is if you have a lot of objects you want to track because large files can quickly use up Access 2GB file size limit.

Each record in the many table would have one file path\name.

However, I just recently recommended in another post an option for user to embed two images into a table with a single record and two attachment fields. Because there were only the two images, not thousands. https://www.accessforums.net/showthread.php?t=67271

[Sgt_Utz]

Interesting. I'm guessing you'd set the form to "attach" a file which copies it to a folder, then use the access record as basically a pointer? Almost like the folder is part of the backend?

Even in a 1-to-many relationship, embedding files into table would still need an attachment or OLEObject field (I've never used OLEObject field or controls).

Recommendation is to store objects in external folder and save file path\name into a text field. That is if you have a lot of objects you want to track because large files can quickly use up Access 2GB file size limit.

Each record in the many table would have one file path\name.

However, I just recently recommended in another post an option for user to embed two images into a table with a single record and two attachment fields. Because there were only the two images, not thousands. https://www.accessforums.net/showthread.php?t=67271

[June7]

Some image type files (jpg, bmp, png, not pdf), can be displayed in Image control by referencing the file path\name field in ControlSource property.

Code can open files in their native application or some (such as PDF) can be displayed in an Internet Browser control on a form.

[Sgt_Utz]

Ok I'm trying this different method. I have a standard naming which is "last, first - purpose yyyy-mm-dd".

I'm looking at putting all files in a folder, then having a query return a list of all users that don't have "initial training". I currently have this working by searching file names of attachments, but now I need to set this up to search a folder. We have approximately 140 users and a lot of different paperwork to maintain so I need to make it as simple as possible to maintain.

[June7]

If you save the filename to a field and then want to verify the document is in folder, 'search' a folder with Dir():

If Dir("some path string here\" & [fieldname] <> "" Then MsgBox "File " & [fieldname] & " found."

[Sgt_Utz]

I put files in a folder for testing then created this query to find all users without "initial training".

It works... the problem is it takes forever and currently there's only 100 files to look through.

I get that it has to connect to the folder and do the search for each record in the db. How could I make this more efficient to filter the files for each user "(FOUO) Last, First" the search those files for the string?

For "initial training" it's easy as I'd just search "(FOUO) Last, First - Initial*", however for other types of records I'll have more than one (annual training) so I'll need to be able to return all matching records for each user.

[June7]

Post your query SQL statement.

What is (FOUO)?