[Resolved] SQL Datatype tags

[Arachnid13]

Does anyone know of a function which formats data from a MS Access field by adding the correct tags and converting it to a string?

to explain a bit better say you have a field of unknown type aField

Select Case aField.Type
Case adDate
strReturnString = "#" & cstr(aField.Value) & "#"
...
End Select

I would make this myself however looking at the DataTypeEnum in the Object Browser reveals a number of data types i don't recognise and some are little confusing such as the fact that there is a 'adDate' and a 'adDBDate'

[Al42]

You can't handle data into a field of unknown type - you have to know the type. You can format data from a field of unknown type, but it you're formatting it as a date, it will look funny if the field value doesn't equate to a valid date. (IOW, formatting the string "3R5" as a date won't look like a date. Neither will a name or an address.)

If you want to format a date field, it would be

strReturnString = Format$(recordset.Fields("aField").Value, "mm/dd/yyyy")  'or whatever format you want

BTW, the various types are defined in the ADO help file under the properties of Field.

adDate A Date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.

adDBDate A date value (yyyymmdd) (DBTYPE_DBDATE).

(The OLE DB types are shown in parentheses.)

[Arachnid13]

@ Al42

Thanks for the reply, but you seem to have misunderstood me.

To clarify things a little here is the full situation.

I have two databases, one is online and the other is stored locally. They are synchonised daily by my VB6 app. If the online database has been modified i want it to copy the change to the local database, however there are _thousands_ of fields and rather than have seperate statments for all the fields I am trying to write a function which, given the type of the data, will format it correctly for use in an SQL statment. For example say field [date1] has been modified online to 25/12/2000, the app would then create the sql statement "UPDATE table SET [date1]=#25/12/2000#" but all i have is the field [date1], the value "25/12/2000" and the fact that the type of the field [date1] is a date, so i want a function that recognises a date as needing the hash signs round it and returns for this example "#25/12/2000#".

Like i said in my first post this would be easy if I knew all the "tags" (by tags i mean the #'s in "#25/12/2000") for the different datatypes unfortunately as there are MANY datatypes I do not. So i ask again does anyone have a function to do this or know where there is a list with ALL the tags specified.

[Al42]

Save yourself the trouble. Copy the .mdb file.

The "tags" are:
Nothing for any numeric type.
" " for any char or varchar type (or nchar or nvarchar)
# # for any date, time or datetime type.

[Arachnid13]

copying the file isnt an option as the database im getting the data from to update the local database is a reduced version so that only the data required for the online database is uploaded/downloaded each time, so it saves time. Thanks for the tags though, i thought perhaps strings and dates where the only ones but i thought id better check just in case.