Can not get Access 2016 queries to accept form value or TempVars! as criteria (1 Viewer)

[bcmarshall]

I've been working in Access 2007 forever and have a myriad of places where a form input is used as a query parameter. In the Criteria line I simply put [Forms]![FormName]![FieldName] and that's all that's ever been necessary. That has also been true of a temporary variable. I only had to put [TempVars]![VariableName] in the Criteria line and everything worked as expected.

I've switched to Access 2016 and I can't get it to accept those Criteria. I get an error message that says, "The Microsoft Access Database Engine does not recognize " as a valid field."

I've done this so often in the past that I just can't understand what the issue could be. I'm hoping one of you wizards can point me in the right direction.

As always, thanks in advance for any replies.

 

[MajP]

Are you say all queries or a specific query? There are certain types of queries where you will have to provide the parameter explicitly. Crosstab by chance?

 

[CJ_London]

To be clear is this every query with this type of criteria? or just one particular query? Is the query built in vba or exists in a querydef?

Since you have not provided any real data, any answer will be speculative - perhaps due to a typo in form or field name or perhaps fieldname is in a subform or the form is not open if it relates to the forms collection. Or perhaps the field you are comparing to doesn't exist.

 

[Minty]

Are you calling those queries in code by any chance?

 

[GPGeorge]

I have actually observed this problem with Tempvars. I resorted to a set of helper functions to resolve the Tempvars values in VBA. I don't have a good explanation as to why this happens. I can't speak to the forms reference issue, as I seldom use them these days. I opted for Tempvars in most places I would have used the forms references.

Public Function TempVarsDate(ByVal VarName As String) As Date
' change the default date to whatever works in the current solution

    If IsDate(TempVars(VarName)) Then     
        TempVarsDate = DateValue(Nz(TempVars(VarName), #1/1/2099#))
    Else
        TempVarsDate = #1/1/2099#
    End If
          
End Function

Public Function TempVarsLong(ByVal VarName As String) As Long

    TempVarsLong = CLng(Nz(TempVars(VarName), 0))
          
End Function

Public Function TempVarsString(ByVal VarName As String) As String


    TempVarsString = CStr(Nz(TempVars(VarName), "*"))
          
End Function

Public Function TempVarsValue(ByRef TempVarName As String, ByVal TempVarValue As Variant) As Boolean
     'Create and initialize any tempvar

    TempVars.Add Name:=TempVarName, Value:=TempVarValue
    TempVarsValue = True
          
End Function

 

[GPGeorge]

I have actually observed this problem with Tempvars. I resorted to a set of helper functions to resolve the Tempvars values in VBA. I don't have a good explanation as to why this happens. I can't speak to the forms reference issue, as I seldom use them these days. I opted for Tempvars in most places I would have used the forms references.

Public Function TempVarsDate(ByVal VarName As String) As Date
' change the default date to whatever works in the current solution

    If IsDate(TempVars(VarName)) Then    
        TempVarsDate = DateValue(Nz(TempVars(VarName), #1/1/2099#))
    Else
        TempVarsDate = #1/1/2099#
    End If
         
End Function

Public Function TempVarsLong(ByVal VarName As String) As Long

    TempVarsLong = CLng(Nz(TempVars(VarName), 0))
         
End Function

Public Function TempVarsString(ByVal VarName As String) As String


    TempVarsString = CStr(Nz(TempVars(VarName), "*"))
         
End Function

Public Function TempVarsValue(ByRef TempVarName As String, ByVal TempVarValue As Variant) As Boolean
     'Create and initialize any tempvar

    TempVars.Add Name:=TempVarName, Value:=TempVarValue
    TempVarsValue = True
         
End Function

Example usage:

SELECT E.exercisedate,
E.exercisetypeid
FROM tblexercise AS E
WHERE
((
( Iif(Tempvarslong("lngexercisetypeid") = 0, 0, [E].[exercisetypeid]) ) = Iif(Tempvarslong("lngexercisetypeid") = 0, 0, Tempvarslong("lngexercisetypeid")) ))
GROUP BY E.exercisedate,
E.exercisetypeid
ORDER BY E.exercisedate DESC;

 

[bcmarshall]

Thank you all for your quick and thorough replies.

It is a Select Query that I’m working with. And I have just switched from 2007 to 2016, and this is the first effort so I have not tried other queries.

I can tell you that it works if I type the limiting text in directly instead of trying to get it from a form or variable.

CJ, I understand the direction you’re going. I can only tell you that I’m confident a typo isn’t the issue.

Minty, the query is called by opening a report. It has worked for years but the owner asked me to allow him to input some limiting parameters.

George, thanks for the thoughts but the TempVars is not the issue. I used the Immediate Window to confirm that the chosen variable and the form value produced the same text. If I type the text in it works as expected but it will not draw from variable.

 

[Gasman]

Can you retest the new changes in 2007 still?
I have 2007 if it helps?

 

[MajP]

Please post the SQL.

 

[sonic8]

I can only tell you that I’m confident a typo isn’t the issue.

Confidence is good. Nonetheless, I strongly recommend you double check this, as it is the most logical explanation for the problem you are facing.

 

[bcmarshall]

Access 2016 actually fills in the form parameters for you. I type Forms! And a drop-down list of form names appears. I select a form and another drop-down list if field names appears to select from.

Because of that I am confident that the name is entered correctly and that there is no typo.

 

[bcmarshall]

I am in error about something. The query in question is a Select Query but one of the source queries is a Crosstab Query.

However the field I am attempting to add a criteria to comes from a source Select Query and not the Crosstab.

 

[MajP]

That is likely the problem. You have no idea the query execution plan, thus it is failing on the crosstab? Seriously post the SQL or we are all just guessing.

 

[Minty]

You have to define parameters for a crosstab even if they are passed through from another query.
However, I'm sure this has been the case for many years, even before Access 2007, unless newer versions are more fussy.
(Which I know they are)

 

[CJ_London]

my problem is you say the error message is

"The Microsoft Access Database Engine does not recognize " as a valid field."

You are focussing on the criteria

just seen your latest post re crosstab- as Majp mentioned in post #2 - you have to declare the parameters. So declare them

 

[bcmarshall]

I have more info. I tested the same TempVars in another query and it works normally so clearly something else is wrong.

I will test and let you know what I find.

Thank you all for your help.

 

[bcmarshall]

What threw me off is that this issue appeared the first time I attempted it in 2016 and so I assumed it was a version issue rather than something else.

I remember now that “assume” makes an ass out of u and me!

I’ll keep you posted now that I have a direction to follow.

 

[bcmarshall]

MajP and CJ, can you please explain what you mean by declaring the parameters? I'm not sure I understand.

Yes, the error message is as you mentioned, and as I said one of the source queries is a Crosstab, but the specific field that I'm applying the criteria to is not from that query, but from a regular Select query.

 

[bcmarshall]

I have another question. Why does the exact same text manually typed work and yet it fails with that error message as a variable?

Can you imagine any reason for that?

 

[Minty]

It's not a parameter if you hardcode it.

In the query design you can use the parmeters option (Right Click)

Or you can simply declare them in the SQL;

PARAMETERS declaration (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com