Using public constants in WHERE clauses

[lamarw]

Hi Everyone,
I've looked all over the POSTS for something on the subject of using CONSTANTS in a WHERE clause of an SQL Select statement. I can't find anything. This is a sample:

The constant might look like this in the Module:

Public Const FromThisDateToThatDate = "FromDate>=3/1/2015 AND ToDate<=4/1/2015 AND Status=1 AND ...etc."

SELECT * FROM tblTable

WHERE FromThisDateToThatDate​

ORDER BY ID​

Is it possible to use a Public Constant in this way?

Lamar

 

[Andrzejek]

Try:

Public Const FromThisDateToThatDate As String = _
"FromDate>=3/1/2015 AND ToDate<=4/1/2015 AND Status=1 AND ...etc."

strSQL = "SELECT * FROM tblTable " _
    & " WHERE " & FromThisDateToThatDate _
    & " ORDER BY ID"

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

I am reading this a little differently. If you want to use a variable or a constant in a query (not a query built in vba) you need a function.

Public Const SomeConstant

Public Function GetSomeConstant () as datatype
  GetSomeConstant = someConstant
end Function

Then in a query
Select ... where SomeField = GetSomeConstant()

 

[lamarw]

Thanks MajP. So, then I can't include in the constant all the fields and their values thereby creating the entire clause without the 'WHERE'?

Lamar

 

[MajP]

Okay, I see what you are saying. My interpretation is incorrect. You would have to do that in VBA like Andy showed, if you wanted to do something like that. However, that concept does not make any sense. If the dates where always constant that you were searching, you could just use a stored query.
Why would you want to do this, if it was possible. I do not see any utility.
I would think it would be more likely that the dates you would be searching are variable.

public fromDate as date
public toDate as date

Public sub someCodeToSetTheDates
  ....
  fromDate = ....
  toDate = ....
end sub

public function GetFromDate() as string
  GetFromDate = "#" & format(FromDate,"mm/dd/yyyy") & "#"
end function
public function GetToDate() as string
  GetToDate = "#" & format(toDate,"mm/dd/yyyy") & "#"
end function

Query1
SELECT * FROM tblTable 
WHERE FromDate >= & getFromDate() and toDate <= getToDate() 
ORDER BY ID

In that context you can only use functions to return a value. You cannot so something like

Select getFieldName() from gettableName()

 

[lamarw]

Okay, after I submitted it occurred to me the use of 'dates' would be confusing. I'm trying to standardize the results by defining WHERE clauses in constants due to their complexity thereby avoiding inconsistent results by being consistent in the 'WHERE'. What if the WHERE clause looked like "Status=1 AND Transfer <>3 AND Transfer<>4 AND PIO=True AND MS=False AND ...etc" (No Dates allowed.) I know this is not complex its just a sample but a more realistic one. There are several more elements involved.

Lamar

 

[dhookom]

I believe you can only use expressions like these by modifying the SQL property of saved queries.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I'm trying to standardize the results by defining WHERE clauses in constants due to their complexity thereby avoiding inconsistent results by being consistent in the 'WHERE'. What if the WHERE clause looked like "Status=1 AND Transfer <>3 AND Transfer<>4 AND PIO=True AND MS=False AND ...etc" (No Dates allowed.) I know this is not complex its just a sample but a more realistic one. There are several more elements involved

This still makes absolutely no sense and has zero utility. If you can define a constant where clause, than you could just store the query. A stored query is always more efficient because it benefits from sql optimization. You are not saving anything by defining a series of where clauses as constants. Further the application is far less updateable.