-1

I want to run the below query through Access-VBA and I receive the error in the attached screen. Any ideas how to solve it?

deletenew = ("DELETE * FROM TEST1 where [ADAM] = " & luadam & " and [Last_Update_Date]=" & " " & "")

DoCmd.RunSQL deletenew

Thanks in advance,

Andreas

AHeyne
  • 3,377
  • 2
  • 11
  • 16
Andreas Zaras
  • 125
  • 4
  • 1
    Stop execution after setting `deletenew` and then run this in immediate window to see the content of the variable: `Debug.Print deletenew`. Then you should see the problems. 1. Field `ADAM` seems to be a string and you need to embed the value of `luadam` in `'`. 2. For the field `Last_Update_Date` you just provide no value at all. – AHeyne Jan 22 '21 at 07:12
  • 1
    Concatenated parameter for text field needs apostrophe delimiters. Concatenated parameter for date/time field needs # delimiters. There is no date parameter provided. – June7 Jan 22 '21 at 07:19
  • Hello! Problem is solved by putting the where statements in '. deletenew = ("DELETE * FROM TEST1 where '[ADAM] = " & luadam & "' and '[Last_Update_Date] is null';") – Andreas Zaras Jan 22 '21 at 07:36
  • You also should read this regarding ado or dao to avoid the risk of sql injection in your queries: https://stackoverflow.com/a/49509616/7658533 – AHeyne Jan 22 '21 at 08:09
  • If your problem can be written in text format, it should be presented in text format, not image. In particular, in the case of the error log, it should be possible to search. See the guide below on stackoverflow. [how-to-ask](https://stackoverflow.com/help/how-to-ask) / [DO-NOT-UPLOAD-IMAGES](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question/285557#285557) – myeongkil kim Jan 22 '21 at 12:22

1 Answers1

1

Your query is not properly written. This:

"where [ADAM] = " & luadam & " and [Last_Update_Date]=" & " <<MissingValue>> " & ""

where <<MissingValue>> is not valid date. MS Access database can NOT translate " " into date.

should be replaced with:

"where [ADAM] = '" & TextValueHere & "' and [Last_Update_Date]= #" & ISODateHere & "#"

More at: Examples of using dates as criteria in Access queries

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • Thnaks for your answer! if in the place of ISODATE i want to enter null how can i do it? – Andreas Zaras Jan 22 '21 at 07:54
  • Use [ISNULL](https://www.techonthenet.com/access/functions/advanced/isnull.php): `ISNULL([Last_Update_Date])` :D – Maciej Los Jan 22 '21 at 07:56
  • `& " and [Last_Update_Date] Is Null")` – June7 Jan 22 '21 at 08:30
  • `and [Last_Update_Date] Is Null` should be OK. – Maciej Los Jan 22 '21 at 08:31
  • Still Have problems with the VBA query: ludate = rs("Last_Update_Date").Value luadam = rs("ADAM").Value deletenew = ("DELETE * FROM TEST1 where ([ADAM]) = " & luadam & " and isnull([Last_Update_Date]);") DoCmd.RunSQL deletenew I get error 3075 – Andreas Zaras Jan 22 '21 at 08:34
  • You should check what value holds `ludate` variable. Then, depending on it, you shoud create proper `DELETE` statement. In other words, if `ludam` is `nothing` then create `DELETE` statement with `ISNULL()` function, otherwise, with `#ISODate#`. :) – Maciej Los Jan 22 '21 at 08:38
  • And still omitting apostrophe delimiters: `WHERE [ADAM]='" & luadam & "' AND `. Review https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 Jan 22 '21 at 09:05