Using InStr on form control

Hi,

I have this bit of code:

Dim myText As Variant, i As Long

    If Not IsNull(Me.mktAreaCombo) Or Me.mktAreaCombo = "" Then
        If InStr(Me.mktAreaCombo, "...") > 0 Then
            MsgBox "... Detected!"
            myText = Split(Me.mktAreaCombo, "...")

            For i = LBound(myText) To UBound(myText)
                MsgBox myText(i)
            Next i
        Else
            strWhere = strWhere & "([mktArea] = " &
Me.Controls("mktAreaCombo") & ") AND "
            MsgBox "No ..."
        End If
        Exit Sub
    End If

Im trying to check if "..." exists in what the user has entered in the
control called Me.mktAreaCombo

Even when i MsgBox out the control value it equals "1...2" (my
testing) however the InStr function always returns 0.

If i replace InStr(Me.mktAreaCombo, "...") with InStr("1...2", "...")
it works fine, however not when its reading it from a variable which
also equals "1...2"?

Am i missing something obvious?

Thanks

0
Veus
4/16/2007 4:00:08 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
728 Views

Similar Articles

[PageSpeed] 59

Veus wrote:
>I have this bit of code:
>
>Dim myText As Variant, i As Long
>
>    If Not IsNull(Me.mktAreaCombo) Or Me.mktAreaCombo = "" Then
>        If InStr(Me.mktAreaCombo, "...") > 0 Then
>            MsgBox "... Detected!"
>            myText = Split(Me.mktAreaCombo, "...")
>
>            For i = LBound(myText) To UBound(myText)
>                MsgBox myText(i)
>            Next i
>        Else
>            strWhere = strWhere & "([mktArea] = " &
>Me.Controls("mktAreaCombo") & ") AND "
>            MsgBox "No ..."
>        End If
>        Exit Sub
>    End If
>
>Im trying to check if "..." exists in what the user has entered in the
>control called Me.mktAreaCombo
>
>Even when i MsgBox out the control value it equals "1...2" (my
>testing) however the InStr function always returns 0.
>
>If i replace InStr(Me.mktAreaCombo, "...") with InStr("1...2", "...")
>it works fine, however not when its reading it from a variable which
>also equals "1...2"?


Add another MsgBox to display the value of the combo box.  I
think you might find it is not 1...2

-- 
Marsh
MVP [MS Access]
0
Marshall
4/16/2007 4:43:16 PM
On 16 Apr, 17:43, Marshall Barton <marshbar...@wowway.com> wrote:
> Veus wrote:
> >I have this bit of code:
>
> >Dim myText As Variant, i As Long
>
> >    If Not IsNull(Me.mktAreaCombo) Or Me.mktAreaCombo = "" Then
> >        If InStr(Me.mktAreaCombo, "...") > 0 Then
> >            MsgBox "... Detected!"
> >            myText = Split(Me.mktAreaCombo, "...")
>
> >            For i = LBound(myText) To UBound(myText)
> >                MsgBox myText(i)
> >            Next i
> >        Else
> >            strWhere = strWhere & "([mktArea] = " &
> >Me.Controls("mktAreaCombo") & ") AND "
> >            MsgBox "No ..."
> >        End If
> >        Exit Sub
> >    End If
>
> >Im trying to check if "..." exists in what the user has entered in the
> >control called Me.mktAreaCombo
>
> >Even when i MsgBox out the control value it equals "1...2" (my
> >testing) however the InStr function always returns 0.
>
> >If i replace InStr(Me.mktAreaCombo, "...") with InStr("1...2", "...")
> >it works fine, however not when its reading it from a variable which
> >also equals "1...2"?
>
> Add another MsgBox to display the value of the combo box.  I
> think you might find it is not 1...2
>
> --
> Marsh
> MVP [MS Access]


Nope, I added the MsgBox before:
MsgBox Me.mktAreaCombo.Value

However it prints out 1...2 but the InStr isnt true.

0
Veus
4/17/2007 8:42:14 AM
On 17 Apr, 09:42, Veus <ollyculverho...@gmail.com> wrote:
> On 16 Apr, 17:43, Marshall Barton <marshbar...@wowway.com> wrote:
>
>
>
> > Veus wrote:
> > >I have this bit of code:
>
> > >Dim myText As Variant, i As Long
>
> > >    If Not IsNull(Me.mktAreaCombo) Or Me.mktAreaCombo = "" Then
> > >        If InStr(Me.mktAreaCombo, "...") > 0 Then
> > >            MsgBox "... Detected!"
> > >            myText = Split(Me.mktAreaCombo, "...")
>
> > >            For i = LBound(myText) To UBound(myText)
> > >                MsgBox myText(i)
> > >            Next i
> > >        Else
> > >            strWhere = strWhere & "([mktArea] = " &
> > >Me.Controls("mktAreaCombo") & ") AND "
> > >            MsgBox "No ..."
> > >        End If
> > >        Exit Sub
> > >    End If
>
> > >Im trying to check if "..." exists in what the user has entered in the
> > >control called Me.mktAreaCombo
>
> > >Even when i MsgBox out the control value it equals "1...2" (my
> > >testing) however the InStr function always returns 0.
>
> > >If i replace InStr(Me.mktAreaCombo, "...") with InStr("1...2", "...")
> > >it works fine, however not when its reading it from a variable which
> > >also equals "1...2"?
>
> > Add another MsgBox to display the value of the combo box.  I
> > think you might find it is not 1...2
>
> > --
> > Marsh
> > MVP [MS Access]
>
> Nope, I added the MsgBox before:
> MsgBox Me.mktAreaCombo.Value
>
> However it prints out 1...2 but the InStr isnt true.

THis has been solved. It was infact AutoCorrect which was
automatically converting the three dots into the ellipsis symbol.

0
Veus
4/17/2007 1:01:42 PM
Veus wrote:

>On 17 Apr, 09:42, Veus <ollyculverho...@gmail.com> wrote:
>> > >I have this bit of code:
[]
>> > >        If InStr(Me.mktAreaCombo, "...") > 0 Then
>> > >            MsgBox "... Detected!"
[]
>> > >Im trying to check if "..." exists in what the user has entered in the
>> > >control called Me.mktAreaCombo
>>
>> > >Even when i MsgBox out the control value it equals "1...2" (my
>> > >testing) however the InStr function always returns 0.
[]
>THis has been solved. It was infact AutoCorrect which was
>automatically converting the three dots into the ellipsis symbol.


Excellent work!  Tracking that down is not intuitive and
well worth remembering.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/17/2007 2:21:40 PM
Reply:

Similar Artilces:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Using different form strings to filter
Hello. Is there a way to code a query to have it use a filter string if a form is open, or ignore it if the filter is not open? Example: Query Name: Query1 Form: Form1 and Form2 Query fields: Field1 and Field2 I have a string in field 1 and 2 Forms!Form1!Field1 in the first field of the query, and Forms!Form1! Field2 in the second field. Can I also have Forms!Form2!Field1 in the query as well? I have tried this, but a box pops up and asks for the information from form 2 when it is not open. Thank you. hi, nybaseball22@gmail.com wrote: > Hello. Is there a way to code a query to...

Cannot set menu in MFC ActiveX control
I am dynamically creating a CMenu object and trying to assign it to my ActiveX control. Windows balks and gives me a 1436 error, something to effect that a child window cannot have a menu. It is not a child window, it is the instance of COleControl, the top level window. (I verified this by inspecting m_parentHwnd.) I am trying to assign the menu within the OnCreate event at which time the control has a valid handle, and have tried it before and after the call to the parent OnCreate. Does anyone know the magic words to make this work? Thanks On Mon, 8 Dec 2008 16:22:02 -0500, Duane A. C...

Using Ghost as OS Backup
I attended a class a while back where the exchange admins said they used Ghost to clone the OS drive. I bought the product and it says that during the clone process it temporarily removes the server from the domain. Has anyone done this or had any problems? Thanks, Fred On Mon, 21 Nov 2005 05:26:28 -0600, "Fred T" <fredt2@mail.com> wrote: >I attended a class a while back where the exchange admins said they used >Ghost to clone the OS drive. I bought the product and it says that during >the clone process it temporarily removes the server from the domain. ...

using beforeprint
I am trying to get an application to append a file on a sheet printout. I am using Workbook_BeforePrint. It prints fine, but doesn't seem t execute the sub. What's wrong -- Message posted from http://www.ExcelForum.com Hi you may post your existing code :-) Also check that you have put this code in your workbook module ('ThisWorkbook') -- Regards Frank Kabel Frankfurt, Germany > I am trying to get an application to append a file on a sheet > printout. I am using Workbook_BeforePrint. It prints fine, but > doesn't seem to execute the sub. What's wrong?...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

MapQuest Buitton on Form
Arvin: Hope you see this post. Mapquest must have changed their code. My Mapquest button on the form does not work anymore. The reason is that in Canadian Postal Codes there is a space (ie. E3A 3N7). My data is stored this way. Mapquest used to accept this format but not anymore. How do I delete the space in the hyperlink created from the event on click (button on Form). Your help would be greatly appreciated again. Here is my code. Private Sub cmdHyperlink_Click() Dim strPath As String If Not IsNull(Me.Postal_Code) Then strPath = "http://www.mapquest.com/maps/" &a...

Forcing Input as Uppercase on User Form
Hi can any one advise me how to amend the following code to ensure :- (a) only two letters are input (b) they are converted to upper case (c) how do you centre the text in a message box header as I have use spaces to achieve this Code used inputVal = TextBox1 Do Until ActiveCell = inputVal ActiveCell.Offset(1, 0).Select If ActiveCell = "" Then MsgBox ("Sorry Not Found - Please Input the first two letter only e.g. RG ") ", vbInformation, " Warning !! " Unload UserForm1 UserForm1.Show End Else End If Loop Excel v...

Close a form when it loses focus
Hello All Is it possible to make a form close as soon as the user clicks elsewhere on the screen? The form in question is a trivial popup form that is best shown in datasheet view, so I can't add a button. I know the user could close it with the 'X' in the top-right corner, but for other reasons it would be much better if I could force it to close as soon as they click elsewhere. I have tried adding the following OnLostFocus event to the form, but nothing happened! DoCmd.Close acForm, "[frm contacts]", acSaveYes Second question (or does this need to be in a...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...

Cannot stop using online services with First Tech Credit Union
My bank, First Tech Credit Union, recently upgraded their online banking services. This upgrade also included a change to the user name and password used for online banking in Money 2006. Their upgrade instructions (http://www.firsttechcu.com/help/help_guide_money.html) say to disable online services for your Money file as the third step to upgrading to the new services. The problem is that when I click on Stop Using Online Services in Money nothing happens. I click on OK and Money hangs for a bit then comes back with no change, clicking on OK again has the same effect, clicking on ...

Using CComboBox embedded in CToolBar
Hi, I've derived a class from CToolBar and overridden OnCreate to replace 2 placeholder buttons with 2 combo boxes. The two combo boxes are also derived from CComboBox, and populate themselves with appropriate strings. My problem is that I'm not sure of how to inform my application when the user has selected a new item in one of the combo boxes. I've added a handler for the CBN_SELCHANGE message to the CComboBox, but I don't know what to put in it. What is the best way to forward the message to the mainframe? Also, when I replace the placeholder buttons, can I reuse their ...

Subform doesn't work once added to main form
Hi, I've been searching around looking for some help and can't find anyone that has had quite the same problem. I have a main form called 'Sites' and have added a subform called 'Previous Year Flows' into the main form. I do not enter data into this subform. Instead, data on previous years is drawn through from a table called prev yr flows into this subform. When I open the subform on its own it draws through all the necessary data, but when it is embedded within the main form, none of the actual data is pulled through at all, although the linked fields, Site ID and A...

Form with desktop shortcut
I created a form which displays one record. The record is chosen at random by a query. It works fine within Access 2003. However, when I create a shortcut to the form on my desktop and run it that way, it always gives me the same record. How can I get the random record I'm looking for? -- CindyLuWho Cindy, I'd say you're probably better off creating a macro that opens the form at the required record. Your desktop shortcut should call this macro, rather than the form. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia "CindyLu" <CindyLu@discus...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

is there a way of using a second monitor as a customer display
This is a multi-part message in MIME format. ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Does anyone know if rms works with 2 monitors. What I mean can you use a second monitor as customer display or something similar. A lot of pos systems out there use a second monitor as customer display, I m not sure if rms supports that option or not but any help would be appreciated. thanks ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

Re:Help with verifying the signature of enveloped -message using x509 certificates.
Re:Help with verifying the signature of enveloped -message using x509 certificates. Hey Friends, I have an xml given by our partner trying to integrating with us and i have their certificate with public key installed in both personal and trusted people folders. when i am using the following code to check the signature of message signed by my certificate i am able to verify the signature but when i am trying to check the signature of the message of our partner using the same following code i am not able to verify the message. the message sent by our partner is right below the code.I wou...

domain controller
If I have a more powerful comuter than needed, can I run Exchange 2003 on a Windows 2003 domain controller, which happens to be the first domain controller in the domain? Yes, you can though it's not recommended. "rook" <anonymous@discussions.microsoft.com> wrote in message news:7e6901c4849a$4ff93a60$a401280a@phx.gbl... > If I have a more powerful comuter than needed, can I run > Exchange 2003 on a Windows 2003 domain controller, which > happens to be the first domain controller in the domain? Yes it is extreamely not recommended because your so called &qu...

PopUp Form is blank white
I have a PopUp Form that works fine - - except if the query which is its record source returns (has returned) no record. In this case the form opens to a blank white - not even labels show. Have never seen this before and would welcome advice that explains why even labels do not appear. Related to this; in the remote situation where there is no record I probably need a default message in this popup form and was initially thinking a non-visible label made visible if a field was null . But in seeing the entire form blank when there is no record (which I suppose is a state different t...

How do I create a chart in Excel using data from two worksheets
I am trying to create a chart using data from two separate worksheets but it will only allow me to use the information from one. Here is one way: I made a chart from data in A10:B20 on sheet1 (A was the x-category data) I copied some cells from Sheet2, right clicked in chart area of chart and used Paste After the copy, I could have left clicked the chart and used Edit | Paste Special to specify more about the data I want to paste. Try it and come back with more questions. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "cdptuesdays" <...

=RowNum([Form])
I am running Access 2003 and found the following function: Public Function RowNum(frm As Form) As Variant On Error GoTo Err_RowNum 'Purpose: Numbering the rows on a form. 'Usage: Text box with ControlSource of: =RowNum([Form]) With frm.RecordsetClone .Bookmark = frm.Bookmark RowNum = .AbsolutePosition + 1 End With Exit_RowNum: Exit Function Err_RowNum: If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row. Debug.Print "RowNum() error " & Err.Number & " - &q...