Syntax error in date in query expression #My_date=01.07.2020.#

Question

Hello

I have vba code on the unbound combo box in access but when I load the form I get syntax error in date. My date format is dd.mm.yyyy.

Please see attached screenshot. Could someone help with this error. Thank you.

Answer 1

Please notice, that a combobox can only hold text items, thus the first column will be FD_Date (or Date()) casted to text as to your regional settings of Windows.

Also, I guess you wish to order by the date value, not the day. Then you would need a column like

Format(Date(), "yyyymmdd")

to order by.

Answer 2

Date literals must be in US format: mm/dd/yyyy

Write yourself a new function ToSqlDate, which takes a date parameter and returns a US-formatted date string.

Then use it in your DCount expression.

Answer 3

Thank you very much on quick reply.

I am not a programmer and do not know how to write a new code. I hve found this code on the internet. Still learning access world. I would appreciate if you can help with this. I guess that regional settings should not be a problem but it is. Since I need date format for my region because of other applications.

Thank you.

Answer 4

Perhaps if you explain what it is you are trying to do we may be able to help. Much of what you have makes no sense (to me anyway). 

me.cbodate=now() makes no sense. You might set a default value for a combo on load, which would be an integer, but not now()

if dcount("1" .... 1 is a terrible name for a field.

If your date is in fact a string then the syntax would be more like:

If DCount("[1]", "Food_Distribution", "[FD_date] = '" & strdate & "'") > 0 Then

but it would be strange to have a date in a table with a datatype of 'string'.

What is the datatype of FD_date in table Food_Distribution?

Answer 5

Hi 

I have unbound form where is unbound combo box for date where on open sets Now() date. On the form I have sub form which is bounded to the table Food_Distribution. When I open form every day then on the subform takes data from tblFD where is ID_No, name, surname and gender and append that date for each ID and save it to  Food_Distribution. tblFD table is a temporary table which is loaded with other beneficiaries every day. Problem was the date which I cannot save in FD table because Food distribution takes place every day and cannot bound date field to a table. FD_Date is a date format dd.mm.yyyy. "1" is coming from the Date_Index table. So every day I get archived all beneficiaries data with a date of Food distribution. Hope this helps to get better picture. 

Thank you any way for your help and explanation.