Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Substitute Code For An Apostrophe?


I was wondering if anyone knew of some sort of substitution or exit code that I can use instead of an apostrophe. The code below crashes if the value of Combo1.text contains an apostrophe:

reportheaderdefault.RecordSource = "Select * from ReportHeader where reportname = 'default' and userName='" + Combo1.Text + "'"

Combo1.text is a name and therefore if someone's name has an apostrophe in it (eg. O'connor), the application crashes.

Can I substitute the apostrophe with something else or is there another way??

For example in XML if you wanted to use "<" in the data of an XML document instance, you could substitue it with "&lt;" so that it is not treated as the beginning of a tag.

Thanks in advance,

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Listview Substitute
Does anyone know a good and free ActiveX substitute to the listview control... If not, is there a way to buy a licence to that control itself? I only have a crappy learning version here, and it doesn't look like the listview is included...


Hwnd Substitute &&0
I've been looking about for a way to get a timer going without using a timer on a form.

I got some intersting info from a few other posts ... but mainly this one.

So ... I can get the timer working in a module, and thats fine ... but someone made a reference to substituting the hwnd of a form for the value &0.

Is it just me ... or is it that if I start the timer this way, I am unable to stop it.

Any advice?

Substitute For .SelColor?
I have coded a "Mouse Over word" function that simply changes the text color of a word in a rich Text Box as the mouse is moved over it.

Here is a code extract.

.SelStart = Start_Pos
.SelLength = End_Pos
.SelColor = vbRed

To reduce flicker I substituted an EM_SETSEL message instead of .SelStart and .SelLength. Like this (This helped a lot) :

retval = SendMessage(rchMainText.hWnd, EM_SETSEL, ByVal CLng(start_pos), ByVal CLng(end_pos))
.SelColor = vbRed

To further reduce the flicker, I'd like to replace the .SelColor with a windows message. Any ideas?

Substitute For Timer?
hi al,

Is there any substitute for timer in VB6?
I mean timer has fixed loop time ( 0 to aaaaaa milli seconds)
Suppose if i need tocontinuously run some prog, how can it be done?


Substitute Range Question?
In a workbook, have data sheet and report sheet.
In the report sheet at cell N1=If(data!B3:H3=1,1,0)
In cell P1=2
How can I substitute the value of P1 into the range so that the formula will become something like N1=If(data!"B" &P1+1:"H" &P1+1=1,1,0). I need to do this to because when I change the value in P1, N1 will give me the answer instantly instead of changing the formula range every time.

Can a worksheet (A.xls) defines reference to a worksheet(B.xls) for data that is not open/used?

In VBA how do I define to use a closed workbook (A.xls) to extract data and write to current open workbook (B.xls)?

GotFocus() Event Substitute?
Ok guys I need a little help! I have a situation where the Form_GotFocus() Event is not firing where you would expect it to. I think the answer to this problem will involve Subclassing, but I don't know what Message to be listening for.

The Zip attachment includes two projects, titled (a) Single Project and (2) Multi Project. The Single Project shows that the Form_GotFocus() Event works; the Multi Project shows that it can fail.

Single Project

Run the Single Project first, if you would, to get a feel for it? When you run it, Form1 opens on which a single button exists stating "Open Form 2". Clicking the button opens up Form 2.

Form2 is set AlwaysOnTop using the SetWindowPos() API. It is also utilizing the Form_GotFocus() event to (a) Beep and (b) use Debug.Print Count, where Count is a Static variable stating how many times the GotFocus() Event has fired.

Clicking back and forth between Form1 and Form2 will result in confirmation Beeps and a printout in the Immediate Window. Try it out and you'll see what I mean...

Multi Project

Then go into the folder named "Multi Project" and open the "Multi Project Group.vbg". This project is substantially identical. Well, it's intended to be 100% identical.

But run it, and you'll see that Form_GotFocus() will fire only once in this case, when Form2 is first Activated. Subsequently clicking on Form1 and back to Form2 still visibly changes the focus, but the GotFocus() event is not firing.

Btw, this is not an IDE-only situation. Compiling the DLL and then the EXE, and then running it, exhibits exactly the same behavior. I don't know why a Form within a DLL, which is still part of the same address space as the EXE, would behave any differently than a Form placed directly in the EXE. I certainly don't see why GotFocus() should fail.

