SQL Date Search

Targets Windows
21

Here is your problem:

image
image783×386 71.5 KB

Click on DatabaseException and it may give you more information. I don’t think the problem is the date format, the error is before executing the command on the database.

I haven’t downloaded your program, I don’t have Microsoft Access but I will take a look later today to see if I can help more.

Edit: sorry I can’t help more, I can’t load the database to see the DatabaseException that you are getting:
image
If you can change your sample code to use SQLite and you get the same error then I can help.

22

I was able to find an online tool to change the Access database to SQLite. After fixing a couple of problems for non-English systems, I don’t get an error there.

Also added the db ID to the calendar:
image

image

ID 24, 8 and 16 are part of May (the code test each day of the month to see if there is a record on the db).

23

Ok, but the database that I have in Access … that of the Calendar is only a part, there are many other tables … It is something related to the date, because I have simplified the code, written for a search from the Object field and everything works … but with the date field I still can’t.


Var RecordFinder As RowSet
'RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data=DATEVALUE("+dataesaminata.shortdate + ")")
RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Oggetto='Prova2'")

Dim i as integer=0
While Not RecordFinder.AfterLastRow
  i=i+1
  'Conta i campi del record
  'For i As Integer = 0 To RecordFinder.LastColumnIndex
  'dataList.CellTextAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
  'messagebox i.ToString
  'Next
  
  RecordFinder.MoveToNextRow
Wend
messagebox " Ci sono " + i.ToString + " records che soddisfano la ricerca"

RecordFinder.Close
24

I’m sorry I can’t help you fix your problem with Access database, don’t have experience nor installed here.

A couple of things you can do:

  • in Promemoria Opening event you have d.ShortDate, this will later create year as 22 and not 2022, so your SQLDate later is 0022-05-18 for today and not 2022-05-18
  • as you said, the date field on your database has SQLDateTime, if the Time is always 00:00:00 then you may try to use dataesaminata.SQLDateTime
  • if that doesn’t work, maybe you can try DATA LIKE instead of DATA =

I hope something in this post helps you.

25

Okay, in the meantime, thanks for your help. I got around the problem with the following code. But there is no way that there is no way to create a condition for the query.

Var RecordFinder As RowSet
dim DataFormattata as new date

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria")

Dim i as integer=0
While Not RecordFinder.AfterLastRow
  DataFormattata=RecordFinder.Column("Data").DateValue
  if  dataesaminata.shortDate=DataFormattata.ShortDate then
    messagebox "Trovata corrispondenza fra le date " + DataFormattata.ShortDate
    i=i+1
  end if
  RecordFinder.MoveToNextRow
Wend
messagebox " Ci sono " + i.ToString + " records che soddisfano la ricerca"

RecordFinder.Close
26

On your query with condition (the one you get an error) can you click here and report back?

sqldatesearch
sqldatesearch793×396 85.9 KB

That may give an idea what is wrong. You can test your different queries to see if you get different errors there.

27

Errore
Errore1132×238 12.5 KB

But I’m not sure if the query is the right way

28

Access is unique in that dates are surrounded by hash tags ‘#’. Creating a simple query in the query designer using a date gives the following SQL:

SELECT Person.Key_ID, Person.EMT_Expire
FROM Person
WHERE (((Person.EMT_Expire)=#12/31/2023#));

This query is successful.

Revise your query to include the hashes. You may have to play around with date formats.

Dennis

29

If that causes an error,

is dataesaminata Nil?
Is dataesaminata a Date Field?

In Access, dates are stored internally as a double representing time elapsed since (I think) 1970

To compare dates in Access SQL , you need to format the date to look like this

#5/2/2012# (5 Feb 2022)

eg
'SELECT * FROM Promemoria where Data = #5/2/2022# '

30

After hitting my head on it all day, maybe I settled with

RecordFinder = db.SelectSQL (“SELECT * FROM Reminder where Data = #” + ExaminedDate.SQLDate + “#”)

I continue tomorrow. now unfortunately I have to leave.

Thanks guys, you are too good :slight_smile:

31

After hitting my head on it all day, maybe I settled with

RecordFinder = db.SelectSQL (“SELECT * FROM Reminder where Data = #” + ExaminedDate.SQLDate + “#”)

I continue tomorrow. now unfortunately I have to leave.

Thanks guys, you are too good :slight_smile:

32

Unless you can trust the date values 100%, this is a dangerous thing to do as you are opening yourself up to an SQL injection attack. See SQL injection - Wikipedia

33

Il contenuto di “dataesaminata” va racchiuso fra apici singoli

34

I believe the original error is due to Access DateValue requires a string with quotes around it. Your code does not include those quotes.

DateValue(2022-05-18) // error
vs.
DateValue(“2022-05-18”)

Try

db.SelectSQL("SELECT * FROM Promemoria where Data=DATEVALUE("""+dataesaminata.shortdate + """)")
35

To do this in a safe fashion, use parameters instead of building the string yourself. It’s a small (but very important) change:

db.SelectSQL("SELECT * FROM Promemoria where Data=?", "DATEVALUE(""" + dataesaminata.shortdate + """)" )

1 Like