Run Time Error 3464 - Don't Understand the Error

[angeltink99]

I have a monster of a database at work and needed to adjust a bit of the program language. I am now getting a run-time error, but only some of the time!! To premise this, the code is all part of a process where we import data off a 3rd party disc, format it through access to what we need per our access requirements, then append the data to our table. The import is anywhere from 60 to 700 rows into our table numbering in the thousands. I uploaded 3 chunks of data without running into the error. Then, I tried another (60 records) chunk and it hit this error. Went back a tried a previous chunk and it still worked. I'm not sure why it is erroring out here, and why only some of the time. Can anyone please shed some light on this? And you have to talk like I'm 2, as I have very little background and am learning as I go.

Here is the code. The portion that breaks is in red!

Private Sub Form_Close()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim dbs As Database, rst As DAO.Recordset
Dim strsql As String

Set dbs = CurrentDb
Msg = "Do You Want To Continue With Import Process?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Import Decision"
Help = "DEMO.HLP"
Ctxt = 1000

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes. IF PROMPT 1


Set dbs = CurrentDb
DtImportTBL_CD_ATT_DATA = Date$


strsql = "SELECT [TBL_CD_AT&T_DATA_ADJ].*, [TBL_CD_AT&T_DATA_ADJ].[Billing Number] FROM [TBL_CD_AT&T_DATA_ADJ] LEFT JOIN ETSBLOG ON [TBL_CD_AT&T_DATA_ADJ].[Billing Number] = ETSBLOG.BILLNUM WHERE (((ETSBLOG.BILLNUM) Is Null) AND (Not Left([TBL_CD_AT&T_DATA_ADJ]![Billing Number],3)= 960));"



Set rst = dbs.OpenRecordset(strsql)

If rst.RecordCount > 0 Then
msgtbl = "You Have Invoice Records Without Bill Numbers"
MsgBox msgtbl
StrTBL_CD_ATT_DATA_NoBILLNUM_Cnt = rst.RecordCount

DoCmd.OpenQuery "8A_QRY_TBL_CD_ATT_DATA_ADJ Without Matching ETSBLOG"


rst.Close
Set dbs = Nothing

DoCmd.OpenReport "RPT_TBL_CD_ATT_DATA_ADJ_NON_MATCH_ETSBLOG", acViewPreview

Else
msgtbl = "You Have No Authorized Amounts Differences."
MsgBox msgtbl

DoCmd.OpenQuery "9D_Qry_Chk_Dup_ETSINV_TBL_CD_ATT_DATA_ADJ"

strsql = "SELECT * FROM TBL_CD_ATT_DATA_ADJ_DUPS"
Set rst = dbs.OpenRecordset(strsql)

If rst.EOF = False Then
rst.MoveLast
End If

If rst.RecordCount > 0 Then
StrTBL_CD_ATT_DATA_DupRecs = rst.RecordCount

msgtbl = "IMPORT ERROR -> You Have These Records Already In The ETSINV Table!"
MsgBox msgtbl

DoCmd.OpenReport "RPT_DUP_ATT_DATA_ADJ_ALREADY IN ETSINV", acViewPreview

Else
msgtbl = "You Have No Duplicate ETSINV Import Errors! Appending TBL_CD_ATT_DATA_ADJ Records To ETSINV Table"
MsgBox msgtbl
DoCmd.OpenQuery "9F_Qry_Map_CD_ATT_ADJ_ETSINV"
Set dbs = CurrentDb
strsql = "SELECT [TBL_CD_AT&T_DATA_ADJ].* FROM [TBL_CD_AT&T_DATA_ADJ]"
Set rst = dbs.OpenRecordset(strsql)

If rst.EOF = False Then

rst.MoveLast

End If

StrTBL_CD_ATT_DATA_InCount = rst.RecordCount
rst.Close


Set dbs = CurrentDb
strsql = "SELECT * FROM TBL_Import_Status"
Set rstStatus = dbs.OpenRecordset(strsql)




End If
Set dbs = Nothing


End If

Set dbs = Nothing

Else ' User chose No. ELSE PROMPT 1
msgtbl = "Import Process Is Stopped!"
MsgBox msgtbl
End If ' ENDIF PROMPT 1
End Sub

 

[GohDiamond]

This is a Datatype mismatch and it breaks your SQL string because a type of data expected in the string is not formated as it wants to see it, often an issue of a NON NUMBER being in a number field coming from the raw data; or perhaps a NULL when it's expecting TEXT. These are the instances I've run into that have produced this and other datatype mismatch errors.

I'd take a look at the 60 records in the batch that causes the error, focusing on the BILLNUM in each of the joined tables.

Cheers!
Goh

 

[angeltink99]

After all my reading yesterday, I figured as much. But I can't find the difference in the data anywhere. I looked at the design of the raw data table and compared it to the last table referenced in the code, which is where the data gets dumped into. All the data field types look consistent, all fields that could have been null where filled with a 0 in the raw data in order to have a number in the number data type fields. Is there something that I can do to help locate better exactly where the code is breaking down within the data?

 

[GohDiamond]

Open the VB editor. put some break points on and around the strsql statement include a few lines above and below the actual statement and 'step thru' the process (you can do an entire subsection related to the sql statement if you want, step thru the process when it stops by clicking on the > in the tool bar).

When the problem occurs you'll see where you were at in the code when it occured. If you pinpoint the part of the code that is causing the issue as data is being processed you can then use the immediate window to check the content of variables and fields that are involved in that part. You'll troubleshoot the variables using something like 'debug.print strsql' or '? Date$' or any other variable that was used. Depending on what you see you may find what data is being read into a variable that is causing the issue.

Good Luck
Goh

Let us know what you find.

 

[angeltink99]

Okay, so I ran the following portion of the code with breaks at each of the emoticons below:

Set dbs = CurrentDb
DtImportTBL_CD_ATT_DATA = Date$


strsql = "SELECT [TBL_CD_AT&T_DATA_ADJ].*, [TBL_CD_AT&T_DATA_ADJ].[Billing Number] FROM [TBL_CD_AT&T_DATA_ADJ] LEFT JOIN ETSBLOG ON [TBL_CD_AT&T_DATA_ADJ].[Billing Number] = ETSBLOG.BILLNUM WHERE (((ETSBLOG.BILLNUM) Is Null) AND (Not Left([TBL_CD_AT&T_DATA_ADJ]![Billing Number],3)= 960));"



Set rst = dbs.OpenRecordset(strsql)

The code ran through the first break. Then it ran all the way through the second break. Once done, the yellow highlighted line and arrow sat over the line in red above. Still no error. Then, when I hit the next step through, it gave the error.

I'm not sure I understand what to do and why the code is blowing up where it is...on the set rst = etc statement.

Any advise on what to do now?

 

[RuralGuy]

If [TBL_CD_AT&T_DATA_ADJ]![Billing Number] is a numeric field then you cannot use a string function on it until you convert it to a string.
(Not Left([TBL_CD_AT&T_DATA_ADJ]![Billing Number],3)= 960
On the other hand, if it *is* a string (text) field then you will need to enclose the 960 in quotes: '960'.

 

[spikepl]

Since you have a set of 60 records that causes trouble, and you are not able to identify the specific record by visual inspection, then do this:

1. Copy your db
2. Import the first 30 records. If error, then the fault lies in this set, so now try importing just the first 15. And so on - just half the numbers of records each time until no fault, and then the fault is in the second half. In six steps or so you will have identified the first record that causes trouble.

 

[gemma-the-husky]

I would try designing a "visual" query in access to select this lot

strsql = "SELECT [TBL_CD_AT&T_DATA_ADJ].*, [TBL_CD_AT&T_DATA_ADJ].[Billing Number] FROM [TBL_CD_AT&T_DATA_ADJ] LEFT JOIN ETSBLOG ON [TBL_CD_AT&T_DATA_ADJ].[Billing Number] = ETSBLOG.BILLNUM WHERE (((ETSBLOG.BILLNUM) Is Null) AND (Not Left([TBL_CD_AT&T_DATA_ADJ]![Billing Number],3)= 960));"

and then simply this should work. Note that if the backend is SQLServer and not Jet then the openrecordset statmeent is probably incorrect.

Set rst = dbs.OpenRecordset("mystoredquery")

 

[事工不可能]

Yeah, I'm having the same problem. I try to import some data from access and I get the data mismatch error 3464. Below is the code that has the Data Type Mismatch in the criteria expression:

'Imports a temporary table containing the data
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CellPhoneLedger(Temp)", "Z:\Cell Phone Database and Management\IMPORT\CellData.XLS", False


'Creates a string to pull the data from the temporary table and put it into the main
sqlString = "INSERT INTO CellPhoneLedger([Phone #], [(Date)], [(Start Time)],

, [(End Time)], [Dialed #], [Seconds], [Cost])  " _
        & "SELECT Trim([CellPhoneLedger(Temp)].[F11]),Left([CellPhoneLedger(Temp)].[F13],3) + 1911 & '/' & Mid([CellPhoneLedger(Temp)].[F13],4,2) & '/' & Right([CellPhoneLedger(Temp)].[F13],2) AS TempDate,Left([CellPhoneLedger(Temp)].[F14],2) & ':' & Mid([CellPhoneLedger(Temp)].[F14],3,2) & ':' & Right([CellPhoneLedger(Temp)].[F14],2) AS TempStart, [CellPhoneLedger(Temp)].[F15],Left([CellPhoneLedger(Temp)].[F18],2) & ':' & Mid([CellPhoneLedger(Temp)].[F18],3,2) & ':' & Right([CellPhoneLedger(Temp)].[F18],2) AS TempEnd, Trim([CellPhoneLedger(Temp)].[F20]), [CellPhoneLedger(Temp)].[F30], [CellPhoneLedger(Temp)].[F22]" _
        & " FROM [CellPhoneLedger(Temp)] ;"
        
[/FONT][FONT=Courier New][COLOR=Red]If DLookup("[(Date)]", "[ImporterWorkAround]") <> "" Then[/COLOR][/FONT][FONT=Courier New]
        MsgBox "You've already imported that data", vbOKOnly, "Import Error"[/FONT]

The red code is where the debugger took me.  I don't see anything wrong with it.  Can you help me recognize the data type mismatch in the red code?

Thank you

 

[vbaInet]

DLookup() can return Null so always wrap it up in Nz()

If [COLOR=Red]Nz([/COLOR]DLookup("[(Date)]", "[ImporterWorkAround]"), vbNullString[COLOR=Red])[/COLOR] <> "" Then

By the way, (Date) is a strange name for a field.

 

[事工不可能]

We copied the above code wrapped in Nz() and tried it out but the same error highlighting the same code came up.

 

[vbaInet]

I don't see any obvious data type mismatch in your code. If you upload a stripped down version of your db I will have a quick look.