query left$

[Dick7Access]

I made a db years ago for my wife that picked area code out of phone number from our main db. Now she wants me to query a area code to see where it is located. How do I query from here"

  Private Sub Form_Current()
  Dim Sname As String
  Dim Result As String
  Sname = Me.ch_ph
  Me.newAreaCode = Left$(Sname, 5)
  End Sub

 

[GohDiamond]

Do you have a table with Contact and Address information in it?

Now that you want to filter or "lookup" by area code , it would be nice if that table had an AreaCode field. If it did you could create a query with a criteria [ENTER AREACODE] in the AreaCode Field and then display Cities where your Contacts with that AreaCode are from.

DoCmd.RunQuery (YourAreaCodeQueryName)

Or you could use a filter on the table itself and get a list of all the contacts data in that filter.

How do you want to display the results?

Cheers
Goh

 

[Dick7Access]

Do you have a table with Contact and Address information in it?

Now that you want to filter or "lookup" by area code , it would be nice if that table had an AreaCode field. If it did you could create a query with a criteria [ENTER AREACODE] in the AreaCode Field and then display Cities where your Contacts with that AreaCode are from.

DoCmd.RunQuery (YourAreaCodeQueryName)

Or you could use a filter on the table itself and get a list of all the contacts data in that filter.

How do you want to display the results?

Cheers
Goh

Thank you very much for answering. This is keep Mama happy db.
There are no contacts. I made a make table query from my main db and only used cities, states and phone numbers. To pick up just the area code I used the left$ to extract just the area code from the phone number. She is in a rehab hospital. What she uses it for is to see where some of her calls are coming from. That kept her happy for a while. She got tired of scrolling to find what city matches the number, so she said I want a button to find the city automatically. After posting this question I did get the idea to make a make table query from the (Dim Result As String). I then made a simple query with a LIKE criteria. Unfortunately all the area codes have brackets around them. Works find as long a I put in the (000). I won’t be back on her computer until tomorrow. I am going to try making a copy of the new table and see if I can change the input mask and get rid of the “( )”.

 

[Dick7Access]

Thanks for making me think GohD,
I am thinking I could possible use mid$, but it has been so long since I have used it, I will have to Google it, and study it.
D7A

 

[Dick7Access]

I have looked at lot of example but can't seem to put it together. Is Mid$ where I should be looking. I have viewed some code that is using loops, but I have never used a loop and don't understand loops yet. Is it necessary to use a loop?

here us an example of 1 record.

Copy Of tblAreaCodeOnly newAreaCode city st (515) Batavia IW

I just need to remove the "( )" Is there a simple way?

 

[kevlray]

I think the replace function will probably be the easiest. Replace(string, searchvalue, replacevalue) (i.e., Replace(field, "(","") Replace(field,")","") )

 

[Dick7Access]

I think the replace function will probably be the easiest. Replace(string, searchvalue, replacevalue) (i.e., Replace(field, "(","") Replace(field,")","") )

Kev,
What did I get wrong?

Private Sub Form_Current()
replace (newAreaCode, "(","")replace(newAreacode,")","") )
'Replace(field, "(","") Replace(field,")","") )
Sub Form_Load()
End Sub

 

[Gasman]

PMFJI,

Put the Replace statement on a line of it's own. It's easier to read.

Or separate them with a ":" (without the quotes)

 

[Dick7Access]

PMFJI,

Put the Replace statement on a line of it's own. It's easier to read.

Or separate them with a ":" (without the quotes)

Thanks for answering. Is it possible that would make it work, as it is not working now?

 

[Gasman]

I would expect it to work, if your fieldname is correct.?

http://www.techonthenet.com/access/functions/string/replace.php

What is it producing?

You do have one too many ")" in the last statement.

 

[Dick7Access]

In a query, As a test this worked ok, took out all the first bracket.

  Exp1: Replace([newAreaCode],"(","")

This gave me error: Invalid syntax.

  Exp1: Replace([newAreaCode],"(","")  replace([newAreaCode],")","")

This gave me error: Invalid syntax.

  Exp1: Replace([newAreaCode],"(","") : Replace([newAreaCode],")","")

This gave me error all the way down the Exp1 column.

  Exp1: Replace([newAreaCode],"(","")  and  replace([newAreaCode],")","")

What am I missing?

 

[Dick7Access]

I have read everything I could find on replace function and tried at least 10 more combination. I can remove the first { ( } or the last { ) } but can't do them both at the same time. If I don't come up with the code pretty soon my wife is going to beat me. <G>

 

