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

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.

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



"Adam" <(E-Mail Removed)> wrote in message
news:4f33b6d4-451a-47e7-882d-(E-Mail Removed)...
> 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

On Dec 28, 12:09*pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
> 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.
>
> --
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no e-mails, please!)
>
> "Adam" <evansada...@googlemail.com> wrote in message
>
> news:4f33b6d4-451a-47e7-882d-(E-Mail Removed)...
>
>
>
> > 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

Brill - thanks Doug!

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" wrote:

> 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
> .
>

On Dec 28, 12:50*pm, Maarkr <Maa...@discussions.microsoft.com> wrote:
> 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'
>
>
>
> "Adam" wrote:
> > 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
> > .

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?

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
http://I.Am/DougSteele
(no e-mails, please!)



"Adam" <(E-Mail Removed)> wrote in message
news:76983df3-e415-4a94-b977-(E-Mail Removed)...
On Dec 28, 12:50 pm, Maarkr <Maa...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
> > .

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?

On 29 Dec, 01:28, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
> 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!)
>
> "Adam" <evansada...@googlemail.com> wrote in message
>
> news:76983df3-e415-4a94-b977-(E-Mail Removed)...
> On Dec 28, 12:50 pm, Maarkr <Maa...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > 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" wrote:
> > > 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
> > > .
>
> 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!