Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Arrow Unanswered: Returning Field type...

    Hi,

    In an effort to combat type mismatch in a more effective way I need to write a function that returns the field type (text, date or number mainly).

    I have a form which lets you build up simple sql statements. The problem starts to get sirious when i am dealing with 20 different fields I need to query and each field has a different type. So for example is i treat numbers as text in an SQL statement i get a type missmatch error.

    What I do right now is have an 'If' statement for almost every field. saying If fieldID = Issued_date then Value = "#" & value & "#". And so on...

    My question is there a way to return something like field name and type so I could just write 1 "date" if statement rather than 5?

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Are you using ADO? Then you can query the field as so

    If rs.Fields.Item("Field1").Type=adDate then
    .
    .
    .
    end if

    Here are some other types (there are more):
    adChar, adCurrency
    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).
    adDBTime A time value (hhmmss) (DBTYPE_DBTIME).
    adDBTimeStamp A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
    adDecimal An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
    adDouble A double-precision floating point value (DBTYPE_R8).
    adInteger A 4-byte signed integer (DBTYPE_I4).
    adNumeric An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
    adSingle A single-precision floating point value (DBTYPE_R4).
    adSmallInt A 2-byte signed integer (DBTYPE_I2).
    adTinyInt A 1-byte signed integer (DBTYPE_I1).
    adUnsignedBigInt An 8-byte unsigned integer (DBTYPE_UI8).
    adUnsignedInt A 4-byte unsigned integer (DBTYPE_UI4).
    adUnsignedSmallInt A 2-byte unsigned integer (DBTYPE_UI2).
    adUnsignedTinyInt A 1-byte unsigned integer (DBTYPE_UI1). .
    adVarChar A String value (Parameter object only).
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    thats great, but how do i get it to check all the fields?

    see what i need is something like this:

    if field type = adDate then

    value = "#" & value & "#"

    endif

    where the field is whatever is selected in the combo box

    then another one for text.

    That should leave number which can be displayed without any quotes in sql.


    Thanks for listing the types too

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Use 2 IIF Statements (There are two single quotations in "'Test'") as follows:

    IIF(fieldtype = adDate,"#12312004#",IIF(fieldtype=adChar,"'Test'", "2"))
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •