Return No Null Rows 2

[perrymans]

I have an Append Query that is bringing in info from an Excel spreadsheet.

It is measurement information, and of the 473 rows, only 75 may actually contain information.

So I am importing the spreadsheet to a table, and now want to Append only those records that have measurement information in them.

The problem is the left hand column contains the name/ identifier for each row, even if there is no measure information.

Also, the measure info can be in any one of the 15 fields, and I need a row with any information.

The only solution I know of is to us "Is Not Null ... OR ... "Is Not Null ..." for each field, which seems excessive.

I just want to look through Field4 - Field26 and only return the rows where there is information.

Any ideas? thanks. Sean. (sample data below)

IDField  Field4   Field5  Field6
8677     1
6557     2        $4.00   $8.00
4322     1                $3.33
7855

I need the first three rows appended, but not the '7855' row.

 

[Remou]

How about concatenating the relevant fields?

[tt]Where Len(Trim(Field4 & ... & Field26)) >0[/tt]

 

[dhookom]

I would use a criteria like:

(Field4+Field5+Field6+field7+...+Field26) Is Not Null

If any field is null, the entire expression will be null.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

dhookom,
Is that not the opposite of what perrymans wants, in that it will only return rows that have been completed in full, rather than any row where there is any information?

I just want to look through Field4 - Field26 and only return the rows where there is information.

 

[dhookom]

My bad. Don't know where my head was at. Probably trying to think about normalizing and losing my senses.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

I can't concatenate the fields becuase it is an append query, I am trying to put it into another table as part of an import.

Is there really now way to return only the rows that aren't blank?

I guess if it is too hard, I can do it manually with sorting and deleting.

Thanks. Sean.

 

[Remou]

I think you may have missed my point:

[tt]Insert * Into tblA Select * From tblB Where Len(Trim(Field4 & ... & Field26) >0[/tt]

Would that not suit?

 

[perrymans]

That is working wel thanks... except I don't think Access likes the large number of fields at play here.

Here is the large SQL:

NSERT INTO Tbl_Measure ( OPFAC, A_1, A_2, A_5, A_3, A_12, A_F, A_InhrtCalc, A_UserIP, A_UserCalc, A_BTErrorValue, A_BlkBCostDiff, A_BlkGCostDiff, B_Ration, B_Allow, B_25, B_InhrtCalc, B_UserIP, B_UserCalc, B_BlkBCostDiff, D_28, D_30, D_31, D_32, D_Rcpt, D_InhrtCalc, D_UserIP, D_UserCalc, D_BlkDCostDiff )
SELECT [Jan# 06].Field3, [Jan# 06].Field6, [Jan# 06].Field7, [Jan# 06].Field8, [Jan# 06].Field9, [Jan# 06].Field10, [Jan# 06].Field11, [Jan# 06].Field12, [Jan# 06].Field13, [Jan# 06].Field14, [Jan# 06].Field15, [Jan# 06].Field16, [Jan# 06].Field17, [Jan# 06].Field19, [Jan# 06].Field20, [Jan# 06].Field21, [Jan# 06].Field22, [Jan# 06].Field23, [Jan# 06].Field24, [Jan# 06].Field25, [Jan# 06].Field27, [Jan# 06].Field28, [Jan# 06].Field29, [Jan# 06].Field30, [Jan# 06].Field31, [Jan# 06].Field32, [Jan# 06].Field33, [Jan# 06].Field34, [Jan# 06].Field35
FROM [Jan# 06]
WHERE ((Len("Field3" & "Field6" & "Field7" & "Field8", "Field9" & "Field10" & "Field11" & "Field12" & "Field13" & "Field14" & "Field15" & "Field16" & "Field17" & "Field19" & "Field20" & "Field21" & "Field22" & "Field23" & "Field24" & "Field25" & "Field27" & "Field28" & "Field29" & "Field30" & "Field31" & "Field32" & "Field33" & "Field34" & "Field35")>"0"));

Any way to simplfy so I don't get the "Wrong number of arguments" error because of how many fields are at play in the WHERE clause (it works if I reduce the number of fields).

Thanks. Sean.

 

[dhookom]

You missed Remou's suggestion. You should have []s around field names and remove the "s from around the 0:

WHERE Len([Field3] & [Field6] & [Field7] & [Field8], [Field9] & [Field10] & [Field11] & [Field12] & [Field13] & [Field14] & [Field15] & [Field16] & [Field17] & [Field19] & [Field20] & [Field21] & [Field22] & [Field23] & [Field24] & [Field25] & [Field27] & [Field28] & [Field29] & [Field30] & [Field31] & [Field32] & [Field33] & [Field34] & [Field35])>0;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

Alright, it is working now!

Thanks again fellas!

Sean.