UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Find Missing Numbers In A Sequence Using A Query, Access 2016    
 
   
Oblio
post Sep 12 2017, 09:26 AM
Post#21



Posts: 126
Joined: 5-February 15



Oh Wow I had not noticed you were from Florida... I am so sorry to see what is happening and I hope and pray you and yours are safe and sound...

Good Luck wished for you as well !!!!

Wish I could help... but if there is a way, please let me know !

Bill
Go to the top of the page
 
ADezii
post Sep 12 2017, 09:47 AM
Post#22



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks River59 and Oblio!

Here is what I have come up with. Hopefully it is something close to what you are looking for since I am still a little hazy on the details. In any event, take a look and see.
This post has been edited by ADezii: Sep 12 2017, 09:48 AM
Attached File(s)
Attached File  Missing_Folder_Numbers_v_1_Revised.zip ( 616.41K )Number of downloads: 3
 
Go to the top of the page
 
River59
post Sep 12 2017, 10:06 AM
Post#23



Posts: 1,253
Joined: 7-April 10
From: Detroit, MI


Nice solution, ADezii. I have never used a rs.Clone but this is going to be a nice piece in my bag of tricks. OP can write these off to a table to refer to for any reporting needed.
Again, stay safe and I hope you didn't suffer too much loss with IRMA.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
ADezii
post Sep 12 2017, 11:12 AM
Post#24



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks again, River59.
    Just another simple point. You can also create the following Query with any Table being in the Grid (one with minimal number of Records).
    SQL
    SELECT TOP 1 fFindMissingFolders() AS Missing_Folders
    FROM t_Users;
  1. Query OUTPUT:
    Missing_Folders
    11, 12, 14, 18
Go to the top of the page
 
Oblio
post Sep 12 2017, 11:20 AM
Post#25



Posts: 126
Joined: 5-February 15



THANK YOU SO SO MUCH!

I feel awful thinking about what you must have been through and continue to go through. My family has had a condo in Ft. Lauderdale at the Port and it breaks my heart to know the damage caused by these hurricanes... we had so many great years there, but have been evacuated a couple of times and that is no easy task. To think you have been trying to get this to me in the midst of all this is surely a testament to your character !!!!

Please, please stay safe and I continue to pray for your safety and your friends and families as well as your state...

and thank you again so much !!!

Again anything I can do to help just let me know !!!

Warmest Regards,

Bill
Go to the top of the page
 
ADezii
post Sep 12 2017, 11:42 AM
Post#26



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks for the concern and yw.gif

BTW, does the Code satisfy your needs?
This post has been edited by ADezii: Sep 12 2017, 11:43 AM
Go to the top of the page
 
Oblio
post Sep 12 2017, 12:54 PM
Post#27



Posts: 126
Joined: 5-February 15



It really does !!! I do have a bunch of subsequent questions related to the function and the form... but I am on a brutal deadline to get this done as I have to show this tomorrow at 130 pm and I am nowhere near done frown.gif

The other item I have to include in the report is whether or not there is one "01.pdf" file in each folder.

If there is nothing to report,Yahoo!, but if there isn't a file named '01.pdf" in each of the folders that exist (i.e. do not include folders which we have already identified as missing with your brilliant code), I need to know which folder(s) are missing this key file as it is a manifest of the items within each folder number.

BTW, 01.pdf is called a MCR, which is short for "Mandatory Course Report", a kind of manifest for the .pdfs in that folder.

I am also trying to get ready some other questions for your form and function... I cannot seem to get it working properly in my master database, and I could just import everything into your version, but I would like to see what I am doing wrong...

And question 1, what is the small grey command button used for up in the top left corner of the form ???

2. What is this line for... what would cause this error? [code]]Err.Raise 13 'Test Error[/code

Thank you again...you are truly a hero !


Cheers,

BILL
This post has been edited by Oblio: Sep 12 2017, 12:58 PM
Go to the top of the page
 
ADezii
post Sep 12 2017, 03:36 PM
Post#28



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
And question 1, what is the small grey command button used for up in the top left corner of the form ???

Used for my Debugging purposes, generates the appropriate BBCodes for Tables & Queries.

QUOTE
What is this line for... what would cause this error? - Err.Raise 13 'Test Error

I intentionally create this Error. It tests the Error Handler in fFindMissingFolders(). If any Error is encountered within this Function, I wanted it to return a NULL Value.
The Calling Routine converts this NULL into an Empty String via Nz(), which is why I test for NOT "", as below:
CODE
Dim strMsg As String

strMsg = vbCrLf & vbCrLf & "P.S. - Not really clear on the MAXIMUM Folder aspect, but it should " & _
         "not be any problem implementing it within the existing Logic."

If Nz(fFindMissingFolders()) <> "" Then
  MsgBox "Missing Folders: " & fFindMissingFolders() & strMsg, _
          vbInformation, "Missing Folders Demo"
End If

CODE
but if there isn't a file named '01.pdf" in each of the folders that exist (i.e. do not include folders which we have already identified as missing with your brilliant code), I need to know which folder(s) are missing this key file as it is a manifest of the items within each folder number.

