I am running an append query and get this error

You tried to assign a Null value to a variable that is not a Variant data type. Use the Dim statement to declare the variable as a Variant, and then try the operation again

How do I do this in the Design View or the SQL window of the query

thank
Sand

"Sandy C" <(E-Mail Removed)> wrote in message
news:2A4DA1FE-D2E6-4043-B78B-(E-Mail Removed)
> I am running an append query and get this error:
>
> You tried to assign a Null value to a variable that is not a Variant
> data type. Use the Dim statement to declare the variable as a
> Variant, and then try the operation again.
>
> How do I do this in the Design View or the SQL window of the query?
>
> thanks
> Sandy

I can't make sense of that error message in the context of running a
query. Does this query call a VBA or user-written function? If you're
looking at the query in the query designer, do you get the error message
when you either switch to datasheet view or click the "Run" button?

If you get the error only when executing the query from VBA code, please
post the code.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk -

I need to get a comboined list of parts for the latest version of several products. Since each product is at a different version, I need to run a query that finds each product one at a time so I can find the latest of each (if not it will find the latest of all of them and only get one product

So I made a make table query to find the latest parts for the first product, then changed it to an append query so I could select the next product. This is where I get the error.

Here is the SQL view of the query

INSERT INTO CombinedBOM ( BoardName, Version, BomRev, ProductID

SELECT TOP 1 Projects.Projects AS BoardName, BOMs.Version, BOMs.BomRev, Products.ProductI

FROM Projects RIGHT JOIN (Products RIGHT JOIN (Boards RIGHT JOIN BOMs ON Boards.Board_ID = BOMs.BoardID) ON Products.ProductID = BOMs.[Part ID]) ON Projects.Proj_ID = Boards.ProjectI

WHERE (((Projects.Projects) Like "*" & [enter board number] & "*")

ORDER BY BOMs.Version DESC

Any ideas?

"Sandy C" <(E-Mail Removed)> wrote in message
news:B6CD9595-16B0-4115-8FF9-(E-Mail Removed)
> Hi Dirk -
>
> I need to get a comboined list of parts for the latest version of
> several products. Since each product is at a different version, I
> need to run a query that finds each product one at a time so I can
> find the latest of each (if not it will find the latest of all of
> them and only get one product)
>
> So I made a make table query to find the latest parts for the first
> product, then changed it to an append query so I could select the
> next product. This is where I get the error.
>
> Here is the SQL view of the query:
>
> INSERT INTO CombinedBOM ( BoardName, Version, BomRev, ProductID )
>
> SELECT TOP 1 Projects.Projects AS BoardName, BOMs.Version,
> BOMs.BomRev, Products.ProductID
>
> FROM Projects RIGHT JOIN (Products RIGHT JOIN (Boards RIGHT JOIN BOMs
> ON Boards.Board_ID = BOMs.BoardID) ON Products.ProductID = BOMs.[Part
> ID]) ON Projects.Proj_ID = Boards.ProjectID
>
> WHERE (((Projects.Projects) Like "*" & [enter board number] & "*"))
>
> ORDER BY BOMs.Version DESC;
>
> Any ideas?

So you're saying you get this error when you run the query from the
query designer?

If you put the query into datasheet view (as if it were a select query)
do you get the error? Or does it happen only when you actually run it
as an action query?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The error only happens as an action query - runs fine as a select query or a "Make Table" query, but not as an "append" query.

"Sandy C" <(E-Mail Removed)> wrote in message
news:B46C0D8D-9C23-49F5-BB36-(E-Mail Removed)
> The error only happens as an action query - runs fine as a select
> query or a "Make Table" query, but not as an "append" query.

I don't understand why that would be, given the information you've
posted. What version of Access are you running? If it's Access 2002,
2000, or 97, I wouldn't mind if you sent me a cut-down copy of the
database so that I can investigate it better. If you'd like to do that,
please make a copy of the database, remove everything except the
elements necessary to demonstrate the problem, compact it and then zip
it to less than 1MB in size (preferably much smaller). E-mail it to the
address derived by removing NO SPAM from the reply address of this
message, and I'll have a look at it, time permitting.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks, Dirk, but I was suggested the following which worked perfectly..
========
No. You do NOT need to run separate queries. You can a quer
based on another query instead.

Create a Totals query, grouping by product and selecting Max of th
version date field; include only these two fields in the query

Now create a second query joining THIS query to your table, joining b
ProductID and the date field, to pick up the rest of the informatio
for this version
=========

Still does not explain why the Append query did not work, but I will leave it for now

Thanks so much

Sand

"Sandy C" <(E-Mail Removed)> wrote in message
news:3F047592-5F57-4D1E-A394-(E-Mail Removed)
>
> Still does not explain why the Append query did not work, but I will
> leave it for now.

That's the intriguing question, but if you want to shelve it I'll just
sit on my curiosity. Good luck!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)