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

You can replace the weird code with something much simpler and cleaner:

Private Sub Form_Load()

    ' Assign today's date to the combo.

    ' Only makes sense, if the source for the combobox includes this date.

    Me!cboDate.Value = Date

    ' Store the record.

    Call LoadData

End Sub

Public Function LoadData()

    Dim Sql As String

    Sql = "Insert Into Food_Distribution (fd_date, first_name, last_name, gender) " & _ 

        "Select Date(), first_name, last_name, gender From tblFD;)"

    CurrentDb.Execute Sql

End Function

Answer 3

Hi there

Thank you for your help. Your function is working but when ever I open form it is add another set of the same records for that day. I need solution to append records to Food_Distribution only  once per day. Why: We take tablet to the kitchen for the breakfast and after breakfast we close form Food Distribution. For the lunch if we open again I want that records to continue to record for the lunch. Three meals have check box which we check for every ID. So lets summarize, when I open form I do not want the same records to be appended to the table. Only when next day we open form than for that day records should be saved to table. Thank yoy

Answer 4

You are right. Missed that detail.

This should take of that:

Public Function LoadData()

    Dim Sql As String

    If IsNull(DLookup("fd_date", "Food_Distribution", "fd_date = Date())) Then

        Sql = "Insert Into Food_Distribution (fd_date, first_name, last_name, gender) " & _ 

            "Select Date(), first_name, last_name, gender From tblFD;)"

        CurrentDb.Execute Sql

    End If

End Function

Answer 5

Hi

I wrote your code and getting error in the line:

 If IsNull(DLookup("fd_date", "Food_Distribution", "fd_date = Date())) Then

When I write it like this the everything is ok:

 If IsNull(DLookup("fd_date", "Food_Distribution", FD_Date = Date)) Then

Could you please help maybe I am missing something.

Thanks