SQL Statement

[news]

Is it possible to have a variable in the HAVING statement instead of the
field [FLDVST.Dt_Visited]? When I use a variable and run the statement the
query ask for a parameter for the variable, it think it's a field. When I
look at the design of the query it added the variable as a field. Thanks.


Set db = CurrentDb()
strSQLSelect = _
"SELECT " _
& "[TW: ProvidersContractEffDate].[Key Name], " _
& "FROM [TW: ProvidersContractEffDate]
& "GROUP BY [TW: ProvidersContractEffDate].[Key Name]

& "HAVING (((FLDVST.Dt_Visited) " _

& "Between #" _

& [Forms]![TW: Reports]![StrDate] & "# " _

& "And " _

& [Forms]![TW: Reports]![EndDate] & "#) ")) _

 

[Ken Snell [MVP]]

Your SQL statement is incomplete, but what you do is concatenate the value
of the variable in the string (see your HAVING line):

Set db = CurrentDb()
strSQLSelect = _
"SELECT " _
& "[TW: ProvidersContractEffDate].[Key Name], " _
& "FROM [TW: ProvidersContractEffDate]
& "GROUP BY [TW: ProvidersContractEffDate].[Key Name]

& "HAVING (((" & MyVariableName & ") " _

& "Between #" _

& [Forms]![TW: Reports]![StrDate] & "# " _

& "And " _

& [Forms]![TW: Reports]![EndDate] & "#) ")) _