I'm not worried about the theoretics, really, I just need it to work! I have tried Subclassing, listening for 'uMsg = WM_ACTIVATEAPP'. This does pick up when the Application (both Forms as a group) gain or lose focus, but, unfortunately, does not pick up the more subtle change in focus between the two Forms that I need to detect...

Anyone know what uMsg I should be listening for and/or have any other alternative ideas?

Huge thanks in advance...

Substitute/alternative For WM_LBUTTONDOWN
Is there an alternative for WM_LBUTTONDOWN? I want my program to click the form of an external program that is on the the background(not seen in the screen). I used

lngresult = SendMessage(lngGetNewTask, WM_LBUTTONDOWN, 0&, ByVal lnglparam)
lngresult = SendMessage(lngGetNewTask, WM_LBUTTONUP, 0&, ByVal lnglparam)

but it will only click the form of the external program when the external program is seen in the screen and not when the external program is below other running program. What I wanted is to click the form of the external program will the external program is in the background and not seen in the screen.

Can You Substitute A Control Name With A Variable?
This code is a snippet from one of the modules in my program. It works perfectly, but I now need to do the exact same thing....but with another form. Is there a way I can substitute the "frmMain.lstDoctors" with a variable name so that I can pass this function the name of the control I'd like to use, when I'd like to use it?

VB Code:
With rsDoctorList     'Run Query & move to the first record...      .MoveFirst         Do While Not .EOF            If IsNumeric(.Fields!PhysicianNo) Then ' ensures the physician number is numeric and not alphanumeric               If .Fields!Active = "Y" Then                  frmMain.lstDoctors.AddItem .Fields!Lastname & ", " & .Fields!FIRSTNAME                  frmMain.lstDoctors.ItemData(frmMain.lstDoctors.NewIndex) = .Fields!PhysicianNo                  .MoveNext               Else                  .MoveNext               End If            Else              .MoveNext            End If         Loop  End With  'The visible property of the lstDoctors control is set to False by default. This loads the records a lot faster.'We then have to display the control on the form.  frmMain.lstDoctors.Visible = True  Exit Sub

Substitute For An Array List
I am trying to archive a database, but I don't think this is a database question though. I need to take all records in my database that are over a month old and save the record for one random wafer for each lot number and store that one wafer to a tblArchive. My table looks something like this:

seqNum Date LotNum WaferNum Measure1 Measure2

1223 10/20/2005 158 1 .98 .0014
1224 10/20/2005 158 2 .97 .0015
1225 10/20/2005 158 3 .975 .0013
1226 10/20/2005 159 1 .92 .0021
1227 10/20/2005 159 2 .93 .0019
1228 10/20/2005 159 3 .91 .0022

My idea is that I will get a recordset of all the records that are over a month old and then I will create an array of all the different LotNum in this recordset. Then I will randomly choose one record from each LotNum to archive. The problem I have is that I don't know beforehand how many LotNum I will have so I would use an array list if I where using .Net or maybe a vector if I were using C++, but I don't know what to do in VB6.

How can I make an array that I can continue to append to? Each time I find a new LotNum should I just create an array that is one bigger than the previous array and do a copy? That seems too ineffecient though. Sorry for such a long question.

Where Can I Find Substitute Controls?
Hi again, Everybody...

I sure hope someone can suggest a solution.
I've mixed textBoxes and MaskEdit Boxes on a form, simply for an appearance thing. The textBox displays recordset contents, and the MaskEdit was to be pretty when editing the same recordset contents.
Trouble is... I can't get rid of the Maskedit prompt character and frankly that looks like hell. Mixing controls also seems like a "bad idea".

There must be a substitute textBox control that would allow changing all the proeprties ( such as txtBox.Appearance=1 ) at run time, and possibly even allow for for input masks or input control.

Where does a guy like me go to to find substutute controls?

I should also ask how to be using substitute controls?


Timer Control Substitute
i think i can't use a loop because i need to be able to perform something else (like click a button...) while my timer perform another task. i am not allowed to use a timer there another way?.

A Substitute To Datagrid Split (my Own One)
Hi Guys, since i didn't like the split method that comes with the DataGrid, I decided to make my own one.
Meaning to freeze the first 2 columns (or whatever) when the user navigates thru the datagrid. Everything when great so far, just what i wanted.
But to do this, i had to catch the keystrokes (left, right, end, home, Tab, etc.) so i used the KeyDown event and wrote in it (in a simplified way) something like this :
Private Sub DataGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case 38, 40 'Up - Down Arrows
Case 37 'Left Arrow
DataGrid1.Col = DataGrid1.Col - 1
KeyCode = 0
Case 39 'Right Arrow
DataGrid1.Col = DataGrid1.Col + 1
KeyCode = 0
End Select

