Solved Parameterised query requires me to re-cast parameter in order to run (?)

[AOB]

I have a query which takes a parameter (a User ID) and then appends a record to a table using that parameter, along with a couple of other fields from a staging table, assuming a similar record doesn't already exist (tested using an outer join). Not a complicated query and I have dozens of similar elsewhere.

PARAMETERS SpecifiedUserID Long;
INSERT INTO tblEmployees
SELECT sE.EmployeeID AS EmployeeID, SpecifiedUserID AS CreatedBy, Now AS CreatedTimeLocal, ConvertLocalTimetoUTCTime(Now) AS CreatedTimeUTC
FROM stgEmployees AS sE
LEFT JOIN tblEmployees AS E ON sE.EmployeeID = E.EmployeeID
WHERE E.EmployeeID IS NULL;

What's puzzling me is, this query fails with the following message :

Number of query values and destination fields are not the same

A quick bit of debugging narrowed it down to this field/value : SpecifiedUserID AS CreatedBy

Which I thought was kinda strange as the parameter is already defined as being a Long Integer (from the first line of the query) and the receiving field is similarly defined as containing Long Integers (?)

Sure enough, if I explicitly "re-cast" the parameter as a Long Integer in the INSERT clause, the query works :

PARAMETERS SpecifiedUserID Long;
INSERT INTO tblEmployees
SELECT sE.EmployeeID AS EmployeeID, CLng(SpecifiedUserID) AS CreatedBy, Now AS CreatedTimeLocal, ConvertLocalTimetoUTCTime(Now) AS CreatedTimeUTC
FROM stgEmployees AS sE
LEFT JOIN tblEmployees AS E ON sE.EmployeeID = E.EmployeeID
WHERE E.EmployeeID IS NULL;

Why would this be necessary / what am I overlooking in either the query or the destination table that might cause the engine to need to coerce the variable into a data type that has already been defined correctly?

What's concerning me is, I have loads of these kinds of queries, and this is the only one that doesn't seem to "trust" the data type being passed (even though I've confirmed everything looks exactly as it should / as I would expect?)

Thanks

Al

 

[arnelgp]

maybe you can specify the fieds to the target table:

Insert Into tblEmployees (EmployeeID, CreatedBy, CreatedTimeLocal, CreatedTimeUTC)
Select ...

 

[AOB]

I mean, I can - similarly I can just use CLng() to force the type - I do have a "solution" for the problem, I just don't understand why the problem exists in the first place (more importantly, whether I need to be concerned about other very similar queries that work just fine without re-casting)

I don't understand why this works :

PARAMETERS SpecifiedUserID Long;
INSERT INTO tblEmployees ( EmployeeID, CreatedBy, CreatedTimeLocal, CreatedTimeUTC )
SELECT sE.EmployeeID, SpecifiedUserID, Now, ConvertLocalTimetoUTCTime(Now)
FROM stgEmployees AS sE
LEFT JOIN tblEmployees AS E ON sE.EmployeeID = E.EmployeeID
WHERE E.EmployeeID IS NULL;

But this doesn't :

PARAMETERS SpecifiedUserID Long;
INSERT INTO tblEmployees
SELECT sE.EmployeeID AS EmployeeID, SpecifiedUserID AS CreatedBy, Now AS CreatedTimeLocal, ConvertLocalTimetoUTCTime(Now) AS CreatedTimeUTC
FROM stgEmployees AS sE
LEFT JOIN tblEmployees AS E ON sE.EmployeeID = E.EmployeeID
WHERE E.EmployeeID IS NULL;

(FWIW I prefer the structure of the second one because each individual field is explicitly set by name/alias, rather than having to ensure the order of the fields in the INSERT INTO clause matches the order of the values in the SELECT clause - makes it a lot easier to read for longer such queries)

 

[DHookom]

I always specify the target fields as suggested by (and works) arnelgp.

You might find some additional clarification about using all fields at https://www.w3schools.com/sql/sql_insert.asp

 

[GPGeorge]

I am a firm believer in the idea that being explicit is better than naively accepting the implicit in almost every aspect of a database.

For better or worse, Access is very good at accommodating ambiguous references, such as not bothering to specify the target fields in an insert query. But in keeping with the idea that I want to be in control of what happens, I think it's just common sense to remove ambiguity to the degree possible.

Yeah, most of the time everything works out the way we anticipate it will without being explicit about what we want, but not always.

In this case, that means specifying both the source fields and data fields explicitly.

Take control of your database.

 

[Pat Hartman]

I can't agree more strongly with the points made by the other experts. Do not assume defaults, always be specific. It's a little like defensive driving and street signs. Make sure you always do the right thing and make sure the people who read your code know what you did even when they, personally, don't remember the defaults. It's a little old fashioned and really uncool but I'm OK with being uncool.

 

[AOB]

I am old fashioned and also really uncool so that argument resonates with me...

Thanks everyone for your respective inputs!