criteria characters

[mmap hq]

Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I have
been unable to find those definitions anywhere in access help :-(. Where are
they defined? TIA

 

[John Spencer]

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.

 

[Marshall Barton]

Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I have
been unable to find those definitions anywhere in access help :-(. Where are
they defined?

Enter wildcard in Access Help - Index

 

[ted medin]

Ok thanks I spent a lot of time looking for that info. Now I have a problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.

Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[John Nurick]

See help for the Split function.

Ok thanks I spent a lot of time looking for that info. Now I have a problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.

Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[John Spencer]

My first idea would be to split the data into its components and store the
components in individual fields.

IF you can't do that and you always want to match on only the first segment,
then you need to split that off using Left and Instr functions.

Left([YourField],Instr(1,[YourField],";")) LIKE "*06#*[a-z]#1;"

Your other choice would be to build a function in VBA to examine this or
perhaps you could use the Regular expression stuff at John Nurick's site.

http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

Ok thanks I spent a lot of time looking for that info. Now I have a
problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.

Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[ted medin]

Problem is when I added the split function to our where for the query, query
said split is unknown.
Following it the first part of the where of the docmd.openreport ...

"(((Sheet1.[Alternate Street]) Is Null)And ((Sheet1.[MEM#]) < 700)And
((Sheet1.[projects sched]) Is Null Or Not ((Sheet1.[projects sched]) Like
"*06#*[-a-zA-Z][1-6]1;*"Or (Sheet1.[proj" ...

What we did was change the 'Sheet1.[projects sched]' to
'split(Sheet1.[projects sched],';',-1)'

and that produced the split not found or something like that

Spent a lot of time trying lots of things but no banana :-(. Any help would
be appreciated. TIA

See help for the Split function.

Ok thanks I spent a lot of time looking for that info. Now I have a problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.


Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[ted medin]

Your regexpression seems to have many more options than ms has in wildcard.
Starting to look like UNIX. Could you tell me where they are defined? TIA

 

[John Nurick]

If you have data like this in [Projects Sched]
04730611X;0486q61;0535b51;0537c61;
you should be able to extract and test the first "subfield" using
something like this. The (0) specifies the first item from the array
returned by Split():

... OR (Split(Sheet1.[Projects Sched], ";")(0)
LIKE "*06#*[-a-zA-Z][1-6]1")

which will be true if the first subfield begins with any character, then
three digits beginning with 06, then any other character, then a letter
or hyphen, then a number from 1 to 6 and finally the number 1.

Problem is when I added the split function to our where for the query, query
said split is unknown.
Following it the first part of the where of the docmd.openreport ...

"(((Sheet1.[Alternate Street]) Is Null)And ((Sheet1.[MEM#]) < 700)And
((Sheet1.[projects sched]) Is Null Or Not ((Sheet1.[projects sched]) Like
"*06#*[-a-zA-Z][1-6]1;*"Or (Sheet1.[proj" ...

What we did was change the 'Sheet1.[projects sched]' to
'split(Sheet1.[projects sched],';',-1)'

and that produced the split not found or something like that

Spent a lot of time trying lots of things but no banana :-(. Any help would
be appreciated. TIA

See help for the Split function.

Ok thanks I spent a lot of time looking for that info. Now I have a problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.


Have been trying to find what characters mean in a 'like' query. See
that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[John Nurick]

Without context I'm not going to guess what this means.

 

[John Spencer]

John,
Pardon me, but I've never been able to get SPLIT to work in a query.

If you have data like this in [Projects Sched]
04730611X;0486q61;0535b51;0537c61;
you should be able to extract and test the first "subfield" using
something like this. The (0) specifies the first item from the array
returned by Split():

... OR (Split(Sheet1.[Projects Sched], ";")(0)
LIKE "*06#*[-a-zA-Z][1-6]1")

which will be true if the first subfield begins with any character, then
three digits beginning with 06, then any other character, then a letter
or hyphen, then a number from 1 to 6 and finally the number 1.

Problem is when I added the split function to our where for the query,
query
said split is unknown.
Following it the first part of the where of the docmd.openreport ...

"(((Sheet1.[Alternate Street]) Is Null)And ((Sheet1.[MEM#]) < 700)And
((Sheet1.[projects sched]) Is Null Or Not ((Sheet1.[projects sched]) Like
"*06#*[-a-zA-Z][1-6]1;*"Or (Sheet1.[proj" ...

What we did was change the 'Sheet1.[projects sched]' to
'split(Sheet1.[projects sched],';',-1)'

and that produced the split not found or something like that

Spent a lot of time trying lots of things but no banana :-(. Any help
would
be appreciated. TIA

See help for the Split function.



Ok thanks I spent a lot of time looking for that info. Now I have a problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields
above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any
ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets [] "A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.


Have been trying to find what characters mean in a 'like' query.
See
that
[0-9] means numeric characters 0 thru 9. So what does *, # ...
mean. I
have been unable to find those definitions anywhere in access help :-(.
Where are they defined? TIA

 

[John Nurick]

You're right. I must have been thinking of some other language. It needs
a wrapper function, which can be as simple as

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)
End Function

 

[ted medin]

Without context I'm not going to guess what this means.

Went to your site (i think):
j.nurick.dial.pipex.com/code/vbregex/rgxextract.htm

Looking @ reg expression I see: \ + which are not in the ms wildcard
definition? Perhaps there are others.

 

[John Nurick]

Yes, the Like and LIKE operators in VBA and SQL are pale shadows of what
a regular expression engine can do. There's a link on the page you
mentioned to more information about regular expressions: see
http://www.j.nurick.dial.pipex.com/Code/vbRegex/MoreAboutRegexp.htm

 

[ted medin]

Ok we have a problem with the split function. Is it possible to specify [^;]
for the delimiter? What we would like is the start of the line (^) or ';' as
the delimiter. The start of our text string does not have a ';' but all the
rest of the items are surrounded with ';' delimiters? TIA

See help for the Split function.

Ok thanks I spent a lot of time looking for that info. Now I have a
problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets []
"A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.


Have been trying to find what characters mean in a 'like' query. See that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help
:-(.
Where are they defined? TIA

 

[John Nurick]

The VBA Split() Delimiter argument takes a string, not a regex. In any
case, most regex engines interpret
[^;]
as "any character except ;", which can't be what you mean.

But if you pass your sample data
04730611X;0486q61;0535b51;0537c61;
to Split() you'll find it starts at the beginning of the line and
returns a 5-element array, with elements
0 04730611X
1 0486q61
2 0535b51
3 0537c61
4 <empty>

In

Ok we have a problem with the split function. Is it possible to specify [^;]
for the delimiter? What we would like is the start of the line (^) or ';' as
the delimiter. The start of our text string does not have a ';' but all the
rest of the items are surrounded with ';' delimiters? TIA

See help for the Split function.

Ok thanks I spent a lot of time looking for that info. Now I have a
problem:
We have this text line: 04730611X;0486q61;0535b51;0537c61;
All of the fields are ended with a ; Therefore there are 4 fields above.

Our 'like' criteria is: *06#*[a-z]#1;*
Unfortunately that criteria spans the first 2 fields :-(.
We would like to end @ the first ; not end @ the second ; Got any ideas?

Try Looking up Wildcard in the online help.

* = any number of characters (including zero characters)
?= any single character
# = any number character
! = Not and is used in conjuction with the square brackets []
"A[!a-e]*"
would be Starts with A and second character not in the range A to e, and
followed by zero or more characters.


Have been trying to find what characters mean in a 'like' query. See
that
[0-9] means numeric characters 0 thru 9. So what does *, # ... mean. I
have been unable to find those definitions anywhere in access help
:-(.
Where are they defined? TIA