sql Syntax

[HelpLaura]

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.

[LinXG]

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.

[si_the_geek]

A memo field contains text, so it uses '

[GaryMazzone]

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?

[HelpLaura]

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

[si_the_geek]

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").

[GaryMazzone]

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""""

[HelpLaura]

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.

[si_the_geek]

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

[HelpLaura]

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?

[si_the_geek]

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).

[GaryMazzone]

Try this:

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