How do I declare 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
Sandy
 
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?
Click to expand...

Neither; you need to do it in the VBA code which is generating the
error! VBA is one language (which has DIM statements and Variants);
SQL is a different language altogether. I suspect that you're either
calling some VBA function from your query, or that this error is
arising for some other reason.

Please post the SQL view of the query and the VBA of the code that
(I'm guessing) that you're using to launch the query.
 
Thanks John - 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;
 
Hi again John -

Here is more detail about what I am trying to accomplish

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.
 
Hi again John -

Here is more detail about what I am trying to accomplish:

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

Ummm... No. You do NOT need to run separate queries. You can a query
based on another query instead.

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

Now create a second query joining THIS query to your table, joining by
ProductID and the date field, to pick up the rest of the information
for this version.
 
Back
Top