DLookup With Date In Form

[JPed]

At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.

However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.

I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.

This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:

=DLookUp("[Total Input]","DailyDataQry","[Work Date] = #" & [Forms]![InputFrm]![WorkDateAtxt] & "#")

The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.

Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?

 

[Mihail]

USA format should work with no problem:
http://en.wikipedia.org/wiki/Date_format_by_country

 

[JPed]

USA format should work with no problem:
http://en.wikipedia.org/wiki/Date_format_by_country

Both dates are in the 'Medium' format "06-Mar-14" at the moment

I think it is something to do with how the date is stored though, as I have another text box on my form which displays the average increase in weight through the production process for that material, and this works perfectly.

=DLookUp("[Avg Of Pick Up]","A Pickup Averages","[Mix Type] = '" & [Forms]![InputFrm]![MixTypeACombo] & "'")

 

[Mihail]

Read this:
Discussion: SQL Date Literals and Regional Settings - Microsoft Access / VBA