Zero length fields on Append Query

[Zeroanarchy]

I am hoping someone might be able to help me with this I have been pulling my hair out for days.

I am getting the following error on running my append query:
and 2 record(s)due to validation rule violations.

How it works, it imports one table into another and if the fields are blank then it assigns a value to the blank field, now when I run this under a select query it looks great but for some reason I get an error on the append.

Here is the code: and if anyone has fives min I would be happy to send you a copy to look at.

INSERT INTO Regestration ( parkcode, drainageline, roadsideinfestation, Loginname, Northings, Eastings, infestedarea, riskrating, vegetationtype, soil, monthtobetreated, commonlocationname, dateregistered, speconstraints, specialconsiderations, Logindate, lifecycle, strata, Yeartobetreated )
SELECT IIf(IsNull([hhreg].[parkcode]) Or ([hhreg].[parkcode]="&quot,0,[hhreg].[parkcode]) AS Expr1, HHreg.drainageline, HHreg.roadsideinfestation, "HandHeld" AS Expr15, Left([Northings],8) AS Expr19, Left([Eastings],8) AS Expr20, IIf(IsNull([hhreg].[infestedarea]) Or ([hhreg].[infestedarea]="&quot,0,[hhreg].[infestedarea]) AS Expr3, IIf(IsNull([hhreg].[riskrating]) Or ([hhreg].[riskrating]="&quot,"NoneSet",[hhreg].[riskrating]) AS Expr4, IIf(IsNull([hhreg].[vegetationtype]) Or ([hhreg].[vegetationtype]="&quot,"NoneSet",[hhreg].[vegetationtype]) AS Expr5, IIf(IsNull([hhreg].[soil]) Or ([hhreg].[soil]="&quot,"NonetSet",[hhreg].[soil]) AS Expr6, IIf(IsNull([hhreg].[monthtobetreated]) Or ([hhreg].[monthtobetreated]="&quot,"January",[hhreg].[monthtobetreated]) AS Expr7, IIf(IsNull([hhreg].[commonlocationname]) Or ([hhreg].[commonlocationname]="&quot,"NoneSet",[hhreg].[commonlocationname]) AS Expr9, IIf(IsNull([hhreg].[dateregistered]) Or ([hhreg].[dateregistered]="&quot,Date(),[hhreg].[dateregistered]) AS Expr10, IIf(IsNull([hhreg].[speconstraints]) Or ([hhreg].[speconstraints]="&quot,"None",[hhreg].[speconstraints]) AS Expr13, IIf(IsNull([hhreg].[specialconsiderations]) Or ([hhreg].[specialconsiderations]="&quot,"None",[hhreg].[specialconsiderations]) AS Expr14, IIf(IsNull([hhreg].[logindate]) Or ([hhreg].[logindate]="&quot,Now(),[hhreg].[logindate]) AS Expr16, IIf(IsNull([hhreg].[lifecycle]) Or ([hhreg].[lifecycle]="&quot,"NoneSet",[hhreg].[lifecycle]) AS Expr17, IIf(IsNull([hhreg].[strata]) Or ([hhreg].[strata]="&quot,"NoneSet",[hhreg].[strata]) AS Expr18, IIf(IsNull([hhreg].[yeartobetreated]) Or ([hhreg].[yeartobetreated]="&quot,"2003",[hhreg].[yeartobetreated]) AS Expr8
FROM HHreg;

Zeroanarchy Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[oharab]

Instead of using
IIf(IsNull([hhreg].[lifecycle]) Or ([hhreg].[lifecycle]="&quot try
IIf(len([hhreg].[lifecycle]) =0)

I have noticed isnull can be a little unpredictable at times.

HTH

BEN ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[Zeroanarchy]

will that work with both text and numeric?

Thanks

Zeroanarchy Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Zeroanarchy]

Well I can not believe this but this method does not work wither I am getting the same error:"and 2 record(s)due to validation rule violations."

Code:

INSERT INTO Regestration ( parkcode, drainageline, roadsideinfestation, Loginname, Northings, Eastings, infestedarea, riskrating, vegetationtype, soil, monthtobetreated, commonlocationname, dateregistered, speconstraints, specialconsiderations, lifecycle, strata, Yeartobetreated, Logindate )
SELECT IIf(Len([hhreg].[parkcode])=0,0,[hhreg].[parkcode]) AS Expr1, HHreg.drainageline, HHreg.roadsideinfestation, "HandHeld" AS Expr15, Left([Northings],8) AS Expr19, Left([Eastings],8) AS Expr20, IIf(Len([hhreg].[infestedarea])=0,0,[hhreg].[infestedarea]) AS Expr3, IIf(Len([hhreg].[riskrating])=0,"NoneSet",[hhreg].[riskrating]) AS Expr4, IIf(Len([hhreg].[vegetationtype])=0,"NoneSet",[hhreg].[vegetationtype]) AS Expr5, IIf(Len([hhreg].[soil])=0,"NonetSet",[hhreg].[soil]) AS Expr6, IIf(Len([hhreg].[monthtobetreated])=0,"January",[hhreg].[monthtobetreated]) AS Expr7, IIf(Len([hhreg].[commonlocationname])=0,"NoneSet",[hhreg].[commonlocationname]) AS Expr9, IIf(Len([hhreg].[dateregistered])=0,Date(),[hhreg].[dateregistered]) AS Expr10, IIf(Len([hhreg].[speconstraints])=0,"None",[hhreg].[speconstraints]) AS Expr13, IIf(Len([hhreg].[specialconsiderations])=0,"None",[hhreg].[specialconsiderations]) AS Expr14, IIf(Len([hhreg].[lifecycle])=0,"NoneSet",[hhreg].[lifecycle]) AS Expr17, IIf(Len([hhreg].[strata])=0,"NoneSet",[hhreg].[strata]) AS Expr18, IIf(Len([hhreg].[yeartobetreated])=0,"2003",[hhreg].[yeartobetreated]) AS Expr8, Date() AS expr21
FROM HHreg;

Zeroanarchy Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[DerekAOE]

You may have to apply different tests depending on the data type of the source field. I.e. for string fields apply your 'if=""' test, but for numeric fields I'm not sure that the test will detect the empty value.

I would tend to use the Nz() function and apply the test after. For string values...

iif(nz(stringfield,"&quot = "", Value for empty, value for not empty)

for numeric fields ...

iif(nz(numberfield,0) = 0, Value for empty, value for not empty)

Also ensure that the values you are adding to your table are the correct data type i.e. don't try to write 'NotSet' into a numeric field.

 

[Zeroanarchy]

Here is where it gets interesting, I turned my Append into a Select Query to Find that all the fields where populated with the correct data. when it is converted back into a Append the error still remains. I have checked the fields that are being append only one is numeric the rest are text. (numeric =infestedarea)

I am hoping someone has an answer, this has got me pulling my hair out.

Zeroanarchy Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.