Results 1 to 7 of 7
  1. #1

    Help! Deadline coming soon!

    I am attempting to do an insert Query in VBA code. I need to parse the data by three different criteria though ( a ID number, a Date, and a less than a greater than a certain length). I first tried to insert entire records with the* field. (When I tried to include each record individually the string was to long. I was able to do it in VBA code but not in the query builder. But when I included the third criteria I kept getting a "Divide By Zero" error. Does anyone know of another way around this dilemma. Help would be greatly appreciated!!!!!

    SQLstr2 = "INSERT INTO TblDefunctFootages" _
    & " SELECT ThicknessReadings.*" _
    & " FROM ThicknessReadings" _
    & " WHERE [ThicknessReadings].[Footage]>" & NewLength _
    & " and ThicknessReadings.EquipmentId =" & EquipID _
    & " and ThicknessReadings.InspectionDate =" & strLatestInspDate & ";"

    Oh Yeah! I am using Access 97

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    The Netherlands
    Thanked 0 Times in 0 Posts

    Re: Help! Deadline coming soon!

    If you use the INSERT statement like this the fields in both tables should have the same definition. If not, you can get a variaty of nice error messages.
    Consider rewriting the insert statement like this:

    INSERT INTO TblDefunctFootages
    FIELDS (fld1, fld2, fld3,...)
    VALUES (value1, value2, value2, ....);

    You can control the INSERTS better and the code becomes more readable.

    How do you execute your SQL statement?
    If you do it like this, you can trap the errors:

    dim db as database
    set db = currentdb()
    db.execute strSQL, dbFailOnError
    set db = nothing

    The dbFailOnError ensures that an error is raised in case the SQL statement fails!

  3. #3

    Re: Help! Deadline coming soon!

    Help! Deadline coming soon!

    Wow! The amount that you know is truly humbling. Thanks for the extra help as well. Up to now I have been executing my queries like:

    DoCmd.RunSQL (SQLstr2)

    But I will try your way right away here as soon as I get this query working.

    Thanks Bart!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 187 Times in 173 Posts

    Re: Help! Deadline coming soon!

    Hi James,
    I'm guessing that you're passing strLatestInspDate as a string when Access is presumably expecting a date to match against InspectionDate. Have you tried amending your last line to:
    & " and ThicknessReadings.InspectionDate =#" & strLatestInspDate & "#;"
    which should (depending on the format of your date string) allow Access to interpret it correctly.
    Hope that helps.

    Microsoft MVP - Excel

  5. #5

    Re: Help! Deadline coming soon!

    Sorry if I am little slow at this, but am close. This is the SQL from the query builder. When I try to go into design mode to test her I get an "error in INSERT INTO clause.

    INSERT INTO TblDefunctFootages FIELDS (EquipmentId, DueDate, InspectionDate, Footage, LineNumber, Section, Subsection, Affix, Length, Diameter, Material, Descriptor, Type, DrawingNumber)
    FROM ThicknessReadings
    WHERE [ThicknessReadings].[Footage]>4 and ThicknessReadings.EquipmentId =321
    and ThicknessReadings.InspectionDate = #01/01/01#;


  6. #6

    Re: Help! Deadline coming soon!

    Hey Rory
    I'll give that a go!

  7. #7

    Re: Help! Deadline coming soon!

    That did it!
    I just may get some sleep tonight yet!
    Thanks to the great help!
    Thank you guys!

Posting Permissions

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