Filtering a query using a variable

[Shas91]

Hi !

I'm bit frustrated, I just can't select all in a query. It is allways hard to explain so I have made a small access program to show what I mean.

If You start frmProject_Filter and chose Adam Adamsson in the dropdown meny and press "Start Filtering" It start up start up and show a filtered qryProject for two seconds, just as it should. And it works like charm with all manager in the dropdown meny so far so god !

The problem is when it comes to show all... It shows nothing... I have tried "like *" , "*" and much more. If You use the asterix directly in the query it works but not as a variable...

It's problebly a very litle fault but it still a fault... Is there anyone who can assist and save my weekend...

// Shas

 

[Ranman256]

instead of param query, make a continuous form that shows all data,
put UNBOUND controls on the header area for it to filter the data,
user then clicks FIND btn which checks the controls on what to filter:

Public Sub btnFIND_Click()
Dim sSql As String, sWhere As String

sWhere = "1=1"

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

 'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif
END SUB

 

[Gasman]

That does not even work?

 

[Ranman256]

did you use BUILDER to get the correct control path?
misspellings can happen if you type it manually or the field doesn't exist.

 

[Gasman]

Here is one way?

 

[Shas91]

Hi !

First sorry for my late respons... As You alredy has notice, "Formulär" is a swedish translation for "forms". Its rather stupid of access to make country versions....

I saw Your code... I have never seen that... So easy and it workes perfect.

Thank You

// Stefan

 

[Shas91]

instead of param query, make a continuous form that shows all data,
put UNBOUND controls on the header area for it to filter the data,
user then clicks FIND btn which checks the controls on what to filter:

Public Sub btnFIND_Click()
Dim sSql As String, sWhere As String

sWhere = "1=1"

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif
END SUB

Hi thanks for Your effort... Gasman solved this a bit easier this time

 

[sonic8]

As You alredy has notice, "Formulär" is a swedish translation for "forms". Its rather stupid of access to make country versions....

It's not the core topic of this thread, nonetheless:
I've got only experience with this with the German localization of Access. There the problem can be (mostly) avoided by typing the English expressions. E.g. you type in "Forms", Access will automatically translate this to the German "Formulare" but it then will also be able to re-translate it back to English. However, if you directly type in "Formulare", Access is sometimes not able to translate this back to "Forms".

 

[Shas91]

It's not the core topic of this thread, nonetheless:
I've got only experience with this with the German localization of Access. There the problem can be (mostly) avoided by typing the English expressions. E.g. you type in "Forms", Access will automatically translate this to the German "Formulare" but it then will also be able to re-translate it back to English. However, if you directly type in "Formulare", Access is sometimes not able to translate this back to "Forms".

 

[Shas91]

Hi I'm always trying to use the english expression with thoose problem You describe as a fact. Well it is as it is...