Ошибка 3464 access - Решение и исправление самых разных ошибок на TopOshibok.ru

I am working on a form on Access. It is supposed to insert data into a table, but I keep getting error 3464.

I am a little new to Visual Basic. This was previously working, but no longer. I am using SQL Server as a back-end. The connection works and it was inserting new rows. I have looked online but have not found a comprehensive explanation of this error.

Private Sub mSubmitButton_Click()

    CurrentDb.Execute "INSERT INTO dbo_Reporting_Table([Date], Vendor, Ordered_By, Picked_Up_By, Reported_By) " & _
    " VALUES('" & Me.mDate & "','" & Me.mVendor & "','" & Me.mOrdered_By & "','" & _
    Me.mPicked_Up_By & "','" & Me.mReported_By & "')"

    CurrentDb.Execute "INSERT INTO dbo_Items_Table([PO#], [Item], [Price], [Equipment], [Quantity]) " & _
    " VALUES(" & Me.mPONumber & ",'" & Me.mItemInput0 & "'," & Me.mPriceInput0 & ",'" & _
    Me.mEquipmentInput0 & "'," & Me.mQuantityInput0 & ")"


    Me.mItemInput0 = ""
    Me.mPriceInput0 = ""
    Me.mEquipmentInput0 = ""
    Me.mQuantityInput0 = ""

    Me.mDate = ""
    Me.mVendor = ""
    Me.mOrdered_By = ""
    Me.mPicked_Up_By = ""
    Me.Requery

    Exit Sub

Error_PopupMessage:
    'Response = MsgBox(Message, vbOKOnly + vbInformation, "Form Entry Hint", "help", "1000")
    Exit Sub

End Sub

Any advice on what I might be doing wrong would be greatly appreciated.

Источник

Remove From My Forums

Question

I have two unbound fields, one a combo box for the current station number and a textbox for the new station number. I have two buttons, «Change GW Station» and «Delete GW Station.»

The Change GW Station is working however when I attempt to delete a record I get a «Error 3464 — Data type mismatch in criteria expression.» The data type of the data I am deleting (Station_Number) is text

I don’t understand especially since I am using the same field in the Change procedure and it works fine.

This is the delete event that is throwing the error

Private Sub cmdGWDelStation_Click()
On Error GoTo errhandler

Dim strSql As String
Dim Db As dao.Database

strSql = "DELETE * FROM Section16_5_Data WHERE Section16_5_Data.Sample_Number = '" & cboGWOldStationNu & "' "

Set Db = CurrentDb()


LResponse = MsgBox("Are you sure you want to delete Station Number '" & cboGWOldStationNu & "' " & _
" All sample data associated with station number '" & cboGWOldStationNu & "' will be also be delete.", vbYesNo)
Select Case LResponse
   Case vbYes
          Db.Execute strSql
          MsgBox ("The station number and all associated sample data has been deleted")
   Case vbNo
          MsgBox ("This task has been cancelled")

End Select
updateField = True
ExitHere:

Set Db = Nothing

Exit Sub

errhandler:
   'There is an error return false
   updateField = False
   With err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "updateField"
   End With
   Resume ExitHere
End Sub

jim neal

Answers

Hi,

you have mentioned Station_Number in your post but I can see Sample_Number in your SQL. Is this correct?

Vladimir Cvajniga
- Marked as answer by
  
  Tuesday, February 26, 2013 11:41 AM

Источник

Vladimir_prog

0 / 0 / 0

Регистрация: 14.07.2016

Сообщений: 2

14.07.2016, 23:33. Показов 4458. Ответов 2

Метки sql, запрос, ошибка, с (Все метки)

Всем доброго времени суток.
Задача такова: Нужно выбрать все записи у которых поле «vDate» соответствует значению переменной «dDATE», после чего занести результат во временную таблицу.
Проблема: При выполнении команды «Excecute», в качестве результата я получаю сообщение об ошибке:
«Run-time error ‘3464’ Не соответствие типов данных в выражении условия отбора»
Прошу помощи разобраться с причиной возникновения этой ошибки.
P.S. 1. Временная таблица «Temp_WeeklyReport» имеет все те же поля что и таблица «021».
2. Поле «DateValue» таблицы «Table_Temp1» имеет тип Date, соответственно.

Visual Basic

dim dDATE As Date
 
vDATE = DLookup("DateValue", "Table_Temp1")
 
CurrentDb.Execute "INSERT INTO Temp_WeeklyReport SELECT * FROM 021 WHERE vDate = '" & dDATE & "'"

Буду очень благодарен за помощь, а то уже 2 дня с этим вожусь. :wall:

mobile

26792 / 14471 / 3192

