searching ID fields...

[kato68]

Ok this is kinda tricky...

I have ID fields for houses, example
67-4-48 1)first number corresponding to city
67-4-49 2)second to street
67-4-50 3)and third to actual house
67-4-51
(In that format.)

I want to be able to run a search on them say houses in neighborhood 67, street 4, houses 48-51. But the problem is when I do this 67-4-5 is included in the results b/c it is a text field. How do I work around this?!?!?!

 

[GumbyD]

Searching id fields

Since you have the data in a standard format you can break it apart and perform the search based on the parts of the ID. The functions you will need to to this are:
Left([string field name],# of Characters you want)
Right([string field name],# of Characters you want)
Mid([string field name],Start position, # of Characters you want)
Cint(expression or field) - this converts the string to an interger
instr([string],"string character you want")

Ok now that you have the functions you can start to build your expressions:
Neighborhood: cint(Left([ID],2))
Street:cint(mid([ID],instr([ID],"-")+1,1))
House:cint(right([id],2))

Then you and parameters in the criteria for each. So under Neighborhood enter [Enter Neighborhood number] and so forth.

Hope it works for you!

GumbyD

 

[kato68]

That concept seems like it will work.

How do I retrieve the house number if I am not sure how long it will be. It could be 1-4 characters long.

Is there a way to retrieve anything right of the second "-"?

 

[GumbyD]

If the length is variable you can still get it because you have the dashes in the string. You can find the location of the second dash and then only get the text that follows it. The expression to use would be:

House:Right([ID],Len([ID])-instr(4,[ID],"-"))

You may need to add one to the instr value to get to the proper starting point so play around with it and see if the data is what you want. If for example the above does not work try:

House:Right([ID],Len([ID])-instr(4,[ID],"-")+1)

The 4 in the instr expression means start at character number 4 in the string and then find the positon for the first dash (in this case). If the other fields are also of variable length then you may have to set the start position to the first dash position plus one character -- instr([id],"-")+1

Good luck

GumbyD

 

[GumbyD]

Sorry just realized that you will need to subtact one not add one.

House:Right([ID],Len([ID])-instr(4,[ID],"-")-1)

GumbyD