Solved Problem with DCOUNT and dates (1 Viewer)

frankt68

Registered User.
Local time
Today, 19:52
Joined
Mar 14, 2012
Messages
67
Hello!

I have a problem using DCOUNT to prevent duplicate entries by date.
I have a table named tAverage(with the ItemName, Quantity, AverageQuantity and Date_average fields) into which I add new records using the qAverage_Add query. This query also calculates the average quantity. I run this query using the fAverage form. I have a textbox txtDate_Average on the form to select a date, which is then written to the tAverage table, in the Date_average box, for all the records added (So I know when the average was calculated).
I would like to prevent duplicate records from being added for the same date.
I tried to use DCOUNT on form fAverage in txtDateAverage's after update event using this code

If DCount("[Date_average]", "tAverage", "[Date_average]= #" & Me.txtDate_average & "#") > 0 Then...


but I get a Run-time error 3075 syntax error in date in query expression "[Date_average]=#31.3.2020"


Obviously, it is something wrong with this piece of code
"[Date_average]= #" & Me.txtDate_average
but Idon't know what.

Any idea?
 

theDBguy

I’m here to help
Local time
Today, 10:52
Joined
Oct 29, 2018
Messages
10,208
Hi. Maybe try.

"Date_average=#" & Format(CDate(Me.txtDate_average),"yyyy-mm-dd") & "#")
 

frankt68

Registered User.
Local time
Today, 19:52
Joined
Mar 14, 2012
Messages
67
Thanx theDBguy.

That works....
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom