update query 1

[Zeroanarchy]

I am running an update query an I am getting Violation errors on all lines of data where a blank field exsists.

Laymans example:

1 2 3 4 5 6 7
1 2 3 5 6 7
1 2 3 4 5 6 7
2 3 4 5 6 7

Records 1 & 4 will not be added as a field is blank.

I was always under the impression that unless you had selected REQUIREMENT = YES then it would add the blank record.

I have tried using Nz([WorkStatus])anyone got any other possible solutions.

thanks Again

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

 

[OldTom]

Hi

I think we need some more detail about the fields in the table(s) you are updating and posting the sql of the query will help us to help you

Tom

 

[Zeroanarchy]

Here you go, this is the sql statment that is in use


INSERT INTO Regestration ( parkcode, weedname, percentage, infestedarea, riskrating, vegetationtype, soil, monthtobetreated, Yeartobetreated, commonlocationname, drainageline, roadsideinfestation, [constraints], specialconsiderations, [life cycle], strata, Northings, Eastings, [Login name], [Login date] )
SELECT HHreg.parkcode, HHreg.weedname, HHreg.percentage, HHreg.infestedarea, HHreg.riskrating, HHreg.vegetationtype, HHreg.soil, HHreg.monthtobetreated, HHreg.Yeartobetreated, HHreg.commonlocationname, HHreg.drainageline, HHreg.roadsideinfestation, HHreg.constraints, HHreg.specialconsiderations, HHreg.lifecycle, HHreg.strata, Left([Northings],8) AS Expr3, Left([Eastings],8) AS Expr4, "HandHeld" AS Expr1, HHreg.dateregistered
FROM HHreg;

Thanks for taking a look
Zeroanarchy


I do recall there is an IIF statment that might solve the prob.
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[OldTom]

Here are a few possibilities for you

1) The REQUIRED property of your "Regestration" table fields
2) The ALLOW ZERO LENGTH property if they are Text fields
3) Indexing properties on your table fields
4) Can data from table HHreg contain NULLS.
5) You could use
either
iif(isnull(HHreg.fieldname),"",HHreg.fieldname)
or
iif(isnull(HHreg.fieldname)," ",HHreg.fieldname)
for text fields
and
iif(isnull(HHreg.fieldname),0,HHreg.fieldname)
for numeric fields
if "Regestration" table fields are required / do not allow zero length
Let me know how you get on
Tom

 

[Zeroanarchy]

Thanks OldTom for your help with my question, Ok the outcome well yes you can use

iif(isnull(HHreg.fieldname),"",HHreg.fieldname)
and
iif(isnull(HHreg.fieldname),0,HHreg.fieldname)

But the append query will still not except blank spaces so what you have to do is assign a value between the "" for it to work such as "none" if you leave it as "" you continue to get the import error.

I know it is the weekend and there are not many people around to help so here is a star for your troubles and time

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

 

[OldTom]

Thanks for the star.

Looking back at it I realise I could have (and perhaps should have) redone all the sql for you.

Your point about it not accepting spaces within the SELECT is valid, but a way round it as to use
either
iif(isnull(HHreg.fieldname),"",HHreg.fieldname) & " AS ExprNNN"
or
iif(isnull(HHreg.fieldname)," ",HHreg.fieldname) & " AS ExprNNN"
for text fields

but make sure that your NNN is unique otherwise you will get an error of duplicates on the output fields


Tom

 

[Zeroanarchy]

Thanks for that I will have to try it as I am having probs with two of the Fields. They will not except "None" as the replacment of space. I am also having a prob with date

Expr19: IIf(IsNull[HHreg].[dateregistered],Now())[HHreg].[dateregistered])

Wondering if you could helps

Thanks again

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

 

[OldTom]

At a quick look
IIf(IsNull[HHreg].[dateregistered],Now())[HHreg].[dateregistered])
should be
IIf(IsNull([HHreg].[dateregistered]),Now(),[HHreg].[dateregistered]) 7 " As EXPRNNN"

Tom

 

[Zeroanarchy]

ok having a bit of an issue with this method at present what I am finding is that some blank fields are populating on blank to the new value but in other instinces it remains blank, there is no pattern it tends to be quite random.

Now I have never seen this method used before
iif(isnull(HHreg.fieldname)," ",HHreg.fieldname) & " AS ExprNNN"

And what it actually does is adds the value AS ExprNNN to the current value I believe there is an error in it somewhere, as I ahve not seen this method use I am unable to figure it out.

Here is a copy of the code:


