Declaring a variable as a Variant

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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
Click to expand...

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.
 
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 said:
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?
Click to expand...

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?
 
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 said:
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.
Click to expand...

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.
 
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 said:
Still does not explain why the Append query did not work, but I will
leave it for now.
Click to expand...

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