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

Yes it is a field date I mean column in table Food_Distribution. Unbound combo box where today's date set is called cboDate.

Answer 3

As for the original code, we have no idea what the purpose of the combobox may be. It doesn't interact with the code you posted.

As for the a date value, this has no format. A format is applied for display only.

Answer 4

Combo box is set to receive present date when I open the form which has a subform where is loaded ID_Number, First_Name, Last_Name and Gender from tblFD. After all data has been loaded into subform which is bounded to table Food_Distribution where I have three check box for each meal Breakfast, Lunch and Dinner. When every person takes his meal I set checkbox to true and data are saved to table Food_Distribution.

On combo box cboDate in the Row source is set:

SELECT Food_Distribution.FD_Date, FORMAT(FD_DATE,"DDMMYYYY") FROM Food_Distribution ORDER BY 2  UNION SELECT DATE() AS FD_DATE, FORMAT(DATE(),"DDMMYYYY") FROM Date_Index ORDER BY 2;

There is Data_Index table where is only one field with value 1 what you can see in the code.