INSERT INTO Regestration ( parkcode, weedname, percentage, infestedarea, riskrating, vegetationtype, soil, monthtobetreated, Yeartobetreated, commonlocationname, drainageline, roadsideinfestation, [constraints], specialconsiderations, [life cycle], strata, Northings, Eastings, [Login name], [Login date] )
SELECT IIf(IsNull([HHreg].[parkcode]),0,[HHreg].[parkcode]) AS Expr2, IIf(IsNull([HHreg].[weedname]),"None Entered",[HHreg].[weedname]) AS Expr5, IIf(IsNull([HHreg].[percentage]),"0%",[HHreg].[percentage]) AS Expr6, IIf(IsNull([HHreg].[infestedarea]),0,[HHreg].[infestedarea]) AS Expr7, IIf(IsNull([HHreg].[riskrating]),"None Set",[HHreg].[riskrating]) AS Expr8, IIf(IsNull([HHreg].[vegetationtype]),"None Set",[HHreg].[vegetationtype]) AS Expr9, IIf(IsNull([HHreg].[soil]),"None Set",[HHreg].[soil]) AS Expr10, IIf(IsNull([HHreg].[monthtobetreated]),"January",[HHreg].[monthtobetreated]) AS Expr20, IIf(IsNull([HHreg].[Yeartobetreated]),"2002",[HHreg].[Yeartobetreated]) AS Expr11, IIf(IsNull([HHreg].[commonlocationname]),"Not Set",[HHreg].[commonlocationname]) AS Expr12, IIf(IsNull([HHreg].[drainageline]),0,[HHreg].[drainageline]) AS Expr13, IIf(IsNull([HHreg].[roadsideinfestation]),0,[HHreg].[roadsideinfestation]) AS Expr14, IIf(IsNull([HHreg].[constraints]),"None",[HHreg].[constraints]) AS Expr15, IIf(IsNull([HHreg].[specialconsiderations]),"None",[HHreg].[specialconsiderations]) AS Expr16, IIf(IsNull([HHreg].[lifecycle]),"None Set",[HHreg].[lifecycle]) AS Expr17, IIf(IsNull([HHreg].[strata]),"None Set",[HHreg].[strata]) AS Expr18, Left([Northings],6) AS Expr3, Left([Eastings],7) AS Expr4, "HandHeld" AS Expr1, IIf(IsNull([HHreg].[dateregistered]),"",[HHreg].[dateregistered]) AS Expr19
FROM HHreg;


thanks
ZeroAnarchy




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

 

[OldTom]

Whoops Big Typo - not concentrating properly

I've only used two fields here, parkcode as numeric and weedname as text

INSERT INTO Regestration ( parkcode, weedname )
SELECT IIf(IsNull([hhreg].[parkcode]),0,[hhreg].[parkcode]) AS Expr1, IIf(IsNull([hhreg].[weedname])," ",[hhreg].[weedname]) AS Expr2
FROM HHreg;

Should be OK now

Tom

 

[Zeroanarchy]

ok OldTom well maybe there is something wrong with my system but I am not getting any results, If you or anyone else has a sec I would like to send a copy of the MDB to have a quick look over.
Thanks
Zeroanarchy

INSERT INTO Regestration ( parkcode, weedname, infestedarea, riskrating, vegetationtype, soil, monthtobetreated, yeartobetreated, commonlocationname, dateregistered, drainageline, roadsideinfestation, speconstraints, specialconsiderations, loginname, logindate, lifecycle, strata, northings, eastings )
SELECT IIf(IsNull([hhreg].[parkcode]),0,[hhreg].[parkcode]) AS Expr1, IIf(IsNull([hhreg].[weedname]),"None Set ",[hhreg].[weedname]) AS Expr2, IIf(IsNull([hhreg].[infestedarea]),0,[hhreg].[infestedarea]) AS Expr3, IIf(IsNull([hhreg].[riskrating]),"None Set",[hhreg].[riskrating]) AS Expr4, IIf(IsNull([hhreg].[vegetationtype]),"Not Set",[hhreg].[vegetationtype]) AS Expr5, IIf(IsNull([hhreg].[soil]),"Not Set",[hhreg].[soil]) AS Expr6, IIf(IsNull([hhreg].[monthtobetreated]),"January",[hhreg].[monthtobetreated]) AS Expr7, IIf(IsNull([hhreg].[yeartobetreated]),"2003",[hhreg].[yeartobetreated]) AS Expr8, IIf(IsNull([hhreg].[commonlocationname]),"Not Set",[hhreg].[commonlocationname]) AS Expr9, IIf(IsNull([hhreg].[dateregistered]),date(),[hhreg].[dateregistered]) AS Expr10, IIf(IsNull([hhreg].[drainageline]),0,[hhreg].[drainageline]) AS Expr11, IIf(IsNull([hhreg].[roadsideinfestation]),0,[hhreg].[roadsideinfestation]) AS Expr12, IIf(IsNull([hhreg].[speconstraints]),"Not set",[hhreg].[speconstraints]) AS Expr13, IIf(IsNull([hhreg].[specialconsiderations]),"Not Set",[hhreg].[specialconsiderations]) AS Expr14, "HandHeld" AS Expr15, IIf(IsNull([hhreg].[logindate]),now(),[hhreg].[logindate]) AS Expr16, IIf(IsNull([hhreg].[lifecycle]),"Not Set",[hhreg].[lifecycle]) AS Expr17, IIf(IsNull([hhreg].[strata]),"Not Set",[hhreg].[strata]) AS Expr18, Left([Northings],8) AS Expr19, Left([Eastings],8) AS Expr20
FROM HHreg;
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[OldTom]

OK send me the mbd then and I'll take a look at it

Tom

tommoran@btlick.com

 

[OldTom]

Sorry - can't type - should be tommoran@btclick.com