Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tough Problem. Need expert help. (Access 97)

    If you figure this out consider yourself extremely awesome!! If you need more info. please let me know.

    Below is part of the code used to import a spread sheet into access db. What this part of the code does is check the WhereCriteria statement for Null values. If a value is not null then it deletes it from the table called ImportTemp. What I would like to have this part of the code do as well is to check another table called IssuedTo (This table has the same fields as in the WhereCriteria)for the same values as the WhereCriteria Voucher# and then delete the record with this number from the IssuedTo Table. Not sure how to do this. I inherited this db from someone else and not sure what to do. Thanks for the help.


    WhereCriteria = "([Voucher#] Is Not Null Or [Voucher#]<>'') AND ([Date Assigned] Is Not Null Or [Date Assigned] <> '') AND ([First Name] Is Not Null Or [First Name]<>'') AND ([Last Name] Is Not Null Or [Last Name]<>'') AND ([Reason] Is Not Null Or [Reason]<>'') AND ([Center] Is Not Null Or [Center] <> '') AND ([Type] Is Not Null Or [Type]<>'') AND ([Test Date] Is Not Null Or [Test Date] <> '') AND ([Assigned By] Is Not Null Or [Assigned By]<>'')"

    DoCmd.RunSQL "INSERT INTO " & " SELECT " & ImportTemp & ".* FROM " & ImportTemp & " WHERE " & WhereCriteria
    End If
    DoCmd.RunSQL "DELETE * FROM " & ImportTemp & " WHERE " & WhereCriteria


    DoCmd.SetWarnings False 'Turn Access warning messages back on

    MsgBox (R - 13 & " Vouchers Imported From Spreadsheet " & Chr(13) & Chr(10) & TotalGood & " Vouchers Added to " & Program & Chr(13) & Chr(10) & TotalRejects & " Vouchers Require Resolution" & Chr(13) & Chr(10) & TotalDupes & " Duplicate Vouchers not loaded" & Chr(13) & Chr(10) & TotalBlank & " Blank Vouchers not loaded")

    Exit_Import:

    tblResolve.Close
    tblVchs.Close
    tblImpTmp.Close
    Set wsp = Nothing
    Set dbv = Nothing
    DoCmd.Close acForm, "Import Block Vouchers"
    Exit Function

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tough Problem. Need expert help. (Access 97)

    In the code, ImportTemp appears to be a variable rather than the actual name of the table. Are you sure it isn't the IssuedTo table already?

    You do realize that this code is checking for not null OR not a zero-length string, right? It isn't checking for strings containing nothing but spaces, and shouldn't you really be using AND instead of OR? I would think you want to check for things like:

    <pre><font color=red>([Voucher#] Is Not Null AND [Voucher#]<>'')</font color=red></pre>


    This line should be throwing an error because its syntax is wrong:

    <pre>DoCmd.RunSQL "INSERT INTO " & " SELECT " & ImportTemp _
    & ".* FROM " & ImportTemp & " WHERE " & WhereCriteria</pre>


    It's trying to insert a record somewhere but there isn't any table name for it to insert into. You need a table name between "INSERT INTO" and "SELECT".
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tough Problem. Need expert help. (Access 97)

    Your right ImportTemp is a Variant for a table named ImportTemp.

Posting Permissions

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