Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    14

    Unanswered: Need help with generating a report

    Hello all,

    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.

    pNum = 1
    dNum = DwellingNumber
    Me.RPC.SetFocus
    Rec = Me.RPC.Text

    pth = "r:\" & "P0" & Left(Rec, 2) & "\"
    pFile = "00" & Rec & "_0" & dNum & "_0" & pNum & ".jpg"
    strFile = Dir(pth & pFile)

    If strFile <> "" Then

    Me.Ph.Picture = pth & pFile
    Me.PicNum.SetFocus
    Me.PicNum.Text = pNum
    End If

    If IsNull(Me.PicNum) Then
    MsgBox "No pictures present for that RPC."
    End If

    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.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    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;;;;;

    hope this helps.
    www CahabaData com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •