I'm trying to insert a date into a textbase field that I have specified to be a strict date field.

I've tried entering the date in a variety of formats with no joy; the error message I get is "Insert value must be a simple value".

I'd be very grateful if someone could post a quick example of a date insert or update query.

The syntax all depends on how you have the date field defined in the textbase .INI file.

If you don't have an [ODBCFields] section, or the date field is listed as:

[ODBCFields]
datefield=X

Then the syntax would be:

insert into sample (datefield) values ('4/8/2003')

If you have it defined as an ODBC DATE field:

[ODBCFields]
datefield=D

Then the syntax would be:

insert into sample (datefield) values ({d'2003-04-08'})

Thanks Lisa, that worked perfectly.
Is the syntax you provided detailed anywhere in Inmagic's ODBC documentation (for future reference) - I had a look but couldn't find it hence my original post.

Thanks,

John

It's documented in the "Special Search Syntax for Text Fields" page, which is indexed under "Dates:searching for" in the Help Index in DBTODBC3.HLP.

"Searching for Dates

The way you search for dates depends on whether the DB/TextWorks Date field is mapped to a SQL DATE or TEXT field. To search a DATE field, type the search argument as {d'YYYY-MM-DD'}. In a TEXT or LONGTEXT field, you can search for full or partial dates in any format, using surrounding quotes. To find all dates in a certain month or year, type the partial date in any recognizable format and use the CT operator (for example, SELECT … WHERE DATEREC CT 'Dec 1998'. To retrieve only the date you specify, precede the date with an equal (=) sign (for example, SELECT … WHERE DATEREC = 'Dec 1998'). You can also search for dates and ranges of dates using the @DATE variable. For more information, see the DB/TextWorks online help.

Use the guidelines for mapping fields to help you decide how to map the DB/TextWorks Date field."