Регистрация: 28.04.2012

Сообщений: 15,782

15.07.2016, 00:27

Сообщение было отмечено Vladimir_prog как решение

Решение

Vladimir_prog, дата не текст, дата в Access поле типа Double — числовое поле с плавающей запятой. К тому же дату в ВБА надо задавать по американскому формату: mm/dd/yyyy. И указатель типа даты это решетки «#». Следовательно выражение для Where надо переписать так:

Visual Basic

1 2	CurrentDb.Execute "INSERT INTO Temp_WeeklyReport SELECT * FROM [021] " _ & "WHERE vDate = " & Format(dDATE, "\#mm\/dd\/yyyy\#")

Обратный слэш указывает, что следующий символ не системный

0 / 0 / 0

Регистрация: 14.07.2016

Сообщений: 2

15.07.2016, 01:25

[ТС]

mobile, большое спасибо за столь быстрый и рассписаный ответ !
Всё верно, всё работает.

Источник

I am working on a database for my work and i’m trying to insert and update values from tables with sql inside the vb editor
This is my code:

Option Compare Database

Private Sub Übernehmen_Click()
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim ArtikelNr As Integer
    Dim Stück As Integer
    Dim Lieferant As String
    Dim Bestellnr As Integer
    Dim EkPreis As String
    Dim Mwst As String
    Dim Einkaufsort As String
    Dim GhIndex As String
    Dim Datum As String
    Dim Uhrzeit As String
    Dim Lager As String
    Dim Beschreibung As String

    ArtikelNr = [Forms]![Einkauf]![ArtikelNr].Value
    Stück = [Forms]![Einkauf]![Stück].Value
    Lieferant = [Forms]![Einkauf]![Lieferant].Value
    Bestellnr = [Forms]![Einkauf]![Bestellnr].Value
    EkPreis = [Forms]![Einkauf]![EK-Preis].Value
    Mwst = [Forms]![Einkauf]![Mwst-Satz].Value
    Einkaufsort = [Forms]![Einkauf]![Einkaufsort].Value
    GhIndex = [Forms]![Einkauf]![GH-Index].Value
    Datum = [Forms]![Einkauf]![Datum].Value
    Uhrzeit = [Forms]![Einkauf]![Uhrzeit].Value
    Lager = [Forms]![Einkauf]![Lager].Value

    strSQL1 = "INSERT INTO Einkäufe (ArtikelNr, Stück, Lieferant, Bestellnr, EKPreis, MwstSatz, Einkaufsort, GHIndex) VALUES (" & ArtikelNr & "," & Stück & ",'" & Lieferant & "','" & Bestellnr & "','" & EkPreis & "','" & Mwst & "','" & Einkaufsort & "','" & GhIndex & "');"

    Beschreibung = DLast("EinkaufID", "Einkäufe")

    strSQL2 = "INSERT INTO Transaktionen VALUES ('" & ArtikelNr & "','" & Datum & "','" & Lager & "','" & Stück & "','EinkaufID ' + '" & Beschreibung & "' ,'Einkauf',NULL,NULL,'" & Uhrzeit & "');"
    strSQL3 = "UPDATE Lagerbestand SET Stück = Stück+" & Stück & " WHERE ArtikelNr = '" & ArtikelNr & "' AND Lager = '" & Lager & "';"

    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3

End Sub

After trying to press the button it first adds the two entries and stops at the third one just to throw an error saying «Runtime Error: 3464».
After I press debug it marks the line DoCmd.RunSQL strSQL3.

I would appreciate any answer I get.
Many thanks in advance.

Источник

Remove From My Forums

Question
I am trying to print a report I am recieving an emrro Run-tim error 3464: Data type mismatch in criteria expresson. How can I debug this error so that I can be able the report in Access?
- Moved by
  
  Thursday, July 12, 2012 1:38 AM
  moving to a more appropriate forum (From:Excel IT Pro Discussions)

Answers

Hi,

As your code, I found that your SQL is a select statement, and based on my try, I think the CurrentDb.Execute
cannot run a “Select” SQL statement. Since the “dbFailOnError” means
Rolls back updates if an error occurs. A “select” SQL statement will not update any object. I think we can use DoCmd.RunSQL strSQL instead.

About this method, please refer to the following link:

http://msdn.microsoft.com/en-us/library/ff194626.aspx

And in your code “strSQL = rs![Col1]”. This statement will change the strSQL statement, it will return the result of the select statement. Then use this result of the select statement
will cause an error.

Jaynet Zhang

TechNet Community Support
- Marked as answer by
  Jaynet Zhang
  Thursday, July 19, 2012 3:03 AM

Источник

Question

Answers

Решение

Question

Answers