sql update statement

[jsmith1611]

Hello, I am trying to update a table using a sql statement in vba. I am getting a data type mismatch error and I think it is in my "where" clause. The wire_id field on the incoming_wires table is an autoNumber field and the primary key of the table. The wireid field on the form is a text field. Here is my code:

'update incoming_wires table
Dim strsql_2a As String
strsql_2a = "update incoming_wires " & _
"set incoming_wires.first = '" & Me.newFirst & "' where incoming_wires.wire_id = " & cast(wireid as integer)
DoCmd.RunSQL strsql_2a

I have tried many methods to convert the wireid to a number for a proper comparison with no luck. Can someone please help?

Thanks,

Joshua Smith

 

[namliam]

Cast is not an access function... did you make it yourself?

 

[jsmith1611]

Does anyone want to post something that is actually useful?

 

[MSAccessRookie]

Does anyone want to post something that is actually useful?

Before I provide what I believe might be "Useful Information", I would like to point out a few things:

1. "The Mailman" is one of the more reliable sources of information on the AWF Forum.
2. He is Correct that Cast() is not an Access Function, and is used in other SQL platforms such as Oracle and SQL Server.
3. I believe that he was trying to HELP you determine what to do. What he did not do, is TELL you what to do.

Useful Information:

What you want to do is look at the String Conversion Functions cStr(), cInt(), and cLng() to see if they help you resolve the issue. Good luck with your projects.

 

[namliam]

Without knowing what is Cast, I cannot help you... I know it is a SQL Server function, but you dont look to be sending the cast to SQL Server??

If your wire_id is a column in an access table it beeing an Autonumber, that would make it a LONG not an Integer.
What is the first column?? Is that a text field??

Perhaps,
strsql_2a = "update incoming_wires " & _
"set incoming_wires.first = '" & Me.newFirst & "' where incoming_wires.wire_id = " & wireid

or,
strsql_2a = "update incoming_wires " & _
"set incoming_wires.first = " & Me.newFirst & " where incoming_wires.wire_id = " & wireid

?? But this is for a good part educated guessing.

 

[jsmith1611]

OK, the wireid on the form is a text field. The wire_id is an autonumber field in the incoming_wires table. I am trying to update the incoming_wires table based on the text entered into an edit form using a sql statement. This form also uses sql insert statements to record the changes in an audit table. This part of my code works fine. When I try to do the actual update to the incoming_wires table, however, I am getting a data mismatch error. There is a "cast" function in sql, and that is what I was trying to use. I have tried cInt() and cDbl() with no luck.

Am I incorrect in my thinking that the contents of the string that is being used as a sql command will not follow vba rules? I mean, if I could use vba to do this, why use the sql string at all?

 

[namliam]

You are using VBA to build an SQL string, what the VBA variable is and the SQL variable/function are two totaly unrelated matters...

If "all" you need / want to do is convert the text wireid to the number Wire_id in the sql statement.... Then you dont need any cast or CInt or whatever.

In SQL your variables are 'typed' by their deliters...
I.e.
Dates: #01-12-2009#
Text: 'This text'

## Makes it a date in SQL, not its format... Note in access this means 12 Jan 2009
'' Makes it a text string in SQL, again not its format.

No delimiter makes it a number... So using something like:
...incoming_wires.wire_id = " & wireid
Will (in SQL) be interperted as beeing a number regardless of the fact that on your form the wireid is a text/date/boolean or number.

The KEY difference here is the transisition from your VBA coding to the SQL beeing generated, those two are distinctly seperated!

I hope this is usefull

 

[MSAccessRookie]

OK, the wireid on the form is a text field. The wire_id is an autonumber field in the incoming_wires table. I am trying to update the incoming_wires table based on the text entered into an edit form using a sql statement. This form also uses sql insert statements to record the changes in an audit table. This part of my code works fine. When I try to do the actual update to the incoming_wires table, however, I am getting a data mismatch error. There is a "cast" function in sql, and that is what I was trying to use. I have tried cInt() and cDbl() with no luck.

Am I incorrect in my thinking that the contents of the string that is being used as a sql command will not follow vba rules? I mean, if I could use vba to do this, why use the sql string at all?

1. I have not heard of a Cast() Function that is supported by MS Access in any version. Perhaps you are referring to a User Defined Function with that name.
2. Since wireid on the form is a Text field, and wire_id in the incoming_wires table is an AutoNumber field, then the Cint() or Clng() Function should change the String to an appropriate Number. The Proper Format for the two Functions is:

IntegerValue=cInt(NumberinStringFormat)
and
IntegerValue=cLng(NumberinStringFormat)
​

 

[jsmith1611]

Mailman, your last post was exactly the kind of useful information I was looking for. Thank you to everyone that replied. I had no intention of offending anyone with my "useful" comment, but when you are already frustrated and you get a comment with no suggestions or even further questions, it isn't very "useful".

 

[jsmith1611]

Ok, I have been able to successfully update the number and text fields in the incoming_wires table, but I am still having trouble with the date field.

Here is my successful number field:

'update incoming_wires.acct
Dim strsql_1a As String
strsql_1a = "update incoming_wires " & _
"set acct = " & Me.newMember & " where " & Me.wireid & "= incoming_wires.wire_id"
DoCmd.RunSQL strsql_1a

Here is my successful text field update:

'update incoming_wires.first
Dim strsql_2a As String
strsql_2a = "update incoming_wires " & _
"set first = '" & Me.newFirst & "' where " & Me.wireid & "= incoming_wires.wire_id"
DoCmd.RunSQL strsql_2a

Here is my date update that is not working:

'update incoming_wires.date
Dim strsql_a As String
strsql_a = "update incoming_wires " & _
"set date = #" & Me.newDate & "# where " & Me.wireid & "= incoming_wires.wire_id"
DoCmd.RunSQL strsql_a

I tried putting the # signs around the date to identify it as such, but I get the following error message:

synax error in UPDATE statement

anyone?

 

[namliam]

Mailman, your last post was exactly the kind of useful information I was looking for. Thank you to everyone that replied. I had no intention of offending anyone with my "useful" comment, but when you are already frustrated and you get a comment with no suggestions or even further questions, it isn't very "useful".

When your frustrated take a step back, look at the post again after ten minutes... Then answer the question asked, the question is asked with a SPECIFIC reason!

The cast if a UDF (user defined function) can be just as easily the source of your problem as the SQL can be. This is why I wanted to know, what is it?
Without answer to that question it is FRIGGING! impossible to help you!

You come here looking for help, then discard the help beeing offered??!!
Even if you are unsure as to the method of the madness, I can assure you there is reason for it... There is order in the chaos even if you do not see it!

 

[namliam]

The problem here might be that DATE is an Access reserved word...
Another may be... What is newDate? Text? Date? Number?
How is it formated?

Also you are turning around the equation in your where, not a problem for the SQL, but might cause a problem for maintenance as not all people will see it for the trees
strsql_a = "update incoming_wires " & _
"set date = #" & Me.newDate & "# where incoming_wires.wire_id = " & Me.wireid

Is slightly better, while on the subject of maintanabilty....
strsql_a = ""
strsql_a = strsql_a & " update incoming_wires "
strsql_a = strsql_a & " set date = #" & Me.newDate & "# "
strsql_a = strsql_a & " where incoming_wires.wire_id = " & Me.wireid
Would increase readability IMHO, thus increase maintainability..., notice too the spaces at the start AND the back end of the lines, which will avoid messy SQL statements like "Update incoming_wiresset" instead of "Update incoming_wires set". Plus starting new lines regularly will keep it readable.

Plus I dont know if you know this or not... but you can update 2 columns in 1 update statement like so:
Update Table

Set Column1 = 'something'
  , Column2 = 26
where PK = 15

 

[jsmith1611]

I will try breaking it up into several lines. The reason I am not creating one update statement is because the edit form allows a user to update one, several, or all of the fields in the main form (and underlying table). Each update statement starts with:

If not isnull(me.newDate) then

This way we are only updating the fields that are necessary and only inserting rows into the audit table for the actual fields that changed.

Regarding my date update, you asked about my newDate field. It is a text field like all of the fields on my edit form.

Thanks again.

 

[namliam]

How about doing something like:

mySQL = "Update table set "
myColumn = ""
If thisField is changed then
    mycolumn = mycolumn & ", ThisField = 12 "
    ' other stuff to log the audit
endif
If thatField is changed then
    mycolumn = mycolumn & ", ThatField = 'hello world' "
    ' other stuff to log the audit
endif
etc....

Now build the SQL, using the Mid function to skip the first comma which you dont need:

mySQL & mid(myColumn,2) & " where yourPK = " & etc...

The advantage of having only 1 update statement is that each individual update statement will take time... If you only do 1 update statement you cut back (potentialy considerably) on the time needed for the update(s).
Even a change from 3 to 2 seconds is very noticable to the (end)user


OK, so newDate is a text field, what format is it?? Remember access "needs" dates to be "MM/DD/YYYY" per my previous "usefull" post.

If that is "proper" try inserting "debug.print strsql_a" before the RunSQL command. Then you can see the actual SQL beeing executed... And post that Perhaps that will help us solve this mystery.

 

[jsmith1611]

Here is my actual sql update statement from the debug window:

update incoming_wires set date = #6/30/2008# where incoming_wires.wire_id =156

Thanks,
Josh

 

[namliam]

The update statement looks good... except for the date beeing a reserved word!
Try explicitly telling access that it is a column in your table, which means changing date to incoming_wires.[date]

I hope that works , cause I am running out of ideas on this one

 

[jsmith1611]

Mailman, that worked like a charm. Thanks again for your patience....

 

[namliam]

Tip:
Dont use reserved words in databases, anything like Date Time Group From etc... Dont use it...
This date could just as easily be TargetDate instead (or something simular) and you wouldnt have run into this problem!

Good luck & Happy to help as always Becuase no matter the "weather" we always deliver