UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Global Query Variables, Any Version    
 
   
ws1o
post Jul 2 2019, 01:06 PM
Post#1



Posts: 104
Joined: 30-October 15
From: Central CT USA


Anyone have an idea for how to use global variables in a database app?

I have a database where different users will want to run it using only data for a couple of countries.

Most data tables have a Country Field (column), there are many queries. A user may only want to see data for 1 to 3 specific countries, such as
CODE
WHERE COUNTRYCODE = 'US'

or
CODE
WHERE COUNTRYCODE IN ('US','CA')

and so on, throughout the database.

Is there an easy way to set and use these values globally?

I was going to use TempVars, but I can't see how to get Access to use them in queries...

I tried
CODE
TempVars.Add "Countries", "'US','CA'"

then...
CODE
SELECT * FROM MyTable WHERE CountryCode IN ( & TempVars!Countries & )

but Access doesn't like that

Any great ideas?
Go to the top of the page
 
theDBguy
post Jul 2 2019, 01:12 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi. You should be able to use TempVars but maybe just modify your query slightly to accommodate. For example, try:
CODE
SELECT * FROM MyTable WHERE "," & TempVars!Countries & "," Like "*," & CountryCode & ",*"
(untested)
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jul 2 2019, 01:19 PM
Post#3


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


Tempvars are especially good for this kind of task. Set the TempVar value in VBA as needed. Refer to it in the query's Criteria.

Example:

Set the tempvars value(s):

CODE
Public Function LoadForm(formName As String)

        TempVars.Add Name:="FoodShoppingID", Value:=0
        TempVars.Add Name:="CategoryID", Value:=0

    DoCmd.OpenForm formName:=formName, view:=acNormal, Windowmode:=acWindowNormal

End Function


Note that the initial value is set to 0 so there will be a non-null value at first run of a query. Elsewhere in the code, the tempvars values are changed, based on a selection from a combo box or list box.

The query is like this:

SQL
SELECT tblFoodShopping.FoodShoppingID, tblFoodShopping.FoodItem AS Item, Val([FoodShoppingID]) AS ShoppingIDFilter
FROM tblFoodShopping INNER JOIN tblFoodCategory ON tblFoodShopping.CategoryID = tblFoodCategory.FoodCategoryID
WHERE (((Val([FoodShoppingID]))=[TempVars]![FoodShoppingID]))
ORDER BY tblFoodShopping.FoodItem;

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jul 2 2019, 01:20 PM
Post#4


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


Modify as theDBGuy shows to handle the string values. Mine uses numeric values.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ws1o
post Jul 2 2019, 01:38 PM
Post#5



Posts: 104
Joined: 30-October 15
From: Central CT USA


Hi, theDBguy...

I did this:

CODE
tempvars.Add "Countries","'US','CA'"

But when I try to run this I get an Not a Valid Bookmark error (??)

CODE
SELECT * FROM MyTable WHERE "," & TempVars!Countries & "," Like "*," & CountryCode & ",*"
Go to the top of the page
 
theDBguy
post Jul 2 2019, 01:43 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hmm, I've never heard of that error before. Just as a test, could you try setting the TempVar like this? Thanks.
CODE
TempVars.Add "Countries", "US,CA"

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Jul 2 2019, 01:58 PM
Post#7


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


QUOTE
CODE
SELECT * FROM MyTable WHERE "," & TempVars!Countries & "," Like "*," & CountryCode & ",*"


This doesn't look like it will parse out correctly.

Perhaps something more like:
CODE
strSQL = "SELECT * FROM MyTable WHERE CountryCode IN (" & CountryCode & ");"



EDIT: Oops! ignore me - I seem to have taken this thread round in a circle blush.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Jul 2 2019, 02:00 PM
Post#8


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi David. I wrote a short blog on this not too long ago. Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Jul 2 2019, 02:04 PM
Post#9


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


DBG, I added an edit as soon as I read the rest of the thread (after posting blush.gif)

Wouldn't you have to surround the tempvars in quotes to make them a string?

I have never seen an SQL statement like:
CODE
SELECT * FROM MyTable WHERE ,US,CA, Like *,US,*;


but I could understand:
CODE
SELECT * FROM MyTable WHERE ',US,CA,' Like '*,US,*';


So more like:
CODE
SELECT * FROM MyTable WHERE "'," & TempVars!Countries & "'," Like '*," & CountryCode & ",*'"


Or are you doing this directly in a query?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jul 2 2019, 02:20 PM
Post#10


UtterAccess VIP
Posts: 11,459
Joined: 6-December 03
From: Telegraph Hill


Gaah! Ignore me again - I see you are doing it directly in a query!

--------------------


Regards,

David Marten
Go to the top of the page
 
ADezii
post Jul 2 2019, 02:24 PM
Post#11



Posts: 2,535
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. All you will need is a single Public Constant Declaration which you can change on-the-fly, as in:
    CODE
    Public Const conSTATE = "IN('PA','WA')"
  2. Sample usage:
    CODE
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM Employees WHERE [State/Province] " & conSTATE

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

    With rst
      If Not .BOF And Not .EOF Then
        .MoveLast
          MsgBox "Number of Records for State/Province: " & .RecordCount, _
                  vbInformation, "Record Count"
      End If
    End With

    rst.Close
    Set rst = Nothing
  3. Possible Declarations:
    CODE
    Public Const conSTATE = "IN('PA','WA')"
    Public Const conSTATE = "= 'WA'"
    Public Const conSTATE = "= 'PA'"
  4. Code has been tested and is operational.
Go to the top of the page
 
GroverParkGeorge
post Jul 2 2019, 04:49 PM
Post#12


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


That's not valid syntax for adding a tempvar. See how I did it? One tempvar for each value?

You'll have to create separate tempvars for each country and filter on all of them.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Jul 2 2019, 05:12 PM
Post#13


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


I would just use a lookup table, and join on that.

I prefer to use data rather than code if possible.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
dale.fye
post Jul 3 2019, 08:39 AM
Post#14



Posts: 129
Joined: 28-March 18
From: Virginia


While I'm a huge fan of tempvars, in this case, I would prefer to have a front-end table tblCountryFilter, which would contain a field for Country codes and a Yes/No checkbox (IsSelected) and would simply join this table to your query with a filter on:

WHERE tblCountryFilter.IsSelected = true

Dale Fye
Developing Solutions, LLC
http://dev-soln.com


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
ws1o
post Jul 4 2019, 03:37 PM
Post#15



Posts: 104
Joined: 30-October 15
From: Central CT USA


Yeah, I'm thinking something like that will end up being the way to go
Go to the top of the page
 
gemmathehusky
post Jul 4 2019, 04:33 PM
Post#16


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


The thing is it's easy to select ALL Countries, or just a single country

In the first case you don't need a where
In the second it's just something like
where country = " & tempvars.countrycode


the difficulty is always with a number of selected countries. That's why it's often easier to have a selected field in a countries table, so you can tick the countries you want, and then the where clause is

where selectedcountry = true (as Dale just said)

but it still needs some thought if multiple users are doing this all at the same time.

for what it's worth, I still use A2003, and never use a tempvar.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 07:42 PM