Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: Can not filter imported records

    Hey everyone,

    My names Jake and I have a problem that I am cannot figure out. I browsed these forums and didn't see a solution to it so I figured I would ask.

    My problem:

    I import a CSV file into a temporary table called tblDataTemp

    Then I use SQL to match up the fields from the temporary table to my tblData.

    All of the information comes in fine, but when I apply a filter nothing shows up. When I change any field of a record and save it it then shows up in the filter. If you need to see the code I can clean it up. Any help would be appreciated. Thanks.

    -Jake

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Jake - welcome to the forum.

    Yup - some code would be great. The db zipped up containing sample data would be top quality too

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    I will work on getting a sample db, in the mean time here is the import code:

    Code:
                  DoCmd.SetWarnings False
                  DoCmd.TransferText acImportDelim, , "tblDataTemp", strFilePath, True
                  DoCmd.OpenTable "tblDataTemp", acViewNormal, acEdit
                  DoCmd.GoToRecord acDataTable, "tblDataTemp", acLast
                  DoCmd.RunCommand (acCmdDeleteRecord)
                  DoCmd.Close acTable, "tblDataTemp", acSaveYes
                  DoCmd.RunSQL "Insert Into tblData ([LastName], [FirstName1], [Address], [City], [State], [ZipCode], [HomePhone], [Initials1], [CRIMailingDate], [DateUpdated]) " & _
                                "Select mid([Name],InStr(1,[Name],' ')+1, len([Name])), left([Name], InStr(1,[Name],' '))," & _
                                "[Address], [City], [State], [Zip], [Area Cd] + [Phone], DLookup('Initials', 'tblPersonelInfo'), Date(), Date()" & _
                                "From tblDataTemp"
                  DoCmd.DeleteObject acTable, "tblDataTemp"
                  DoCmd.SetWarnings True
    strFilePath is the path to the .CSV
    The csv has the columns: fullname, address, city, state, zip, area code, and phone.
    I delete the last record of tblDataTemp because theres a EOF record in the CSV file.
    The dlookup takes the first initials from tblPersonelInfo (yah, should have been two n's but I didnt bother changing the code when I noticed that, hah). The filter looks like this:
    Code:
    SELECT tblData.Initials2, *
    FROM tblData
    WHERE (((tblData.Initials2) Is Null) AND ((tblData.CRIMailingDate)<=Date())) OR (((tblData.CRIFinal)<=Date()) AND ((tblData.Initials3) Is Null))
    ORDER BY tblData.LastName;
    The dates are equal and there is nothing stored in Initials2, so the record should show up when using this filter. Only after you change any other field in the record will it show up in the filter.

    I will work on getting a test db.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Jake

    yep - a stripped down sample db with even only a couple of dummy records that illustrate the problem would be great.

    Couple of quickies:
    Do Initials2 or CRIMailingDate or CRIFinal or Initials3 contain any default values?

    What happens if you run the below:
    Code:
     SELECT *
    FROM tblData
    WHERE (NZ(Initials2, '') = '' OR CRIMailingDate IS NULL) AND (CRIFinal IS NULL OR NZ(Initials3, '') = '')
    If nothing then try:
    Run the below SQL (either through Docmd.RunSQL or create a DDL SQL specific Query
    Code:
     ALTER TABLE tblData ADD BatchUpdated Bit
    Change your import code to
    Code:
    DoCmd.RunSQL "Insert Into tblData ([LastName], [FirstName1], [Address], [City], [State], [ZipCode], [HomePhone], [Initials1], [CRIMailingDate], [DateUpdated], BatchUpdated ) " & _
    "Select mid([Name],InStr(1,[Name],' ')+1, len([Name])), left([Name], InStr(1,[Name],' '))," & _
    "[Address], [City], [State], [Zip], [Area Cd] + [Phone], DLookup('Initials', 'tblPersonelInfo'), Date(), Date(), -1" & _
    "From tblDataTemp"
    and your filer to
    Code:
     SELECT tblData.Initials2, *
    FROM tblData
    WHERE BatchUpdated = True
    ORDER BY tblData.LastName;
    and see if your records are returned.

    Sample db would be super though
    Last edited by pootle flump; 12-16-05 at 05:07.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2005
    Posts
    5
    It was the sql filter that wast he problem. I changed it to
    Code:
    SELECT *
    FROM tblData
    WHERE (NZ(tblData.Initials2,'')='' And tblData.CRIMailingDate<=Date()) Or (tblData.CRIFinal<=Date() And NZ(tblData.Initials3,'')='')
    ORDER BY tblDAta.LastName;
    and now it works perfectly, thanks for the idea.

Posting Permissions

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