Access 97 - 2002 Conversion

R

Reg Booth

Dear Group,


I am a first time poster who is having a problem converting an Access97
Applicaiton to Access2002. It all seems to work
except for this one item. I have an append query query which is executed
through code but does not append data.

--- Code excerpt ----

Dim lobjDb As Database
Dim myquery As DAO.QueryDef

Have Tried ---


lobjDb.Execute "INSERT INTO tblTempCBChanges (CB, [Line], Action,
OldProduct, OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth,
OldProdLength, OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness,
OldPlacement, OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT
tblCB.CB, tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE tblCB.EffectiveDate <= #" & Date & "# AND
(tblCB.ExpirationDate = Null OR tblCB.ExpirationDate >= #" & Date & "#) AND
tblEyeCB.CB = Null", dbFailOnError

---- And have tried ----

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"
lobjDb.Execute sql

----and have tried ---

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

lobjDb.Execute "qapItemsNotInEyeCB"


-----and have tried ------

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

DoCmd.OpenQuery "qapItemsNotInEyeCB"




If I Open the database window and double click on the query - It does not
append records.
If I open the Query in Design Mode and select "DataSheet View" - I see the
records to be appended.
If I open the Query in Design Mode and select "RUN" --- It will append the
data! If I then Close the query and Select "Save" - I
can double click on the query and it will append the data.

I am Referencing
Visual Basic for Applications, Microsoft Access 10.0 Object Library,
Microsoft DAO 3.6 Object Library,
OLE automation, Microsoft Excel 10.0 Object Library, and Microsoft Office XP
Web Components.

Any Assistance in saving what little hair I have left would be appreciated.

TIA

Reg
 
V

Van T. Dinh

I am surprised that it worked in A97: you cannot use the comparison operator
= with Null since the *expression*

(A Null value) = Null

does not result in True or False. It evaluates to Null which is interpreted
as not True.

Try change all " = Null" to " Is Null".

--
HTH
Van T. Dinh
MVP (Access)




Reg Booth said:
Dear Group,


I am a first time poster who is having a problem converting an Access97
Applicaiton to Access2002. It all seems to work
except for this one item. I have an append query query which is executed
through code but does not append data.

--- Code excerpt ----

Dim lobjDb As Database
Dim myquery As DAO.QueryDef

Have Tried ---


lobjDb.Execute "INSERT INTO tblTempCBChanges (CB, [Line], Action,
OldProduct, OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth,
OldProdLength, OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness,
OldPlacement, OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT
tblCB.CB, tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE tblCB.EffectiveDate <= #" & Date & "# AND
(tblCB.ExpirationDate = Null OR tblCB.ExpirationDate >= #" & Date & "#) AND
tblEyeCB.CB = Null", dbFailOnError

---- And have tried ----

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"
lobjDb.Execute sql

----and have tried ---

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

lobjDb.Execute "qapItemsNotInEyeCB"


-----and have tried ------

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

DoCmd.OpenQuery "qapItemsNotInEyeCB"




If I Open the database window and double click on the query - It does not
append records.
If I open the Query in Design Mode and select "DataSheet View" - I see the
records to be appended.
If I open the Query in Design Mode and select "RUN" --- It will append the
data! If I then Close the query and Select "Save" - I
can double click on the query and it will append the data.

I am Referencing
Visual Basic for Applications, Microsoft Access 10.0 Object Library,
Microsoft DAO 3.6 Object Library,
OLE automation, Microsoft Excel 10.0 Object Library, and Microsoft Office XP
Web Components.

Any Assistance in saving what little hair I have left would be appreciated.

TIA

Reg
Click to expand...
 
R

Reg Booth

Thanks Van - Changing the "= Null" to "Is Null" fixed my problem. I didn't
check the SQL Code because it was working fine in the A97 Version.

Much Apprecicated

Reg


Van T. Dinh said:
I am surprised that it worked in A97: you cannot use the comparison operator
= with Null since the *expression*

(A Null value) = Null

does not result in True or False. It evaluates to Null which is interpreted
as not True.

Try change all " = Null" to " Is Null".

--
HTH
Van T. Dinh
MVP (Access)




Reg Booth said:
Dear Group,


I am a first time poster who is having a problem converting an Access97
Applicaiton to Access2002. It all seems to work
except for this one item. I have an append query query which is executed
through code but does not append data.

--- Code excerpt ----

Dim lobjDb As Database
Dim myquery As DAO.QueryDef

Have Tried ---


lobjDb.Execute "INSERT INTO tblTempCBChanges (CB, [Line], Action,
OldProduct, OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth,
OldProdLength, OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness,
OldPlacement, OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT
tblCB.CB, tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE tblCB.EffectiveDate <= #" & Date & "# AND
(tblCB.ExpirationDate = Null OR tblCB.ExpirationDate >= #" & Date & "#) AND
tblEyeCB.CB = Null", dbFailOnError

---- And have tried ----

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"
lobjDb.Execute sql

----and have tried ---

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

lobjDb.Execute "qapItemsNotInEyeCB"


-----and have tried ------

sql = "INSERT INTO tblTempCBChanges(CB, [Line], Action, OldProduct,
OldQuality, OldProdWeight, OldSquareMeters, OldProdWidth, OldProdLength,
OldPKX, OldAngleDir, OldAngleMin, OldAngleSec, OldThickness, OldPlacement,
OldDensity, OldBands, OldPackUOM, OldUnitWeight) SELECT tblCB.CB,
tblCB.Line, 'DEL', tblCB.Product, tblCB.Quality, tblCB.ProdWeight,
tblCB.SquareMeters, tblCB.ProdWidth, tblCB.ProdLength, tblCB.PKX,
tblCB.AngleDir, tblCB.AngleMin, tblCB.AngleSec, tblCB.Thickness,
tblCB.Placement, tblCB.Density, tblCB.Bands, tblCB.PackUOM, tblCB.UnitWeight
FROM tblCB LEFT JOIN tblEyeCB ON tblCB.CB = tblEyeCB.CB AND tblCB.Line =
tblEyeCB.Line WHERE trim(tblCB.EffectiveDate) <= #" & Date & "# AND
(trim(tblCB.ExpirationDate) = Null OR trim(tblCB.ExpirationDate) >= #" &
Date & "#) AND trim(tblEyeCB.CB) = Null"

Set myquery = lobjDb.QueryDefs("qapItemsNotInEyeCB")
myquery.sql = sql
myquery.Close

DoCmd.OpenQuery "qapItemsNotInEyeCB"




If I Open the database window and double click on the query - It does not
append records.
If I open the Query in Design Mode and select "DataSheet View" - I see the
records to be appended.
If I open the Query in Design Mode and select "RUN" --- It will append the
data! If I then Close the query and Select "Save" - I
can double click on the query and it will append the data.

I am Referencing
Visual Basic for Applications, Microsoft Access 10.0 Object Library,
Microsoft DAO 3.6 Object Library,
OLE automation, Microsoft Excel 10.0 Object Library, and Microsoft
Click to expand...
Office
XP
Web Components.

Any Assistance in saving what little hair I have left would be appreciated.

TIA

Reg
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

I don't know what the syntax rules are for "IS NULL" and "= Null"
in standard SQL, but Jet SQL will evaluate "= Null", and seems
to use the same rules as VBA:
NULL == boolean AND NULL
NULL == False or NULL
True == True or NULL
So the criteria shown in the example (c or (b and a)) would be
expected to return records for some conditions and not for others.

Since "=Null" is normally converted to "is null" when you open a query
in design view, the process of changing between an Append and a Select
query will be modifying the SQL.

(david)
 
D

david epsom dot com dot au

I don't know what the syntax rules are for "IS NULL" and "= Null"
in standard SQL, but Jet SQL will evaluate "= Null", and seems
to use the same rules as VBA:
NULL == boolean AND NULL
NULL == False or NULL
True == True or NULL
So the criteria shown in the example ( c or (b and a)) would be expected
to return records for some conditions.

Since "=Null" is normally converted to "is null" when you open a query
in design view, the process of changing between an Append and a Select
query may be modifying the SQL.
 
Top