Show All records in Access Query for null entry (1 Viewer)

Ksabai

Registered User.
Local time
Yesterday, 22:00
Joined
Jul 31, 2017
Messages
104
Iam Using the SQL LIKE "*" & Forms!YourFormName.YourComboBoxName & "*" in the Criteria of a Access Query. i have 4 controls to be searched like this.

I need to search a field with Numbers. While Searching for "1" it returns 18,16,12,13 instead of just returning "1"

if i use =Forms!YourFormName.YourComboBoxName OR Forms!YourFormName.YourComboBoxName IS NULL

The Saved Query Gets Populated and when i open a report is shows ODBC Failed. Need help seriously here as project is based on searching thru number fields.
 

sxschech

Registered User.
Local time
Yesterday, 22:00
Joined
Mar 2, 2010
Messages
661
If you are using like and wildcards, then you would expect to get numbers containing 1 per your example. If you need an exact match, do not use like and wildcards for that criteria.
 

Ksabai

Registered User.
Local time
Yesterday, 22:00
Joined
Jul 31, 2017
Messages
104
Can u please then tell me what i shld do
 

theDBguy

I’m here to help
Local time
Yesterday, 22:00
Joined
Oct 29, 2018
Messages
10,192
Hi. What is your BE? Is it SQL Server? Are you querying a linked table or a view?
 

Ksabai

Registered User.
Local time
Yesterday, 22:00
Joined
Jul 31, 2017
Messages
104
its Sql Server, iam querying a linked table
 

arnelgp

error reading drive A:
Local time
Today, 13:00
Joined
May 7, 2009
Messages
9,323
something like:

select * from linked_table where [field]=iif(trim(Forms!YourFormName.YourComboBoxName & "") = "", [field], Forms!YourFormName.YourComboBoxName)
 

Ksabai

Registered User.
Local time
Yesterday, 22:00
Joined
Jul 31, 2017
Messages
104
Hi Arnelgp, i tried string thru TempVars the above code and it says Error 2465 as below

error number : 2465
Error Source: mod_DB_Variable\cmd_LocateFile_Click
Error Description: Database Cant find the field '|1' reffered to in your expression

Can u help
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 00:00
Joined
Feb 28, 2001
Messages
16,835
When you see that message that calls out "|1" it is because you did one of two things. (A) Spelled the name of the field wrong or (B) forgot where it is actually located. That message tells you that one of the fields isn't in the selected table or query. NOTE that it could have been in ANY FIELD referenced in the query, whether in a SELECT sub-clause, a WHERE clause, an ORDER BY, or anyplace else where a field-name reference is permitted.

When you say "searching through number fields" - the question is whether those fields are of a text data type or a numeric data type - because LIKE doesn't work so well when searching numbers. It WOULD work for searching through digit strings (numbers represented as separate digits for display purposes). Please clarify.
 

Ksabai

Registered User.
Local time
Yesterday, 22:00
Joined
Jul 31, 2017
Messages
104
it is searching thru Numbers and not digit strings. let me check my reference. in the meanwhile pls advice if there is any other alternative
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 00:00
Joined
Feb 28, 2001
Messages
16,835
OK, "LIKE" is really a text-oriented comparison operator that really isn't that good for number testing.

If looking for specific numbers you use WHERE field = Value as a comparator. If looking for a continuous range for numbers, you use WHERE field BETWEEN x AND y (which is an inclusive range; i.e. x and y are included in the range). When looking for numbers within a list, you use WHERE field IN (1,2,3...8,9) i.e. a short enumerated list of numbers.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom