Error in query

[Jorge Novoa]

Hello everyone!

I got a little error trying to execute this query:

DoCmd.RunSQL "UPDATE caso SET caso.CasoId ='" & Me.cbxGDestino & "-" &
Trim(str(Caso.Deudor)) & "', caso.Gestor ='" & Me.cbxGDestino & "',
caso.FReAsignacion ='" & Format(Me.datFAsignacion2, "dd/mm/yyyy") & "' " _
& "WHERE (((caso.Gestor)='" & Me.cbxGOrigen & "'));"

An error message comes uo telling me that 'the variable has not been
defined, inside the trim funcion *Trim(str(Caso.Deudor))*, highlighting the
field: Caso.Deudor.

I've tried to remove the trim() and str() funcions, leaving only tthe field
caso.deudor.

But It ssems that the query doesn't recognize the field DEUDOR.. which is
indeed a field of the CASO table.
How can I solve this???

Please help me!

thanx a lot

Jorge Novoa

 

[Guest]

Hi Jorge

You need to move the Caso.Deudor inside your quotes. At the moment it is
being evaluated as VBA code and therefore treated as a variable. You need to
make it part of the string being passed to the RunSQL so it will be treated
as a table.column reference.

Use...

DoCmd.RunSQL "UPDATE caso SET caso.CasoId ='" & Me.cbxGDestino & "-' " & "&
Trim(str(Caso.Deudor)), caso.Gestor ='" & Me.cbxGDestino & "',
caso.FReAsignacion ='" & Format(Me.datFAsignacion2, "dd/mm/yyyy") & "' " _
& "WHERE (((caso.Gestor)='" & Me.cbxGOrigen & "'));"

Note carefully how I have moved your single quote next to the - and that the
single quote is followed by a space. Also, I have placed an & followed by a
space after the next double quote just before Trim.

Hope this helps

Regards

Andy Hull

 

[krissco]

Hello everyone!

I got a little error trying to execute this query:

DoCmd.RunSQL "UPDATE caso SET caso.CasoId ='" & Me.cbxGDestino & "-" &
Trim(str(Caso.Deudor)) & "', caso.Gestor ='" & Me.cbxGDestino & "',
caso.FReAsignacion ='" & Format(Me.datFAsignacion2, "dd/mm/yyyy") & "' " _
& "WHERE (((caso.Gestor)='" & Me.cbxGOrigen & "'));"

An error message comes uo telling me that 'the variable has not been
defined, inside the trim funcion *Trim(str(Caso.Deudor))*, highlighting the
field: Caso.Deudor.

I've tried to remove the trim() and str() funcions, leaving only tthe field
caso.deudor.

But It ssems that the query doesn't recognize the field DEUDOR.. which is
indeed a field of the CASO table.
How can I solve this???

Please help me!

thanx a lot

Jorge Novoa

Try Caso.[duedor]

Will the str or trim function fail on null? You might want to try
trim(str(nz(caso.[duedor], "")).

Also, what is the purpose of str? Should it be Cstr? Is it even
necessary?

-Kris