My problem is that if I write something let's suppose in the 2nd cell and use the arrows to navigate, the whole text is moved to the next cell, not only the cursor ..........

Any ideas ?

Flush Substitute For VB TEXTSTREAM?
I am writing to a log file in a VB app but I do not want to close the textstream object everytime to flush the buffer. Does anyone know of a substitute for Flush() in VB? I can't seem to find a way to clear the buffer without closing the object.

Thanks in advance for any help!

Substitute Codes From Qbasic
i m new to vb but i have some or little knowledge of qbasic. i got stuck in a situation where i want to use read, data statements from qbasic in vb. i wonder if there is any way around this problem. i hope u can help me. my email is


Where Can I Find Substitute Controls?
Hi Everybody,

I'm getting huge grief trying to mix textBox and MaskEdit Box controls on a form.

I need to find a control that mixes some of the features of both into a single control.
( I like the VB textBox control, but I need to toggle between the 3D appearance and the flat appearance at run time.... also need to "format" user entry a variety of ways )

I would imagine there are sites that offer "user designed" controls, so I hope that some of you folks will reply and tell me where they are?


Substitute For 'Replace' Method
Hi Gurus, Please help me out in the following matter:

Presume I have a String expression strExp="H+H1*W"

. I wish to evaluate this expression. As soon as I get the values of the variables I start substituting the values in the expression. For eg. if I get the value of W as 10 the expression becomes H+H1*10.

I do this using the replace method,


This goes fine uptill here. What if I wish to substitute the value of H as, say 3. When I use the Replace method the expression strExp becomes something like this:
3+31*10 while it should have become 3+H1*10 since I do not still have the value of H1.

I think you can presume the problem in substituting value for variables which are substrings of some other variables used in the expressions.

I request you to please tell me any better method that uses a proper 'comparison' while replacing i.e. replcaes on 'exact-match' only.

Any takers?

Online copy of my Resume:
CDAC-Alumni website at
My site for SCJP aspirants & Java-admirers:
My writings at

How Substitute The QueryDefs In Crystal ?

I have and old app using VB5 + DAO + Access97. Now I'm trying to migrate to MySQL. I have a lot of situations where I create the reports from QueryDefs.

Obviously there aren't query defs in MySQL and I'm looking a way to migrate the reports but how can I design the new reports from ODBC queryes ???!!!

If there's a way to ling a report with an ADO recordset it could be great !

Thanks in advance.


Tabindex On Form - Substitute/Add Return Key
Does anyone know an easy way to add or substitute the Return key for the Tab key to navigate around a form? I know the Tabindex keeps track of textboxes, etc. on a form numerically. Any way to access that index and Add or Substitute the Return key?

Substitute For IntTextBox In Masktext.vbx *RESOLVED*
Hi everybody,

inTextBox in masktext.vbx is a pretty old control that does not have an activeX update. However, I am just wondering if there is any ocx control with similar behavior available these days. This control would output and input text as a traditional textbox but it would choose input in a way. In another words, this intTextbox would not accept anything other than numbers between 0 to 9.

Thanks very much for your help.

Is There A Substitute For The While.. Doevents.. Wend Cycle?
The Doevents thing is preventing my form from unloading.
Is there a way to unload a form nicely while a While/Doevents/Wend loop is running, or is there a better substitute altogether?


Does Anybody Know How To: Macro Substitute And Obtain Current Procedure And/or Function Name
Does anybody know how to:

1.) macro substitute (just like in Visual FoxPro) and
2.) obtain current function or procedure name to place in error log
(without hard coding for each function or procedure)



~ Steve

Is it legal to enter apostrophe before @ in email address ???
I mean that for example is this a valid address: some'
I want to write an email validation script MYSELF, and just want to know according to RFC is this a valid one?

Apostrophe In Name
Hi, here's my sql query:


.RecordSource = "Select * From Names where Name = ' " & Combo1.Text & " ' "
Everything works just fine until the name I choose from the comBox has an apostrophe in it like: "Normand L'Amour", when
running, VB takes the apostrophe in the name like then end of the comboBox text. How can I work around that apostrophe problem?

