Next problem with date , I do not understand

Hello group, after a few months, I resumed my schedule, I find myself again fighting with dates. I have a box from which I take the date in EUROPEAN format, therefore dd/mm/YYYY and register it as a stringvalue in the date field.


But it marks me as an error because something is wrong … the error that marks me depends on the format, in fact it transforms it and reads it as

SELECT * FROM PurchaseInvoiceHeader WHERE InvoiceNumber='568' and Company='Delta' and Data=#2022-12-31#.

How do I get the date #2022-12-31# ? It should log the date 01/30/2023!!

I tried with ParseDate and the same happens
where am i wrong?

What error are you getting?
Do you have a sample to review the code/error?
What database are you using?
Column “Rata1” is date field or text field?
Are you trying to save the date as short date like 30/01/2023? or is ConvertToDateTime saving it like SQLDateTime (I guess time will be 00)?

Not sure what you mean here.

Yes, I should record a short date (EUROPEAN FORMAT dd/mm/YYYY). But I don’t understand why now it’s giving me problems, I managed to enter some dates, but now it’s giving me problems … I don’t understand where I’m wrong.
The field that i used in my database msAccess .mdb, is datetime type.
I don’t understand if the error depends on the format of the date in the search expression …

sql = "SELECT * FROM " +TabellaDiAppartenenza+" WHERE NumeroFattura='" + scadenziario.TextField1.Text.ConvertEncoding(Encodings.WindowsANSI)+"' and Ditta='" + scadenziario.TextField3.text.ConvertEncoding(Encodings.WindowsANSI) +"' and Data=#"+ ConvertToDateTime(scadenziario.textfield2.Text.ConvertEncoding(Encodings.WindowsANSI)).ShortDate+"#"
Var RecordUpdate As RowSet = db.SelectSQL(sql)

In this example, i enter the date 01/07/2022, but the date passed is 2022-07-01.I don’t understand where the error is, whether in the format or the dash bothers me.

Ok, the error generated is: [Microsoft][Driver ODBC Microsoft Access] Errore di sintassi nella clausola FROM

Ciao Federico,
the “user” date is a local format
There is no European date format (for example in Germany it is and so on)
In your db you have to save the SQLDate or SQLDateTime format (yyyy-mm-dd) that have some big advantages like it’s standard for every one, is directly sortable…
When you read a Date Field with this format you can get a DateTime object and show to the user the local standard format or whatever format you want.

This is a classic represantation problem, the real value (the value used by your program) can have a different format for the user, but this is only a view of your original value not the true value (take as example a double value that you write in your code as 3.2 and show to the user as 3,2)

Thanks Antonio, yes, what you are saying is very clear … not being a professional programmer, I was trying to understand why it worked for some entries and now it doesn’t work for me anymore, I think it is related to the date format, but it doesn’t i’m sure, however i’m investigating what the problem is in the sql expression. Thank you.

For sql use a prepared statement (now you need only the string) and let it be called with your data
Don’t try to create it directly

myRowSet=myDB.sqlSelect(“select * from myTable where fieldWithData<?”, myDateTimeObject)

Looking further into the sql statement, I think it may deal with how I retrieve the data for FROM…it has a name with spaces, I think this may be the problem…now I try to replace “FROM Input Invoice Table” with “FROM INPUTS”. If so, the date field had nothing to do with it, also because I have done many things with dates and up to now we have solved all the problems. As soon as I solve it, I’ll let you know. In the meantime, thanks as always for the support, I don’t feel alone :slight_smile:

Ok the problem in this part of my code was a space :slight_smile: I solved it.
But now, i have a similar problem:

Microsoft][Driver ODBC Microsoft Access]Valore di precisione non valido.
Error Number=98

SELECT * FROM IntestazioneFatturaAcquisti WHERE NumeroFattura=‘568’ and Ditta=‘Pingo Pallino’ and Data=#2022-12-31#

I tried to search for this error but I can’t find anything, can anyone help me?

What happens if you remove and Data=#2022-12-31# do you still get the error? Sorry I don’t have MS Access experience.

Perhaps you need:

... and Data='#2022-12-31#'
1 Like

Just use dates as in proper SQL standards.

and Data='2022-12-31'


I’m working for a company (in the Netherlands) that is using MS Access on a daily base.
When using sql statements in VBA we always convert date / datetime to mm-dd-yyyy format.

DateFormat = "#" & Format(dDate, "mm-dd-yyyy") & "#"
DateTimeFromat = "#" & Format(dtDateTime, "mm-dd-yyyy hh:mm:ss") & "#"

For us that is the most reliable method.

ok thanks

That’s not a standard SQL date format. As rick said:


is the correct format for SQL.

1 Like

You are absolutely correct.
But the thing is that MS Access doesn’t use standard SQL.
When you create a query in MS Access and look at the created SQL, the date is formatted as #mm-dd-yyyy# or #mm/dd/yyyy# depending on the local settings.

When the database is SQL Server (MS SQL Server Management Studio) the standard SQL date format ‘yyyy-mm-dd’ has to be used outside MS Access, but within MS Access format #mm-dd-yyyy# is still being used.

Through ODBC, all relational DBs to conform to minimal SQL standards. So all them uses it.

Anyway, the best practices for date literals in their native (MS Access Engine) flavor also is the international savvy #YYYY-MM-DD#. Programmers should avoid the problematic dubious #MM/DD/YYYY#

Access uses {d ‘2022-13-31’} or #12/31/2022# something like 12/31/2022 is calculated to (rounded to) 0 and results in 1899-12-30

Personally I convert all date/times to SecondsSince1970 which you get for free after parsing a date/time string and creating a new DateTime object. Thus one uses the data storage layer for storing data. Computation and comparison becomes easy too. Finally for presentation I’ve already asked the user what format they would like the date/time to be presented in (unfortunately Locale does not provide access to the system’s settings for this).

Thus all this discussion about format is avoided.

1 Like

You guys just need to read the manuals, again:

The ODBC layer translates SQL basic standards to native drivers necessities.
Just write the ISO ones as always. ‘yyyy-mm-dd’

But as I also see people writing locale specific confusing codes instead of the international ready ones for the VBA engine side of things, the universal ISO yyyy-mm-dd works too, just surrounded by “#”…

1 Like