filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I did a query on a data warehouse table, but for some of the records in the
[userid]column, there is a SPACE or something invible. I made sure that when
I performed the query that I filtered out those records that are not NULL for
this [userid]column, so I know there has to be SOMETHING (maybe a space, who
knows) there even though it appears to be NULL for some records.

The query made a csv file, to which I have linked in MS Access. Here is
some sample data:

account number number of calls contact code userid
254 1 EN
2440 1 SI
22404 1 DE BGKC
131 1

How can I filter out records that do not begin with an actual letter or
number using a query?

Thanks in advance,
geebee
 
Hey geebee,

If you want to find a record with a space in the very start of the
field, use in the criteria of a query for the field you wish to find
these invisible beginning spaces in (This only looks for one space.
Insert more spaces between the first set of quotes to add more spaces
for it to search for):

Like " "+"*"

If you would like to find any records with spaces in them, use this as
your criteria (Same applys as before about the spaces):

Like "*"+" "+"*"

If your invisible friends are at the very end and you wish only to
search the end for these guys, use this criteria:

Like "*"+" "

If you want you can use this to find any part of any record instead of
having to search for entire string which can be frustrating at times.
To do so, use the code as follows for this example..i want to search
for the letters "CH" in a name field:

Like "*"+"CH"+"*"

This is really a neat function if you want to watch the results in a
list box or something change as you're typing. Simply change the
option in access to go to end of the end of the field instead of
selecting the whole thing. Then, create an OnChange event that uses
the GoToControl function to send the value to a list or button and then
back to the textbox you're typing in. Then have it requery the list.
If the option is set to send it to the end of the field instead of
selecting the whole thing, you can continue typing and watch the
results in the box below change as you type (only since it's looking
for partial strings).

Well, I hope this helps you find the solution to your problem. If you
ever know of anyone that needs a database designer or you yourself
need, my email is charlesguzman at gmail dot com and i'd be quite happy
to help.

Charlie
 
Hey geebee,

If you want to find a record with a space in the very start of the
field, use in the criteria of a query for the field you wish to find
these invisible beginning spaces in (This only looks for one space.
Insert more spaces between the first set of quotes to add more spaces
for it to search for):

Like " "+"*"

If you would like to find any records with spaces in them, use this as
your criteria (Same applys as before about the spaces):

Like "*"+" "+"*"

If your invisible friends are at the very end and you wish only to
search the end for these guys, use this criteria:

Like "*"+" "

If you want you can use this to find any part of any record instead of
having to search for entire string which can be frustrating at times.
To do so, use the code as follows for this example..i want to search
for the letters "CH" in a name field:

Like "*"+"CH"+"*"

This is really a neat function if you want to watch the results in a
list box or something change as you're typing. Simply change the
option in access to go to end of the end of the field instead of
selecting the whole thing. Then, create an OnChange event that uses
the GoToControl function to send the value to a list or button and then
back to the textbox you're typing in. Then have it requery the list.
If the option is set to send it to the end of the field instead of
selecting the whole thing, you can continue typing and watch the
results in the box below change as you type (only since it's looking
for partial strings).

Well, I hope this helps you find the solution to your problem. If you
ever know of anyone that needs a database designer or you yourself
need, my email is charlesguzman at gmail dot com and i'd be quite happy
to help.

Charlie
 
Hey geebee,

If you want to find a record with a space in the very start of the
field, use in the criteria of a query for the field you wish to find
these invisible beginning spaces in (This only looks for one space.
Insert more spaces between the first set of quotes to add more spaces
for it to search for):

Like " "+"*"

If you would like to find any records with spaces in them, use this as
your criteria (Same applys as before about the spaces):

Like "*"+" "+"*"

If your invisible friends are at the very end and you wish only to
search the end for these guys, use this criteria:

Like "*"+" "

If you want you can use this to find any part of any record instead of
having to search for entire string which can be frustrating at times.
To do so, use the code as follows for this example..i want to search
for the letters "CH" in a name field:

Like "*"+"CH"+"*"

This is really a neat function if you want to watch the results in a
list box or something change as you're typing. Simply change the
option in access to go to end of the end of the field instead of
selecting the whole thing. Then, create an OnChange event that uses
the GoToControl function to send the value to a list or button and then
back to the textbox you're typing in. Then have it requery the list.
If the option is set to send it to the end of the field instead of
selecting the whole thing, you can continue typing and watch the
results in the box below change as you type (only since it's looking
for partial strings).

Well, I hope this helps you find the solution to your problem. If you
ever know of anyone that needs a database designer or you yourself
need, my email is charlesguzman at gmail dot com and i'd be quite happy
to help.

Charlie
 
Thanks. I was thinking of counting only records in which the length of
[userid] is more than 3. Will this Len([userid]) >3 work? Will it be as
accurate?

Thanks
 
It might be depending on your needs, but what about the records where
the userID is truley less than three. The invisible space will still
be there and it will still count as a character. I'd still suggest
using the Like function. Let me know if it works out

Charlie
 
The following criteria should find records that start with any number or
letter.

Like "[0-9A-Z]*"
 
Back
Top