I am not an Access and/or query writing genius so some assistance would be most appreciated. Here is what I have, an sql table with fields from our primary database. One of these fields is RPC, which is basically an account number. Some of our accounts have pictures associated with them, located in folders on another server. The file structure here is, if an account of 11111 has a picture, that picture is located at R:\PO11\0011111_01_01.jpg. (R is the drive, P011 is the folder and then the picture name). Each P0# corresponds to the first two numbers of the account number and each of these folders has roughly 1000 account numbers/pictures in it. We have @ 24,000 account numbers. What I am trying to do is decipher how many of our account numbers do not have a picture associated with it, so really my question is what would be the best method to achieve this either with vb or a query. Just as a side, I have made a picture viewer that can look up the corresponding picture based on the account #...
'Load pictures into form and intialize picture variables.
Me.Ph.Picture = pth & pFile
Me.PicNum.Text = pNum
If IsNull(Me.PicNum) Then
MsgBox "No pictures present for that RPC."
Can I write a vb script or query that can look through the sql list of account numbers and then compare it to the folders where the RPC/account is contained within the picture file name, and then generate a report that lists only the numbers that have no associated pictures? Perhaps, set up several tables that contain the breakdown of accounts, in my example, the picture for account 11111 is listed in folder P011, then run a query that compares the 11### account numbers to the RPC numbers embedded in the picture names? Long post, but I am trying to explain my situation as thoroughly as possible. Thanks in advance for the help.
well at first you seem to be constructing the file path & file name on the fly based on a formula.
but then I see you have: IsNull(Me.PicNum) which means there is a value coming from somewhere, hopefully a field in the table.
so I'm not sure as to what is what exactly - - so in general terms:
if you have the file name in a field - - then it would be very simple to simply query for the : Is Null and get all those records without a picture.
if you don't have that file name in a table/field and are making a formula on the fly; someone made a decision, I won't say flawed, but a deliberate decision that can only assume that the pic file exists....however I don't know how one checks for IsNull(me.PicNum) in such a case......so probably this scenario is not exactly what is happening;;;;;