By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

How to filter query using another data field?

100+
P: 180
I used already fgetname() in criteria in one data field of a query, how can i filter query? can i use again the fgetname() in criteria in other data field of query?
Jan 3 '12 #1

✓ answered by eneyardi

How could you make query if you don't have a table?

Share this Question
Share on Google+
11 Replies


100+
P: 759
Yes you can use the same function every where you need.
If you wish the same result any time is not a problem. But if you wish different results (based from where you apply the function) you must slightly modify the function.
Jan 3 '12 #2

100+
P: 180
It shows blank records when i used fgetname() in same query but in different data field.

This are the details: In my form name (home) i have two combobox name (combo238) and (combo245). Combo238 rowsource contains name of employees ex. "Juan, Reynulfo R.";"Dela Cruz, Juan C."
while combo245 rowsource contains accounting code ex. "155";"222";"223"
In query name (mstrquery) i put fgetname() in datafield name of employee criteria and another fgetname() to datafield accounting code. When i preview the home form and clicked one of the accounting code in combo245, i got blank record same when i clicked one of the employee name in combo238
Jan 4 '12 #3

100+
P: 759
Of course. You obtain the correct result.
That because your function return the same value. So you filter the query with the same value in both fields.
I am almost sure that you use a single variable to store the value(s) from both combo boxes. Something like this:
Expand|Select|Wrap|Line Numbers
  1. YourVar = Combo238
  2. YourVar = Combo245
  3. DoCmd.OpenQuery ("queryName")
By the way: Why you don't rename your combo boxes in order to know exactly what data is stored in ? As example rename Combo238 as cmbNames and Combo245 as cmbAccounts.

Returning to the preview code, when you open the query, YourVar is all the time = Combo245. So, the filter for both fields is YourVar's value. And is no employe name equal to... account. So the query show... nothing (not blank records).

Even if you use this code:
Expand|Select|Wrap|Line Numbers
  1. YourVar = Combo238
  2. DoCmd.OpenQuery ("queryName")
  3. YourVar = Combo245
  4. DoCmd.OpenQuery ("queryName")
the query will show no records.
This time when you open the query first time (line 2) you will have the value from Combo238 as criteria for both your fields => no records (but you don't see this result because immediately you apply again the query (line 4) this time with Combo245's value as filter to both fields => no records.

In this situation, as I say (see my preview post) you must modify a little bit the code:
Declare two Public variables (because you need to store two values) and modify the function.
Expand|Select|Wrap|Line Numbers
  1. Public strEmployeName As String, strAccount As String
  2.  
  3. Public Function RetVal(ParameterIdentifier As String)
  4.     Select Case ParameterIdentifier
  5.         Case "EmployeName"
  6.             RetVal = strEmployeName
  7.         Case "Account"
  8.             RetVal = strAccount
  9.         Case Else
  10.             Stop 'for debug purpose
  11.     End Select
  12. End Function
Now, in the query's criteria row type:
- for field with employes names: RetVal("EmployeName")
- for field with accounts: RetVal("Account")

That is all.
Of course you must set the values for both variables under click event of your combo boxes before you use the query.

Keep in mind that is no difference if you apply a function from VBA or from the Queries.

I answer to your original question: if you can use the same function (and how to) in another field's criteria row.

If you like you can design as many function you need in order to use each one in a single place. But I think that is a bad practice).

Let me know if I can help you more.
Jan 4 '12 #4

100+
P: 180
still it shows no records after i put retval("EmployeName") in the criteria of field with employee names and retval("Account") in the criteria of field with accounts. i copied your codes declaring two public variables
Jan 4 '12 #5

100+
P: 180
I renamed the two combobox cmbnames for combo238 and cmbaccount for combo245. i replaced the previous contents of module then applying your given code, i don't know what are my shortcomings.
Jan 4 '12 #6

100+
P: 180
In Case "EmployeName" is this data field name in query?
Jan 4 '12 #7

100+
P: 759
Oh no !
You MUST understand the code. You can't only copy-paste it.
First of all do you use the click event for the combo boxes to set the variables strEmployeName and strAccount?
Second: Are your query working if you put values (not the function) in the criteria row ?
Third: How you design your database ? Every employe can have multiple accounts ? If YES, are you sure that when you select an employe name from cmbNames and an account from cmbAccounts THAT employe has THAT account ?

There are a lot of questions. And YOU must answer to that questions. No one from this forum know how you design your database, how you design the queries and what you wish to achieve.

No. "EmployeName" is NOT the field name in your query. It is impossible for me to know how you name the fields in YOUR query.
This is the argument for RetVal() function in order to return desired result.
If the argument is "EmployeName" then the function will return the value stored in strEmployeName variable. If the argument is "Account" the same function (RetVal) will return the value stored in strAccount variable.

Take a general look at the functions procedures to understand how this constructions work.
Jan 4 '12 #8

100+
P: 180
The attached file is my program, the password for startup form is 123456. I apply your given code for my previous post in cmbnames and in module. I want to do the same thing with the cmbclass which contains accounting code. that when i clicked one of the accounting code, form (list) will show up and the records containing that accounting code will be filtered. I can't apply the same code with the cmbnames coz i got blank records.
Attached Files
File Type: zip ICIS 2011.zip (2.55 MB, 83 views)
Jan 5 '12 #9

100+
P: 759
Sorry eneyardi.
You will not enjoy what I'll say but that is:
Take the most beautiful women from the world, remove hers brain and you'll obtain... your database.

First of all the user interface looks fine but my computer think few seconds before scroll it. That because the pictures use a lot of processor resources. But this is not a big problem.

The big problem is that you have no idea about databases. Even you don't know why the databases are invented.
From what I see you have some papers ("tables") stored in the same place (your "database"). No link between that tables. No logic. NOTHING.

My advice:
Buy a book. Any one. Is not important if it is for Access, Oracle, Fox or any other language/application. Is necessary to be about databases. And to be for ABSOLUTE beginners.
Read (at least) one book about databases then come back.

It is nothing to do with your database. Sorry.

Teach yourself and come back !
Jan 5 '12 #10

100+
P: 180
How could you make query if you don't have a table?
Jan 6 '12 #11

100+
P: 759
You could not.
I say that what you design is not a database.

Take a look here:
http://bytes.com/topic/access/insigh...ble-structures
Maybe you can understand why I say that yo have not a database.

But, believe me: a book, in your language, is more useful.
Jan 6 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.