[Dick7Access]

I achieved what I wanted but and double.
I did a replace of the front { ( } and then made a table from it.
I then did a replace off that table and removed the back end { ) }

Just couldn't get both the same time.

 

[WayneRyan]

On a phone so this is brief

Replace(replace(area code, "(", ""), ")","")

Hth,
Wayne

 

[Gasman]

You've gone from putting it in code to trying to use it in a query as a function.

I would have suggested creating your own function with the requisite code and then using that in the query, but WayneRyan has shown us the way to use the standard function twice.

Very nifty.

 

[DennisOJensen]

[FONT="Courier New"]Private Sub Form_Current()
    Dim Sname As String
    Dim Result  As String

    Sname = Me.ch_ph
    Me.newAreaCode = Left$(Sname, 5)
End Sub[/FONT]

Okay the original question if I am right is how do you make a query using the Area Code you just acquired in order to get the state associated with that Area Code in the format of "(###)"

[FONT="Courier New"]Presumed Table Layout

   [B]PhoneNumber[/B]    City   State[/FONT]

I assume the key to the table is PhoneNumber as both City and State would not be unique values.

[FONT="Courier New"]
Dim sqlQuery  As String
Dim CityState As String
Dim dbs       As Database
Dim rs        As Recordset

Set dbs = CurrentDb

sqlQuery = ""
sqlQuery = sqlQuery + "SELECT (City + ", " + State) AS CityState"
sqlQuery = sqlQuery + "  FROM tablename
sqlQuery = sqlQuery + " WHERE Left(PhoneNumber, 5) = " 
sqlQuery = sqlQuery + Me.newAreaCode.Value

Set rs = dbs.OpenRecordset(sqlQuery)

rs.MoveFirst
If Not rs.EOF Then
    CityState =  rs("CityState") 
Else
    CityState = "Not Found"
End If
[/FONT]

That should give you a head start on implementing what you requested

 

[Dick7Access]

[FONT=Courier New]Private Sub Form_Current()
    Dim Sname As String
    Dim Result  As String

    Sname = Me.ch_ph
    Me.newAreaCode = Left$(Sname, 5)
End Sub[/FONT]

Okay the original question if I am right is how do you make a query using the Area Code you just acquired in order to get the state associated with that Area Code in the format of "(###)"

[FONT=Courier New]Presumed Table Layout

   [B]PhoneNumber[/B]    City   State[/FONT]

I assume the key to the table is PhoneNumber as both City and State would not be unique values.

[FONT=Courier New]
Dim sqlQuery  As String
Dim CityState As String
Dim dbs       As Database
Dim rs        As Recordset

Set dbs = CurrentDb

sqlQuery = ""
sqlQuery = sqlQuery + "SELECT (City + ", " + State) AS CityState"
sqlQuery = sqlQuery + "  FROM tablename
sqlQuery = sqlQuery + " WHERE Left(PhoneNumber, 5) = " 
sqlQuery = sqlQuery + Me.newAreaCode.Value

Set rs = dbs.OpenRecordset(sqlQuery)

rs.MoveFirst
If Not rs.EOF Then
    CityState =  rs("CityState") 
Else
    CityState = "Not Found"
End If
[/FONT]

That should give you a head start on implementing what you requested

Sorry if I wasn’t clear enough. The problem was removing the bracket " ( " " ) " from around the phone number area code. See post #13.

 

[Dick7Access]

My Telephone area code number db that filters by an alpha/numeric query on open. Works fine.
The minute I filter by state it leaves that filter in the “filter” and “ordered by” in the forms property sheet. My button to re-filter to all records will not filter alpha/numeric query. I have spent over 2 hours goggling, but have come up with nothing. Any help will be appreciated.

 

[DennisOJensen]

Okay to get just the area code (I am using an Integer just in case if you do not want an Integer then drop the CInt and change AreaCode to String) from your character phone number string

[FONT="Courier New"]Dim AreaCode As Integer

AreaCode = CInt(Mid(PhoneNumber, 2, 3))[/FONT]

Okay the rest of that appears to be different issue than you initially posted and it is rather unclear as to what you are doing and thus hard to say why it is not working

 

[Dick7Access]

Still haven’t solved the filter remaining on form property sheet, after a query. What settings should I have on the form’s [filter on load] yes or no, and [Order by on load] yes or no? I can figure out what yes or no does.