Criteria

  • Thread starter Thread starter lewy the fly via AccessMonster.com
  • Start date Start date
L

lewy the fly via AccessMonster.com

How can I set the citeria to search on the second value of a string. ie I
have a series on four digit numbers formated as text 1201 2201 3201 2204 2401
2502 etc etc and I wish to select all the record with a matching second
number in aselect querie.

Regards

Lewy
 
In design view add another output field like this ---
Second_Set: Mid([YourTextField],5,4)

Use [Enter 2nd string criteria]

In SQL view use this --
WHERE Mid([YourTextField],5,4) = [Enter 2nd string criteria]
 
Karl,
I have tried it and cant get it to work.

Regards

Lewy

KARL said:
In design view add another output field like this ---
Second_Set: Mid([YourTextField],5,4)

Use [Enter 2nd string criteria]

In SQL view use this --
WHERE Mid([YourTextField],5,4) = [Enter 2nd string criteria]
How can I set the citeria to search on the second value of a string. ie I
have a series on four digit numbers formated as text 1201 2201 3201 2204 2401
Click to expand...
[quoted text clipped - 4 lines]
Lewy
Click to expand...
Click to expand...
 
How can I set the citeria to search on the second value of a string. ie I
have a series on four digit numbers formated as text 1201 2201 3201 2204 2401
2502 etc etc and I wish to select all the record with a matching second
number in aselect querie.

Regards

Lewy
Click to expand...

It sounds like you have one text field containing multiple numbers,
separated by blanks; and that you want to search the table for the
second of those numbers. Am I understanding you correctly?

If so, you're having difficulty because you're violating a very basic
principle of database design: fields should be ATOMIC.

If you have a one to many relationship from this table to a series of
numbers, model it *as a one to many relationship*, with TWO tables,
and one record for each of these numbers.

With your current hodgepodge, you will almost certainly need some VBA
code to extract the desired value. Untested air code: put the
following into a new Module; save the module as basQSplit:

Public Function QSplit(vField As Variant, _
iWhich As Integer, _
sDelim As String) As Variant
Dim vAns() As Variant
vAns = Split(vField, sDelim)
If UBound(vAns) >= iWhich - 1 Then
QSplit = vAns(iWhich - 1)
Else
MsgBox "Too few values in array"
QSplit = Null
End If
End Function

This will use the builtin Split function to return any selected
substring. Call it in a query with

SecondNumber: QSplit([yourfieldname], 2, " ")

Use "," if your numbers are delimited by commas instead of blanks,
etc.

John W. Vinson[MVP]
 
John,
Thanks after cecking the microsoft site and reading about the MID function I
got it to work the info I had been given was almost correct. The number were
not separated by blanks I need to tell it to return the second number and
only one number so I have MID([Unit Number]2,1) works fine.
I may be dumb but what do you mean by Atomic?

Regards
Lewy

John said:
How can I set the citeria to search on the second value of a string. ie I
have a series on four digit numbers formated as text 1201 2201 3201 2204 2401
Click to expand...
[quoted text clipped - 4 lines]
Lewy
Click to expand...

It sounds like you have one text field containing multiple numbers,
separated by blanks; and that you want to search the table for the
second of those numbers. Am I understanding you correctly?

If so, you're having difficulty because you're violating a very basic
principle of database design: fields should be ATOMIC.

If you have a one to many relationship from this table to a series of
numbers, model it *as a one to many relationship*, with TWO tables,
and one record for each of these numbers.

With your current hodgepodge, you will almost certainly need some VBA
code to extract the desired value. Untested air code: put the
following into a new Module; save the module as basQSplit:

Public Function QSplit(vField As Variant, _
iWhich As Integer, _
sDelim As String) As Variant
Dim vAns() As Variant
vAns = Split(vField, sDelim)
If UBound(vAns) >= iWhich - 1 Then
QSplit = vAns(iWhich - 1)
Else
MsgBox "Too few values in array"
QSplit = Null
End If
End Function

