Append Query - no records with #Num!

[Adam]

Hi All

I have a table linked to an Excel spreadsheet, which has formulas in
it like If(a2="","",now() ).

When i go to the linked table in Access, it shows <>"#Num!" whenever I
have the formula and there is no data in A2, of course...

But I want to create an append query from this table, and only append
records that do not have #Num! recorded.

Is there a criteria I can input that says <>"#Num!" that will work for
this situation?

Hope someone can help,

Kind regards
Adam

 

[Douglas J. Steele]

The problem is that Now is a numeric field, whereas "" is a text field.

If you have control over the spreadsheet, try changing it to

If(a2="", Null, Now())

You would then use IS NOT NULL as your condition.

 

[Adam]

The problem is that Now is a numeric field, whereas "" is a text field.

If you have control over the spreadsheet, try changing it to

If(a2="", Null, Now())

You would then use IS NOT NULL as your condition.

Brill - thanks Doug!

 

[Maarkr]

You can try to correct the formula in Excel so the field is imported as a
numeric or date value (instead of "", insert "1/1/1950" and filter it out in
the query), or since the #NUM is probably a null value in Access, you may be
able to filter it out in the query with a criteria like 'NOT IS NULL'

 

[Adam]

You can try to correct the formula in Excel so the field is imported as a
numeric or date value (instead of "", insert "1/1/1950" and filter it outin
the query), or since the #NUM is probably a null value in Access, you maybe
able to filter it out in the query with a criteria like 'NOT IS NULL'

The problem I've now come across is the table I've linked to the Excel
spreadsheet has two of the fields recorded as 'Double' formats...
however all of my other tables are working with Long Integers, which I
cannot change...


How can I get the linked table (excel spreadsheet) to have long
integers for 2 of its fields instead of doubles?

 

[Douglas J. Steele]

I'm not sure you can.

However, if your concern is to be able to join the fields, you can still
join them. You'll have to go into the SQL of the query and use the CLng
function on the Double field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can try to correct the formula in Excel so the field is imported as a
numeric or date value (instead of "", insert "1/1/1950" and filter it out
in
the query), or since the #NUM is probably a null value in Access, you may
be
able to filter it out in the query with a criteria like 'NOT IS NULL'

The problem I've now come across is the table I've linked to the Excel
spreadsheet has two of the fields recorded as 'Double' formats...
however all of my other tables are working with Long Integers, which I
cannot change...


How can I get the linked table (excel spreadsheet) to have long
integers for 2 of its fields instead of doubles?

 

[Adam]

I'm not sure you can.

However, if your concern is to be able to join the fields, you can still
join them. You'll have to go into the SQL of the query and use the CLng
function on the Double field.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





The problem I've now come across is the table I've linked to the Excel
spreadsheet has two of the fields recorded as 'Double' formats...
however all of my other tables are working with Long Integers, which I
cannot change...

How can I get the linked table (excel spreadsheet) to have long
integers for 2 of its fields instead of doubles?

That has got around the problem.

thank you Doug!