Solved Tempvars not working in function?

[Gasman]

@theDBguy
Mainly as I got it from your site.

I am trying to run a query to add this months dates to a table.

I have set up the From and To values using Tempvars, but when the function at https://www.accessmvp.com/thedbguy/codes.php?title=execqry gets them, it says it cannot find them in the query, on the line

 prm.Value = Eval(prm.Name)

I am passing in a query name, not a string.
So where am I going wrong please?

Query.

PARAMETERS [Tempvars("dtFrom")] DateTime, [Tempvars("dtTo")] DateTime;
INSERT INTO tblDates ( StepsDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between Tempvars!DtFrom And Tempvars!dtTo) And ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);

Immediate window

? eval(TempVars("dtFrom"))
 7.05467372134039E-05
01/07/2025

? eval(TempVars("dtTo"))
 2.18694885361552E-03
31/07/2025

 

[Gasman]

Tried the .Value property as well, same error

PARAMETERS [Tempvars("dtFrom")].[Value] DateTime, [Tempvars("dtTo")].[Value] DateTime;
INSERT INTO tblDates ( StepsDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between Tempvars!DtFrom And Tempvars!dtTo) And ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);

 

[Gasman]

Got it.
Have to use Tempvars!dtFrom etc

 

[Josef P.]

Look at param name.

With CurrentDb.QueryDefs("YourQuery")
      Debug.Print .Parameters(0).Name ' => [Tempvars("dtFrom")] or Tempvars("dtFrom")
End With

=> if [...]
prm.Value = Eval(TrimChar(prm.Name, "[]"))

Public Function TrimChar(ByVal TextToTrim As String, ByVal CharsToRemove As String) As String
   Do While InStr(1, CharsToRemove, Left(TextToTrim, 1)) > 0
      TextToTrim = Mid(TextToTrim, 2)
   Loop
   Do While InStr(1, CharsToRemove, Right(TextToTrim, 1)) > 0
      TextToTrim = Left(TextToTrim, Len(TextToTrim) - 1)
   Loop
   TrimChar = TextToTrim
End Function

/edit:

Have to use Tempvars!dtFrom etc

This is easier.

Let's take the upper part as a lesson learned for possible later function/property calls.

 

[theDBguy]

Got it.
Have to use Tempvars!dtFrom etc

Sorry, just woke up. I think the Tempvars("varnane") is a VBA syntax, while the Tempvars!varnane is a query (expression service) syntax.

 

[Gasman]

Probably. The "name" is my usual syntax. Have never used the .Add method.