Thanks in advance!

I have a customer with an apostrophe in their name. It causes my vb program fits. I need to use that field as a parameter in a SQL statement, but the apostrophe causes it think there an extraneous quote in the string. How do you folks handle things like that?

Apostrophe Help!!
i am using sql statement to insert data to my ms access database from my vb project. but i have trouble inserting apostrophe symbol.
for ex, entering a (can't) word in rich text box and insert it to ms access with memo data type.
error occured regarding the apostrophe ...
any idea guys???
thanks in advance..

Apostrophe In SQL
I need to include the apostrophe in a field in my database.
I have tried using the replace single quote with two single quotes
which sort of works, but it add two single quotes to the field. I
just need one and if I let it go through without checking I get an

I also don't want to be changing any settings on the server if at all possible.

Thanks in advance for any help or suggestions.

SQL And Apostrophe
For the majority of my database work I use Access as a back-end! <Hopefully that'll change soon>.

I was curious about having Apostrophe's in my strings for queries. Here is an example about my question

MySql = "SELECT * FROM Companies " & _
"WHERE Company = '" & <MyVariable> & "'"

Now lets say <MyVariable> = "Danny's"

so mysql reads :
SELECT * FROM Companies
WHERE Company = 'Danny's'

!!!! See where this is no good!?!?!?
I have written my code so that I don't except apostrophes but they are nice and I was curious about a way around this madness!

Formula With An Apostrophe...
This is probably a really dumb question but im new to this. I'm trying to put in a code for VB to automatically insert a formula into a specific cell. My formula has an apostrophe in it because it refers to another workbook...

ActiveSheet.Range("B66").Formula = "=pull("'"&mfpn&wsn&"'!"&ct)"

Everything after the first quotation is converted into a comment...Is there a work around?

please help!!

Variable Contain Apostrophe
i did face this problem when i issue sql statement as below

LampiranSql = "INSERT INTO LampiranI" _
& "([Lampi_No],[Date],[customer],[Do Number])" _
& " values ('" & strLampi_No & "', '" & dtmdate & "','" & strCustomer & " ','" & strDoNo & "')"
olddb.Execute LampiranSql, 128

when strCustomer value = "high's tech"
the program prompt :Syntax error (Missing Operator)in query expression "high's tech"

How to solve this problem???

Use Of Apostrophe In String
The use of apostrophes in a textbox, when using collaboration with a SQL statement causes trouble - i was wondering if theres any way to get around this - ie when inserting a value that contains an apostrophe, to omit it before using the SQL insert statement. Any help would be greatly appreciated!

Apostrophe In Path
Hey guys. I'm having trouble with my path for an image source because one of the folders has an apostrophe in the name. I can't change the name of the folder because it would mess up several people. I'm printing to an html doc and the image isn't showing up because I don't know how to mark for an apostrophe. I know a space is %20.
varPath = "//server/apps/department's%20databases/checkbox.gif"
Print #1, "<tr><td align=left><img align=middle src=" & varPath & "></td>"
Thanks for the help!

Apostrophe Question

Want to replace single with double apostrophe to make safe for access database. I know the line is something like:

Replace(txtFirstName.Text, ",", Chr$(something))

but what's the number for something?

Thanks kindly


Apostrophe In SQL Statement

i'm using ADO 2.6 obj lib and the SQL statement INSERT INTO to add a record to a DB, my question is:

is there a way to get around value that contains an apostrophe ( ' ) in an sql statement. It produces error if any of the values that are being inserted into the db contains an apostrophe.

thanks for any help

Apostrophe In Column Name
i am using ADODB.Connection object with an access database.

when i try to alter a table (adding a column with a apostrophe in it) i get an error of improper syntax for column name.

for example:
MyObject.execute "ALTER TABLE " & tablename & " ADD COLUMN " & "somecolumn with 's" & " MEMO NULL"

i have tried replacing the ' with 2 of them '' and still the same error.

access allows column names with apostrophe, so i wonder how to do this in code.

Problem With Apostrophe
sql =INSERT INTO Table1( Name) Values ('"& Text1.Text &"');
myCmd.CommandText = sql

I have problem with apostrophe. In this code if Text1.Text = Albert's
I have problem.Does anybody know anything about this problem. How I can solve it?

An Apostrophe In SQL WHERE Statement
I have a SQL Query that looks at a global variable for the WHERE statement.

WHERE CName = '" & CompName & "'"

Now the problem I have is when the global variable has an apostrophe in it, for instance A's Cleaning does not work. Is there anyway around this, it does allow As Cleaning. However I also pass this value to a report, so it would work for us if I have to remove it, any assistance would be greatly appreciated.

Insert Apostrophe
hi, i have this code where it will import from excel to access but it won't accept fields with apostrophes in it, is there a way around it? i remember a code, something like replace? but do not know how to use it in this case. thanks guys

strSQL = "insert into test ([A], [b]) values ('" & xlSheet.Cells(j, 3).Value & "', '" & xlSheet.Cells(j, 4).Value & "')"

Problem With Apostrophe (')
ei guys,

i need your help regarding apostrophe... whenever i retrieve/use data from an access database with an apostrophe, the system sends a run-time error... tnx

Insert Apostrophe
Hi all,

I am trying to insert a value using "INSERT INTO ..." statement.

One text field contain name such as "O'neil", and which raise an error when I execute that Insert statement.

Please help. THanks in advance.


Apostrophe In Sql Query
I am writing a small program to query a database for a client. Everything works except for the client names that have an ' in them. For example

dim c_name as string
dim Query as string

c_name = "Smith Brother's"

Query = "select * from tablename where client_name = ' " & c_name & " ' "

This query works perfectly if c_name = "Bob", but when the name has an ' in it it returns an error and crashes. Does anyone know a VB workaround for this so I dont have to tell them to change their database, as unfortunatley, they are somewhat against the idea.

I am writing this in VB6, and the database is in MSSQL 2000.

Thanks in advance,

Prevent Apostrophe Help..
i am using sql statement to manipulate my question is how to prevent user from typing apostrophe (') to my control such as textbox???

Apostrophe Excel
i have data in excel which i want to convert it to
my problem is in excel data the date format was wrong,
when i want read it through recordset in vb, it return

nothing because it contain apostrophe

example data in excel :
it's return nothing in recordset vb

Has Anyone Done An Insert With An Apostrophe In It?
I have an old problem.  It is doing an insert with values that have an apostrophe in them
Example Gary's Garage would blow up SQL

Has anyone found a way around this?

ADO Apostrophe Issues...
VB 6 / M$ Access


I'm beginning to pull my hair out on this one. I have to loop through a recordset, and insert some data. Problem is, some of the addresses have apostrophes (') in them, and it's hosing me up on my cnn.Exectute (insert) statement. Here's a sample of my code (extremely simplified):

Do Until rst.EOF
     cnn.Execute "INSERT INTO TABLE (COL1, COL2, COL3) VALUES (" & idx & "', '" & rst("ADDRESS") & "', '" & rst("PAYCODE") & "')
     idx = idx + 1

As you might guess, the apostrophe kills it every time (data looks like "315 O'Reilly Road"). I tried using the replace function, but got killed when rst("ADDRESS") was null. Tried an IIF statement around that, but no luck there, either.

Anyone have any ideas?

Thanks in advance for any / all help!


The Dreaded Apostrophe && Sql
You all helped me with function keys last week. Thanks so much; your advice
was right on.

My question now - does anyone have a function that I can use to test my sql
statements for the apostrophe and quote and do a search/replace type thing.
I'd like something to put in my basFunctions module instead of replacing
every time I do a sql command in my program code. Thanks!

Urgent!! ADO Does Not Add Apostrophe
Hi all


Could any one explain to me, when I add an a Apostrophe sign ' to my text "like some words have" and press OK the data doesn't get saved. When I type the same text without the sign it is saved.

Here is a good example:
Doesn't vs Doesnt

Attached is a sample app of the ado code I used. Please have a look at it

Thanks in advance

Apostrophe (') Problem
Because I have a field called address, why is it that this field does not allow a apostrophe(') char?


Sending Apostrophe Key With Sendkeys
How do you use sendkeys to send apostrophe key (") ? I tried putting it in brackets but it doesn't work.

Error Caused By An Apostrophe (‘) ?
Why do I get an error trying to enter an apostrophe (‘) to an Access DB memo field

If that’s just the way it is, then what else can I not type into this field

Or can I trap this and stop the error

Copyright © 2005-08, All rights reserved