query filter error

smile · Jan 5, 2012

Hi, I have a query in my database that filter data from 2 tables with relationship
one to many between tables. I also have a form with text boxes for inputing data to the query.

In the query I have a criteria that takes input from my form. The code in query criteria field is

Between [forms]![frm_laikotarpis].[startdate] And [forms]![frm_laikotarpis].[enddate]

This filter my data by date.

In the other criteria field I want to show records that match the second filtering criteria.

This the query code

[forms]![frm_laikotarpis].[text_field_name]

Everything works until I decided that I still would like to be able to view all records without filtering.

In my form I enter "is not null" into the field that forwards data to my query and I get error that expression is typed incorrect or is too complex. If I enter "is not null" inside criteria field in my query directly is works fine so how to pass this "is not null" with textbox from a form???

P.S. I actually wanted to pass data from a drop down box, but since I don't know how to pass "is not null" decided to try to solve this with textbox.

Thank you.

Galaxiom · Jan 5, 2012

"is not null" from a textbox is a string so your query is trying to match the value to that string (or abandonning it because it isn't a text field that it is comparing.

Is Not Null is an SQL phrase. These cannot be passed as values into a query criteria.

To achieve that you would have to get into VBA and make dynamic queries where the SQL is assembled on demand.

vbaInet · Jan 5, 2012

You can replace this

Code:

[forms]![frm_laikotarpis].[text_field_name]

with this

Code:

([forms]![frm_laikotarpis].[text_field_name] Or 
[forms]![frm_laikotarpis].[text_field_name] Is Null)

In exactly the same line you had it before, not in the OR row.

smile · Jan 5, 2012

([forms]![frm_laikotarpis].[text_field_name] Or
[forms]![frm_laikotarpis].[text_field_name] Is Null)

Thank you, I'm so grateful. You saved my day

I see this works for text box and comb box too.

If I would like to use a list box (I think it passes as many variables as I select right?) to filter say 2 different criteria than I need VBA?

vbaInet · Jan 6, 2012

The entire code won't work for a multiselect listbox. You will need to build your SQL string in code.

smile · Jan 7, 2012

vbaInet said:
The entire code won't work for a multiselect listbox. You will need to build your SQL string in code.

I have no idea how to write VBA, most of stuff I know I have learned from this forum by example.

I can provide a database sample if you could take a look at it.
Sorry if my English is bad.

vbaInet · Jan 8, 2012

I guess it's time for you to learn. We don't need to see your db, thanks for offering.

Search the forum for this solution. It's been discussed many times. If you having difficulty understanding what has been proposed, post us a quick message here.

smile · Jan 9, 2012

I don't know what to search for, can you give me any keywords?

vbaInet · Jan 9, 2012

All discussed here:

http://mymsaccessblog.blogspot.com/2008/03/filtering-using-multi-select-list-boxes.html

Let us know how you get on.

smile · Jan 26, 2012

Hello, I have multiple queries filter by date as criteria and then I open a report from those queries by using a button on my form.

Button code is like this:

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click

Dim stDocName As String

stDocName = "rpt_bknyga"

I need to type the code here right ?

Click to expand...

DoCmd.OpenReport stDocName, acPreview

Exit_Command36_Click:
Exit Sub

Err_Command36_Click:
MsgBox Err.Description
Resume Exit_Command36_Click

End Sub

I have inserted a listbox to my form:

"I want the listbox to look up values in table or query"
I then selected my table that contains the filter criteria I want.
I then named (in properties name) my listbox as "filterlistbox"
I then changed litbox to be extended

List box shows the values from my table, what I do next, I don't quite understand all the code.

vbaInet · Jan 26, 2012

smile, you will need to create a new thread for this new problem.

smile · Jan 26, 2012

vbaInet said:
smile, you will need to create a new thread for this new problem.

But this is the same issue I'm having. I need to filter a query that makes data for my report by using listbox extended (multiselect), and you posted about custom code I need.

I read the articles, and I understand some of the code, but I need directions to help adapting it to my database.

currently I'm using the code inside my queries (yes I have few of them), works fine, except I would like to learn to use multiselect listbox for obvious reasons.

([forms]![frm_laikotarpis].[text_field_name] Or
[forms]![frm_laikotarpis].[text_field_name] Is Null)

smile · Jan 26, 2012

I found a code that allows me to populates text box on my form with comma separated values from the listbox

Private Sub filterlistbox_Click()

Dim strSelected As String
Dim varItem As Variant

With Me.filterlistbox
For Each varItem In .ItemsSelected
strSelected = strSelected & "," & .ItemData(varItem)
Next varItem
Me.Text65 = Mid(strSelected, 2)
End With

End Sub

But for some reason when I pass the criteria on my query using the text box it only works for since selections

[forms]![frm_laikotarpis].[text65]

I also found various posts about how to build the queries using SQL ir VBA, but I have my queries allready made, and I have like 6 of them. I only need to filter what they output, If it is required to build all of the code from 6 queries into VBA code that is not good, not flexible if I need to change anything like add more fields to the queries etc.

Thank you for your time in helping me.

vbaInet · Jan 27, 2012

Seriously smile, you have three great links that completely explain the entire process. I don't think I can explain it any better than that, I can only guide you. But from what I can see you've not followed the entire steps explained in any of the links. Please look at the URL I provided again and follow the examples. Then post what you have done.

query filter error

smile

Registered User.

Galaxiom

Super Moderator

vbaInet

AWF VIP

smile

Registered User.

vbaInet

AWF VIP

smile

Registered User.

vbaInet

AWF VIP

smile

Registered User.

vbaInet

AWF VIP

smile

Registered User.

vbaInet

AWF VIP

smile

Registered User.

smile

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread