Recordset field error? 1

[jordanking]

Hello,

I am gathering a adodb recordset from Access 2003 in order to update a remote mysql database (via myODBC).

I have the connection and update query working properly.

The problem that occurs is that one specific field produces an error. The table that I am pulling from has 21 fields. The field prodcing an error is the only "date" field.

So I have removed it from the update sql and everything works. I isolated the field in the following code in order to reproduce the error:

dtUpdate = Format(rsCustUpdate.Fields("dtUpdated"), "yyyy-mm-dd hh:nn:ss")

I am trying to get the date value and format it into a mySQL format. But when the code gets to the recordset field call, the following error occurs:

Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal.

I normally get this error when i misspell the field but i have triple checked the spelling.

I am not sure where to go next in the debugging of this problem. I need some help in order to determine what is going wrong.

Thank you in advance

JK

 

[SkipVought]

Hi,

You do realize that when you use the Format function it returns a STRING and not a REAL DATE.

I don't know what kind of field you are trying to reference in the SQLDB in your query, but you may have a date conversion issue.

Skip,

_[red][/red]

 

[jordanking]

I had a feeling that was true,

this is how I imbedded the format function in the original query

customer.dtUpdated = '" & Format(rsCustUpdate.Fields("dtUpdated"), "yyyy/mm/dd hh:nn:ss") & "'

the dtUpdated field is a Date/Time field from access.

Does this help?

 

[PHV]

And what about this ?
customer.dtUpdated = [!]#[/!]" & Format(rsCustUpdate.Fields("dtUpdated"), "yyyy/mm/dd hh:nn:ss") & "[!]#[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Here is the entire SQL

                sqlCustSync = "UPDATE customer SET customer.lngzCompanyID = '" & rsCustUpdate.Fields("lngzCompanyID") & "', customer.txtFirstName = '" & rsCustUpdate.Fields("txtFirstName") & "', customer.txtLastName = '" & rsCustUpdate.Fields("txtLastName") & "', customer.dblAccNum = " & rsCustUpdate.Fields("dblAccNum") & ", customer.txtPhoneNumber = '" & rsCustUpdate.Fields("txtPhoneNumber") & "', customer.lngzphoneCode = " & rsCustUpdate.Fields("lngzphoneCode") & ", customer.lngzPostID = " & rsCustUpdate.Fields("lngzPostID") & ", customer.txtHangers = '" & rsCustUpdate.Fields("txtHangers") & "', customer.txtCstmrInstuc = '" & rsCustUpdate.Fields("txtCstmrInstuc") & "', customer.numHangers = " & rsCustUpdate.Fields("numHangers") & ", customer.lngzPayment = " & rsCustUpdate.Fields("lngzPayment") & ", customer.curSpecial = " & rsCustUpdate.Fields("curSpecial") & ", customer.numInvoiceType = " & rsCustUpdate.Fields("numInvoiceType") & "," & _
                         " customer.memInfo = '" & rsCustUpdate.Fields("memInfo") & "', customer.lngSDBID = " & rsCustUpdate.Fields("lngSDBID") & ", customer.webID = " & Nz(rsCustUpdate.Fields("webID"), 0) & ", customer.blnErequest = " & rsCustUpdate.Fields("blnErequest") & ", customer.lngSDBID = " & rsCustUpdate.Fields("lngSDBID") & ", customer.lngzAccountStatus = " & rsCustUpdate.Fields("lngzAccountStatus") & ", [COLOR=red]customer.dtUpdated = '" & Format(rsCustUpdate.Fields("dtUpdated"), "yyyy/mm/dd hh:nn:ss") & "'[/color]" & _
                         " WHERE (((customer.idsCustomerID)=" & rsCustUpdate.Fields("idsCustomerID") & "));"

 

[jordanking]

PHV,

The query is being passed to the myODBC connection, so I need the query string to follow mysql formats. In short, I tired it and it did not work.

Thanks

 

[PHV]

And this ?
customer.dtUpdated = '" & Format(rsCustUpdate.Fields("dtUpdated"), "yyyy[!]-[/!]mm[!]-[/!]dd hh:nn:ss") & "'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Yes, that is acutally the first format I tried because mySQL prefers the dash seaperated dates. The version I posted was a second try at using slashes just to make sure the dashes were not the problem. It does not work as well.

 

[jordanking]

this is the sql that obtains the value of the date field

    sqlCustUpdate = "SELECT tblCustomer.*, tblCustomer.dtUpdated FROM tblCustomer WHERE (((tblCustomer.dtUpdated) Between #" & dtLstSync & " " & tmLstSync & "# And #" & Format(Now(), "m/d/yyyy hh:nn:ss AM/PM") & "#));"

 

[PHV]

Why repeating tblCustomer.dtUpdated as you use * ?
sqlCustUpdate = "SELECT * FROM tblCustomer WHERE dtUpdated Between #" & dtLstSync & " " & tmLstSync & "# And #" & Format(Now, "yyyy-mm-dd hh:nn:ss") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

okay,

Got it to work,

For some reason, removing the "tblCustomer.dtUpdated " from the query made everything work. I am not sure why, But why question a good thing?

 

[jordanking]

sorry, phv, just posted when you did,

thank you, your post is the solution, star for you

JK