INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using public constants in WHERE clauses

Using public constants in WHERE clauses

(OP)
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

RE: Using public constants in WHERE clauses

Try:

CODE

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.

RE: Using public constants in WHERE clauses

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.

CODE -->

Public Const SomeConstant

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

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

RE: Using public constants in WHERE clauses

(OP)
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

RE: Using public constants in WHERE clauses

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.

CODE -->

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()

RE: Using public constants in WHERE clauses

(OP)
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

RE: Using public constants in WHERE clauses

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

RE: Using public constants in WHERE clauses

Quote:

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close