Parsing a text field in query

[nhtraven]

Hi

I am attempting to parse a text field in a query by form, where the user will input into a text box a street name. THe field from the table where the query is looking has the whole street address. I did this in a regular query, but using the query by form method i am lost as to how to put this into code. This is what i did i thought if i used the InStr function to compare the two this would work.

dim StreetAdd as String

StreetAdd = InStr(4, table.StreetAddress, Me![StreetAddr], " &quot

i cannot get the information from the table to work. It is empty. I dont need to worry about the number before the streetname.

any ideas? i have searched microsoft and this forum, and cannot find anything pertinent.

thanks
raven

 

[tlbroadbent]

You question is rather confusing to me. Do you want to verify that an address exists in the table, parse an address or build a query to search the table?

Instr returns a numeric value - the position of the search string within the string being searched or 0 if not found. Additionally, you can't query a table with the Instr function. You can use the Dlookup function to verify if an address exists.

StreetAdd = Dlookup("StreetAddress", "Table", "Instr([StreetAddress], '" & Me.StreetAddr & "')>0&quot

You can use the Mid function to parse the address. For example, the following will return the contents of Me.StreetAddr after the first space.

StreetAddress = Mid(Me.StreetAddr,Instr(Me.StreetAddr," &quot+1)

If you want to use an existing query definition to search based on the value entered in the textbox on the form, use the following syntax in the query criteria.
[tt]
Field: StreetAddress
Table: TableName
Criteria: Like "*" & [Forms].[FormName].[StreetAddr] & "*"
[/tt]
Alternate criteria:
Instr([StreetAddress],[Forms].[FormName].[StreetAddr])>0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[nhtraven]

Terry

I am making a query by form, i need to be able to find all records with matching street names. In the table, the field which contains the street address contains this
310 Everywhere lane

I want to query on a street name not the whole address in the field,
Everywhere Lane

In a simple query i used this:

<added field for query> housenum:CInt(Left([StreetAddress],InStr([StreetAddress],&quot; &quot))
In the streetaddress field from tblInput/ criteria
Like &quot;*&quot; & [Please Enter Street Name] & &quot;*&quot;


i tried to use the same in the code for the query by form, and have messed with it and cannot get it to work. I need to parse the street number to use the street name.
fyi > this form has 4 other fields for criteria, and i cannot use the query already made for the street address.

Is this any clearer?

Thanks
Raven

 

[tlbroadbent]

Your criteria should change to use the text box on the form as follows.

Like &quot;*&quot; & Nz([forms].[Formname].[Textboxname],&quot;&quot & &quot;*&quot; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.