SQL Date Search

[Federico_Giannetti]

Hello Group. I would like to compare the database dates, with a constructed date, but it doesn’t work.

// Create my date 
Dim DataEsaminata as new date
DataEsaminata.Year=2022
DataEsaminata.month =05
DataEsaminata.day=03
'messagebox("Confronto la data di calendario =" + dataesaminata.shortDate)





Var rows As RowSet
dim DataFormattata as new date

Try
  rows = db.SelectSQL("SELECT * FROM Promemoria where Data="+dataesaminata.shortDate)
  If rows <> Nil Then
  For Each row As DatabaseRow In rows
         messagebox "Trovata data=" + dataesaminata.shortDate
  Next
  rows.Close

Where am I wrong?

[Kem_Tekinay]

Use .SQLDate instead of .ShortDate.

And that SQL would generate an error anyway because you aren’t quoting it. Try it like this instead (assuming SQLite):

rows = db.SelectSQL("SELECT * FROM Promemoria where Data=?", dataesaminata.SQLDate)

[Federico_Giannetti]

I TEST WITH
rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=”+dataesaminata.SQLDate)

BUT THE PROGRAM RETURN ERROR AND NONT START.

IF I TEST WITH:
rows = db.SelectSQL(“SELECT * FROM Promemoria where ID=16”)

Program RUN.

SURE, the problem is comparsion between dates.

[AlbertoD]

@Federico_Giannetti

rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=”+dataesaminata.SQLDate)

@Kem_Tekinay

And that SQL would generate an error anyway because you aren’t quoting it

You need to change that line of code.

[Federico_Giannetti]

@Kem_Tekinay

quoting

Guys sorry, I don’t understand. How should I quoting SQL?

[AlbertoD]

Can you change this:

@Federico_Giannetti

rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=”+dataesaminata.SQLDate)

to this:

@Kem_Tekinay

rows = db.SelectSQL("SELECT * FROM Promemoria where Data=?", dataesaminata.SQLDate)

Maybe you have an error in another place too. If you post a sample project maybe someone can take a look.

[Kem_Tekinay]

Did you try the way I posted it?

[Tom_Dixon]

rows = db.SelectSQL(“SELECT * FROM Promemoria WHERE Data=’”+dataesaminata.SQLDate + “’”)

[Federico_Giannetti]

Ok i change the code in:

rows = db.SelectSQL(“SELECT * FROM Promemoria WHERE Data=’”+dataesaminata.SQLDate + “’”)

But return an error . I USE SHORT DATE.

[AlbertoD]

Your image doesn’t include information about what the error is, so is hard to know. Maybe you are not connected at this point to the db?

[Federico_Giannetti]

It’s a problem with the date because if I replace with … where ID = 16 it works.

[AlbertoD]

If you can share a sample project, I can take a look and let you know what needs to be changed.

[Federico_Giannetti]

CERTAINLY, only that I have the project on my pc in the office, tomorrow I’ll copy it and update the link. Thanks.

[Nedi_Freguglia]

Il valore della data da confrontare (SQLDate) va racchiuso tra apici.

[Nedi_Freguglia]

Se stai eseguendo la query su Access bisogna utilizzare DATEVALUE (sto andando a ricordi)
“SELECT * FROM Promemoria where Data=DATEVALUE(’”+dataesaminata.SQLDate + “’)”
Prova così…

[Federico_Giannetti]

nothing, it gives me an error even with the string:

rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=DATEVALUE(”+dataesaminata.SQLDate + “)”)

I am attaching the project, so you can see where the error is. TEST.rar | Files.fm.

[Xavier_Mass]

What is the contents of error.Message ?

[Emile_Schwarz]

WARNING to helpers:

The project uses “Microsoft Access”…

[Federico_Giannetti]

[Federico_Giannetti]

Guys, maybe I understand the problem. If I read the date from the database without formatting it, it comes out as LongDate. So I should be able to format it in the query so that it reads as ShortDate and be able to compare it with the search query. It can be done ?

So, I think this is the problem. I display the short date, but in the DB it is recorded as long / complete … when I enter the comparison date which is short, it does not find it.