UPDATE STATEMENT TIME INSERTION 
Author Message
 UPDATE STATEMENT TIME INSERTION

What am I doing wrong here?

SQLQuery="UPDATE tasks SET tk_date='" & txt2 & "', tk_time= #" &
format(txt3, "##:##:##") & "#, tk_offerte="&ch1&", tk_support="&ch2&",
tk_development="&ch3&", tk_invoiced="&ch4&" WHERE tk_id = "&NR&";"

DB.Execute(SQLQuery)

the red part is where it goes wrong. I want to insert the txt3(the name of
an input field in a form which i have extracted with the request.form
statement) variable in the tk_time column(which is a short time defined
column).

ANY solutions?



Tue, 28 Aug 2001 03:00:00 GMT  
 UPDATE STATEMENT TIME INSERTION
On Fri, 12 Mar 1999 15:48:43 +0100, "Philip Apts"

Quote:

>SQLQuery="UPDATE tasks " & _

    "SET tk_date='" & txt2 & "', " & _
    "tk_time= #" & format(txt3, "##:##:##") & "#, " & _
    "tk_offerte = " & ch1 & ", " & _
    "tk_support = " & ch2 & ", " & _
    "tk_development = " & ch3 & ", " & _
    "tk_invoiced = " & ch4 & " " & _
    "WHERE tk_id = " & NR & ";"

1) Dates: Jet has specific rules about date literals and how to pass
them. Look this up in help. In brief, use this:
  "SET tk_date = " & Format$(CDate(txt2),"#yyyy-mm-dd#") & ", "

2) Times: Jet is less fussy, but you still have to pass a legal value.
Check out Help on the Format$() function. You should use something
like
  "tk_time = " & Format(CDate(txt3),"#hh:nn#") & ", "

3) I am assuming ch1, ch2, ch3, ch4 and NR are numerics and already
checked for illegal or null values etc.

4) By the way, there's no red in the newsgroups! :-)

HTH

Tim F

--



Tue, 28 Aug 2001 03:00:00 GMT  
 UPDATE STATEMENT TIME INSERTION
If you put all this in one statement VB will get confused with the " signs
that you use in the format statement

This approach seemed to work

    Sql = "UPDATE tasks SET tk_date='"
    Sql = Sql & txt2 & "', tk_time= #"
    Sql = Sql & Format(txt3, "##:##:##")
    Sql = Sql & "#, tk_offerte="
    Sql = Sql & ch1
    Sql = Sql & ", tk_support=" & ch2 & ",tk_development=" & ch3 & ",
tk_invoiced=" & ch4 & " WHERE tk_id = " & NR & ";"

Also, look for articles on using time with date constants using # signs.
Also in VB help. There are many issues in this when using non US settings in
Windows control panel.

Quote:

>What am I doing wrong here?

>SQLQuery="UPDATE tasks SET tk_date='" & txt2 & "', tk_time= #" &
>format(txt3, "##:##:##") & "#, tk_offerte="&ch1&", tk_support="&ch2&",
>tk_development="&ch3&", tk_invoiced="&ch4&" WHERE tk_id = "&NR&";"



Wed, 29 Aug 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. UPDATE STATEMENT TIME INSERTION

2. UPDATE STATEMENT TIME INSERTION

3. AVI Insertion Declaration and Command Statement?

4. Update statement creates Error, when statement is fine

5. Report Field Data Insertion & Update

6. Report Fields Data Insertion and Update

7. Generic insertion combo box (was :Insert text at Insertion point of a field)

8. RTF insertion of text at a specific insertion point

9. using stopeatch form functions to time an event procedure and update elapsed time control

10. web service and updating web reference with vb.net design time vs run time

11. web service and updating web reference with vb.net design time vs run time

12. DoCmd and Update statement

 

 
Powered by phpBB® Forum Software