sql Syntax

I know that you put # round dates, ' around text and nothing around numbers when inserting into a database, what category does a memo box in access fall under. I seem to be having problems trying to get it to work in my code.

Hi HelpLaura;

I just took a quick look at some code I have that adds text to a Memo field and it's simply delimited with '.

Perhaps you could post a bit more detail on what is happening.

A memo field contains text, so it uses '

The # around dates is only for MS Access database. Other DBs use different deliminaters for dates (most use a single qoute, but Oracle is different). What problems are you having?

the other problem i have is that in one of my fields, it is for height the problem i have is that each time i produce an insert with 7'11 it bugs it up because of the ' is there any way that i can get round this? i tired putting ['7'11'] as well as '[7'11]' but doesn't like either of these

If you are using an Insert statement you need to replace each ' character in data with two, so you would have: fieldname = '7''11'

If you use a Command object with parameters, this (and similar issues) are not a problem. If you want to see how to do that, there is an example in our Database FAQs ("ADO: How do I add a record").

You will also find the same condition if you use 7'11" as the height. The single qoute needs to be replaced by tow single qoutes ( '' ) and the double qoute needs to be replace by four double qoutes ( """" ). So 7 feet 11 inches would look like this: 7''11""""

in my text box on my front end i have put 7''1 and then in my code i have tbl_suspect.height = '" txt_height.text & "'" but this doesn't work :(

Have i somehow miss understood what you mean? On my front end i only have one text box.

You'll need to show us a bit more code, as tbl_suspect.height doesn't appear to be valid code.

tbl_suspect.height = '" & txt_height.text & "'"

this code is part of my sql statement i am executing. This field a is not a number field, so when i tried it out with text in this field the code works fine. its those ' that are causing me problems. What other code do you need me to provide?

It would be much better to show us the entire line of code (and preferably a few before/after it too), and of course tell us details about why something "doesn't work".. eg: if there is an error, what is it?

From what you have told us, it looks like it should work (assuming the rest of that line of code is valid).

Try this:

tbl_suspect.height = '" & replace(replace(txt_height.text ,"'","''"),"""",""""") & "'"