Form for entering criteria (1 Viewer)

olxx

Registered User.
Local time
Today, 12:04
Joined
Oct 2, 2009
Messages
52
Hi,

Here´s my problem. I have a form that contains unbound textboxes, comboboxes and a button that runs query. The query criteria fields are linked to the textboxes/comboboxes on the form so the user can enter a criteria. In criteria i use -Like "*" & [textbox] & "*"- so if the textbox is left empty, it is considered as no criteria or any value. Works like a charm. But the problem begins, if the table contains fields that are linked to other tables. Field then just contains ID. Yes, i can use combobox then, but "Like" expression and wildcards will mess up results, because field actually contains just ID, a number. If you remove "Like" and wildcards, it works fine, but then the field can´t be left empty, because query won´t return any results. Anyone faced the same problem? Can someone help?

Olxx
 

wazz

Super Moderator
Local time
Tomorrow, 03:04
Joined
Jun 29, 2004
Messages
1,711
you can work it to use comboboxes. the first column of the combobox would contain the ID but it would not be visible (width = 0). user can select something useful (viewing column 2) but the query would use column 1.

and you would not need to use Like, because it's type is Long Integer.

...And xID = forms!frmName!cboName... <-- uses value from first column of combobox.
 

olxx

Registered User.
Local time
Today, 12:04
Joined
Oct 2, 2009
Messages
52
Thx for reply.

Yes, I tried, it works without Like this way, but as i said, the problem will come when user doesn´t enter/choose value to cbo. You, see, there isn´t option "show all values" or "no criteria on that field". If the cbo is left empty the query will search then with Null as criteria and won´t give any results.
 

John Big Booty

AWF VIP
Local time
Tomorrow, 06:04
Joined
Aug 29, 2005
Messages
8,263
If you are running the query from a button, then use the following at the start of the code to ensure your user selects a value in the Combo box.

Code:
If IsNull(Me.ComboName) Then
     MsgBox "Please select a value from the Combo Box"
     Me.ComboName.SetFocus
     Exit Sub
End If
 

olxx

Registered User.
Local time
Today, 12:04
Joined
Oct 2, 2009
Messages
52
No, no.
The user don´t have to fill it, if he don´t want to add that criteria. There are 6 different fields on form and user can choose any of them. The user can make query based different criterias for only one or for any fields he needs. Let say fields are, example: tboInvoiceNr, cboClient (linked from clients table, actually just ID, but user sees client name), tboItem, tboDate. Now if you do the query with some number on InvoiceNR field and other fields are empty, that cboClient as well, the query won´t give you any result because there isn´t a record in table with Client field empty! It is easy when using textboxes, you can just add Like expression and if it is left empty, it means "any". But combobox which is limited to list, there ´s trouble. If i add Like expression there, it works when its empty, but will give some false results when used. Example with Like expression for that cbo: User picks some value, which is actually a ID number. Let´s say that value´s ID is 12. It returns values with ID=12 and also values with ID lets say 112, because it contains that "12". But the behind that ID 112 is something absolutely different that the user is looking for.
 

Simon_MT

Registered User.
Local time
Today, 19:04
Joined
Feb 26, 2007
Messages
2,177
Here is a search Criteria, I have broken (with line break that will need to be removed) it up for clarity:

Code:
OriginalsSearchCriteria = "[Artist Surname] like '" & .[Surname] & "*" & "'
and [Artist First Name] like '" & .[First] & "*" & "'
and [Orig Type] like '" & .[Type] & "*" & "'
and [Status Flag] like '" & .[Flag] & "*" & "'
and [Orig Status] like '" & .[Status] & "*" & "'
and [Orig Web] like '" & .[Web] & "*" & "'
and [Orig Stock Status] like '" & .[Stock Status] & "*" & "'"

Simon
 

olxx

Registered User.
Local time
Today, 12:04
Joined
Oct 2, 2009
Messages
52
thx Brianwarnock!

You got the point. That example is just what i needed, i´ll just have to dig it little further to work my way around. Big thanks, i wasted 2 d, trying to find a solution.

Cheers,

Olxx
 

Users who are viewing this thread

Top Bottom