This will use the builtin Split function to return any selected
substring. Call it in a query with

SecondNumber: QSplit([yourfieldname], 2, " ")

Use "," if your numbers are delimited by commas instead of blanks,
etc.

John W. Vinson[MVP]
Click to expand...
 
John,
Thanks after cecking the microsoft site and reading about the MID function I
got it to work the info I had been given was almost correct. The number were
not separated by blanks I need to tell it to return the second number and
only one number so I have MID([Unit Number]2,1) works fine.
I may be dumb but what do you mean by Atomic?
Click to expand...

Sorry... that's database jargon. Not knowing special jargon is NOT
dumb (ignorance is curable, dumb goes to the bone! <g>)

An "Atomic" field is one which cannot be divided into smaller
portions, each of which has its own independent meaning. A classic
example of a composite field (one which is not atomic) would be an
"Intelligent Key" like "ABC-1231-10/23/2006" where ABC is the
department number, 1231 is a sequential number, and the date is the
date of a transaction. Storing this all in one field maybe made sense
when the identifier needed to go on a file folder label, but doesn't
in a database!

If the second digit of your Unit Number has a meaning independent of
the rest of the unit number - then it may be that your unit number is
a composite of two or more different attributes. It's much easier to
take two or three fields and concatenate them for display purposes
than it is to split up one field into components!

John W. Vinson[MVP]
 
John,
Thanks again, without knowing the term thats pretty much the way i have
things working.
I run a kitchen manufacturing busines on the Gold Coast , Australia and the
core of our business
is in unit development work. This particular part of the data base is to sort
vanity design and colour into the
different unit types, the reason for wanting to sort by the second digit is
that we are manufacturing one floor level at a time and in this case Unit
2304 is building 2 level 3 unit 4 so we search on the secong digit to find
all units by floor level, apart from that we do all other searching by the
entire number.


John said:
John,
Thanks after cecking the microsoft site and reading about the MID function I
got it to work the info I had been given was almost correct. The number were
not separated by blanks I need to tell it to return the second number and
only one number so I have MID([Unit Number]2,1) works fine.
I may be dumb but what do you mean by Atomic?
Click to expand...

Sorry... that's database jargon. Not knowing special jargon is NOT
dumb (ignorance is curable, dumb goes to the bone! <g>)

An "Atomic" field is one which cannot be divided into smaller
portions, each of which has its own independent meaning. A classic
example of a composite field (one which is not atomic) would be an
"Intelligent Key" like "ABC-1231-10/23/2006" where ABC is the
department number, 1231 is a sequential number, and the date is the
date of a transaction. Storing this all in one field maybe made sense
when the identifier needed to go on a file folder label, but doesn't
in a database!

If the second digit of your Unit Number has a meaning independent of
the rest of the unit number - then it may be that your unit number is
a composite of two or more different attributes. It's much easier to
take two or three fields and concatenate them for display purposes
than it is to split up one field into components!

John W. Vinson[MVP]
Click to expand...
 
the reason for wanting to sort by the second digit is
that we are manufacturing one floor level at a time and in this case Unit
2304 is building 2 level 3 unit 4 so we search on the secong digit to find
all units by floor level, apart from that we do all other searching by the
entire number.
Click to expand...

Then it's a classic case of a non-atomic, composite field.

The unit ID field SHOULD NOT EXIST.

You should instead have a field for Building, a field for Level, a
field for Unit. You can make all three fields a joint Primary Key, and
you can concatenate them for display purposes, e.g. with a calculated
field

ShowUnit: [Building] & [Level] & Format([Unit], "00")

With your current design, you'll crash and burn if you ever have a
tenth building or a tenth floor. Is 11104 Building 11, first floor? Or
Building 1, eleventh floor? Or do you simply turn down that contract?

John W. Vinson[MVP]
 
Back
Top