Re: Can't believe it - Back Again Already! - How to set variables to 'Type' in SQL Statement?

From: Kahuna (none_at_gonewest.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 18:01:35 -0000

That's exactly what I need Marshal thanks - just couldn't get my head around
the FORMAT action/method.

I'll try that tonight thanks a lot. 

-- 
Kahuna
------------
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:lv56s0tte11f54nnjgtc7cp5k0siar8m5h@4ax.com...
> Kahuna wrote:
>>Following on from last nights disaster <lol> (Doug, John and Bas know what 
>>I
>>mean) - I need some guidance on this one:
>>
>>I have an SQL Update statement (that works!!), which takes an input from a
>>form to show which field to update in the table, a second input from the
>>form which says update where 'x' is the current table entry, and a third
>>input where the user types in what he'd like the data changed to.
>>
>>All works lovely in a text field in the table but fails dismally when 
>>there
>>is a number / bool / date etc.
>>
>>I know the data type number of the field chosen to be updated, but cant 
>>find
>>the way to set the SQL variables to assume the data types of the field.
>>Here's the SQL string:
>>
>>strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
>>[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
>>                        & " WHERE (qryFabricCondition." &
>>[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"
>>
>>The variable txtUpdate_With is actually now Dim'd as a variant, thought 
>>this
>>would allow it to assume the data type of the field to update to.
>>
>>    Guess:    Is it possible to set the form field to accept only a
>>particular data type?
>>                  Is it possible to set the variable to a specific data 
>> type
>>before adding it to the SQL statement?
>>                  I need to remove the extra quotes around the
>>txtUpdate_With variable dependant upon the data type -
>>                        This last seems most likely - but does that mean
>>building the SQL on the fly or creating multiple copies of it (one for 
>>each
>>type)?
>
>
> You're already building the SQL on the fly.  What you need
> to do is take the type of the field to bu updated into
> account.  Here's some air code that should demonstrate the
> idea:
>
> strSQLUpdate = "UPDATE qryFabricCondition " _
> & "SET " & [txtField_to_Update] & " = " _
> If <type of field is Text> Then
> strSQLUpdate = strSQLUpdate _
> & "'" & txtUpdate_With & "'" _
> & "  WHERE " & [txtField_to_Update] _
> & " = '" & txt_Where_equal_to & "'"
> ElseIf <type of field is date> Then
> strSQLUpdate = strSQLUpdate _
> & Format(txtUpdate_With, "\#m\/d\/yyyy\#") _
> & " WHERE " & [txtField_to_Update] _
> & " = " & Format(txt_Where_equal_to, "\#m\/d\/yyyy\#")
> Else 'number type
> strSQLUpdate = strSQLUpdate & txtUpdate_With _
> & " WHERE " & [txtField_to_Update] _
> & " = " & txt_Where_equal_to
> End If
>
> -- 
> Marsh
> MVP [MS Access]

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Type Mismatch Error Message
    ... >> Using Text as SQL data type. ... I created a database on a SQL server manually and matched the column names ... I thought you "didn't use the database wizard?" ... >> Data type in form is No Constraints. ...
    (microsoft.public.frontpage.programming)
  • Re: DISP_E_TYPEMISMATCH with OWC11
    ... you think its related to what data type OWC is expecting from the SQL ... I have a SQL ... Dim objChartspace, objChart, c, objConn, objRS, axis, sql ... An unhandled exception occurred during the execution of the ...
    (microsoft.public.office.developer.web.components)
  • Re: From BAD To WORSE: A Disasterous Delete
    ... > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN IS ... Tibor Karaszi, SQL Server MVP ... > mysterious deletion of ALL RECORDS, but now with the smaller data extract. ...
    (microsoft.public.sqlserver.server)
Quantcast