How to filter query using another data field?

[eneyardi]

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?

[Mihail]

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.

[eneyardi]

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";"22 3"
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

[Mihail]

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:

YourVar = Combo238
YourVar = Combo245
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:

YourVar = Combo238
DoCmd.OpenQuery ("queryName")
YourVar = Combo245
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.

Public strEmployeName As String, strAccount As String

Public Function RetVal(ParameterIdentifier As String)
    Select Case ParameterIdentifier
        Case "EmployeName"
            RetVal = strEmployeName
        Case "Account"
            RetVal = strAccount
        Case Else
            Stop 'for debug purpose
    End Select
End Function

Now, in the query's criteria row type:
- for field with employes names: RetVal("Employe Name")
- 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.

[eneyardi]

still it shows no records after i put retval("Employe Name") 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

[eneyardi]

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.

[eneyardi]

In Case "EmployeNam e" is this data field name in query?

[Mihail]

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. "EmployeNam e" 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 "EmployeNam e" 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.

[eneyardi]

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.

[Mihail]

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 !

[eneyardi]

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

[Mihail]

You could not.
I say that what you design is not a database.

Take a look here:

http://bytes.com/topic/access/insights/585228-database-normalization-table-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.