Try:
SQL
SELECT DISTINCT t_Directories.Directories_ID, t_Directories.FPath
FROM t_Directories WHERE (((DCount("*","t_files","t_files.[Directories_ID]=" & [Directories_ID] & " AND t_files.[FName]='01.pdf'"))=0))
ORDER BY t_Directories.FPath;

OUTPUT:
Directories_IDFPath
268M:\Processed\09-08-2017\
277M:\Processed\09-08-2017\15\Z\
279M:\Processed\09-08-2017\16\Z\
283M:\Processed\09-08-2017\19\Z\
285M:\Processed\09-08-2017\2\Z\
287M:\Processed\09-08-2017\20\Z\
294M:\Processed\09-08-2017\26\Z\
296M:\Processed\09-08-2017\27\Z\
298M:\Processed\09-08-2017\28\Z\
300M:\Processed\09-08-2017\29\Z\
303M:\Processed\09-08-2017\30\Z\
305M:\Processed\09-08-2017\31\Z\
308M:\Processed\09-08-2017\33\Z\
312M:\Processed\09-08-2017\36\Z\
321M:\Processed\09-08-2017\5\Z\
323M:\Processed\09-08-2017\6\Z\


This post has been edited by ADezii: Sep 12 2017, 04:29 PM
Go to the top of the page
 
Oblio
post Sep 12 2017, 10:32 PM
Post#29



Posts: 126
Joined: 5-February 15



Thanks so much for explaining that...

My only problem now seems to be handling the there is no folder results which is throwing an error:

Invalid Procedure Call or Argument

I added a MsgBox to say there are no missing folders which does appear after I hit OK on the Invalid Procdure Call or Argument message box and that does come up, but can you help me figure out the Invalid Procedure call issue ?

Hope all is well with you !

Bill
Go to the top of the page
 
ADezii
post Sep 13 2017, 09:04 AM
Post#30



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


The Error generated from No Missing Folders should be easy enough to avoid. When I can get to my PC, I'll see what I can come up with and Post the Code change.
Go to the top of the page
 
Oblio
post Sep 13 2017, 10:24 AM
Post#31



Posts: 126
Joined: 5-February 15



Thank you SIR smile.gif
Go to the top of the page
 
ADezii
post Sep 13 2017, 11:15 AM
Post#32



Posts: 1,788
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
can you help me figure out the Invalid Procedure call issue ?

This Error occurs because when there are no Missing Folders (GAPs), strBuild within the fFindMissingFolders() Function equals a Zero Length String ("") with the resulting Function Return failing, as below:
CODE
fFindMissingFolders = Left$(strBuild, Len(strBuild) - 2)

  1. I am posting new Code below that should handle this Error. It was actually my mistake, for I never allowed for the contingency that there would NOT be any Missing Folders (GAPs). Replace ALL the Code, including the Error Handler, in the fFindMissingFolders() Function, with the following:
    CODE
    Public Function fFindMissingFolders() As Variant
    On Error GoTo Err_fFindMissingFolders
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstClone As DAO.Recordset
    Dim intCtr As Integer
    Dim strBuild As String

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("q_Distinct_Folders", dbOpenSnapshot)
    Set rstClone = rst.Clone

    rst.MoveFirst       'Move to First Record
    rstClone.Move 1     'Move to 2nd Record

    With rst
      Do While Not rstClone.EOF
        If (rstClone![LastFolderIs] - ![LastFolderIs]) > 1 Then
          For intCtr = 1 To (rstClone![LastFolderIs] - ![LastFolderIs])
            If intCtr + ![LastFolderIs] <> rstClone![LastFolderIs] Then
              strBuild = strBuild & CStr(intCtr + ![LastFolderIs]) & ", "
            End If
          Next
        End If
          .MoveNext
           rstClone.MoveNext
      Loop
    End With

    rst.Close
    rstClone.Close
    Set rst = Nothing
    Set rstClone = Nothing

    If strBuild = "" Then       'No Missing Folders
      MsgBox "No Missing Folders were found", vbExclamation, "No GAPs"
        fFindMissingFolders = Null
          Exit Function
    End If

    fFindMissingFolders = Left$(strBuild, Len(strBuild) - 2)

    Exit_fFindMissingFolders:
      Exit Function

    Err_fFindMissingFolders:
      MsgBox Err.Description, vbExclamation, "Error in fFindMissingFolders()"
        fFindMissingFolders = Null
          Resume Exit_fFindMissingFolders
    End Function
  2. Notice how we now check the Value of strBuild.
  3. Still on the 1:30 P.M. deadline? pullhair.gif

This post has been edited by ADezii: Sep 13 2017, 11:16 AM
Go to the top of the page
 
Oblio
post Sep 13 2017, 04:53 PM
Post#33



Posts: 126
Joined: 5-February 15



Fudge fudge fudge... made it through this round but Friday's the big show dunce.gif oh no

OK, I will give this a try for certain....I am sorry it caused you more work... I hope things are going well for you !

It is really kind to help and just wanted to say thanks so much as you have made my world so less stressful and I can use the break😕 but then I remember how lucky I am to have a home and food and a million other things...I wish we could reach out and help all the people hurt by these storms...

So thank you again for helping me....you are an awesome human being!

Have a good night and my prayers are with you and your family and friends...

Bill

Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2017 - 04:44 PM