Solved DSum quantities based on a text date box.

[Chief]

Hello,
I am trying to sum all of the sheets cut on a date which is picked on a form.

Form:

Query

SQL:
SELECT JobInfoT.JobID, JobInfoT.CarcassCut_Due, JobInfoT.CarcassCut_Date, JobInfoT.CC_Sheets
FROM JobTypesT INNER JOIN JobInfoT ON JobTypesT.JobTypeID = JobInfoT.JobTypeID
WHERE (((JobInfoT.CarcassCut_Date)=[Forms]![ManufSubF]![TxtDateFilter]));

And the Field where it should be adding them all up: (CC_Cut on the form)
=Nz(DSum("CC_Sheets","CC_SheetsCutQ","[CarcassCut_Date] = [TxtDateFilter]"))

Thanks

 

[arnelgp]

use can either use American date or ISO date plus the # delimiter:

=Nz(DSum("CC_Sheets","CC_SheetsCutQ","[CarcassCut_Date] = #" & Format$([TxtDateFilter], "mm/dd/yyyy") & "#"))

 

[Chief]

use can either use American date or ISO date plus the # delimiter:

=Nz(DSum("CC_Sheets","CC_SheetsCutQ","[CarcassCut_Date] = #" & Format$([TxtDateFilter], "mm/dd/yyyy") & "#"))

G'day mate,

I get a #Name? error

 

[theDBguy]

#Name? usually indicates a typo. Double check your spellings.

 

[Chief]

#Name? usually indicates a typo. Double check your spellings.

I missed an equals sign.
But now I am getting #Error and it is constantly flashing really fast

 

[Chief]

Hopefully someone can assist

Trying to update and load today

 

[theDBguy]

Hopefully someone can assist

Trying to update and load today

If you need immediate help, you might consider posting a sample copy of your db with test data.

 

[Chief]

If you need immediate help, you might consider posting a sample copy of your db with test data.

Ok,
Ill Try that, thanks.

 

[theDBguy]

Ok,
Ill Try that, thanks.

How do I duplicate the problem?

 

[arnelgp]

is this what you meant?

 

[Chief]

How do I duplicate the problem?

 

[Chief]

in the sample database the fields are blank.
in my database the field is error.

 

[Chief]

is this what you meant?

So I don't use a query?
go straight to the Table?

 

[arnelgp]

your query is based on the date textbox on the form.
the expression i made (on table) is also based on the date textbox.

 

[Chief]

is this what you meant?

your query is based on the date textbox on the form.
the expression i made (on table) is also based on the date textbox.

your query is based on the date textbox on the form.
the expression i made (on table) is also based on the date textbox.

Ok, cool.
Looks like it is working Thank you so much.

What's the best way to make this refresh?
Form event?
or an event on TxtDateFilter

Im Finding i select the date.
tab out (Not what i want to do)
but have to click back in date field to make numbers work.

 

[arnelgp]

if you change the txtDateFilter, the two textbox will compute automatically
so no need to refresh the two textbox or the form.

 

[Chief]

if you change the txtDateFilter, the two textbox will compute automatically
so no need to refresh the two textbox or the form.

Unfortunately that's not working for me.
i have to click out of the date field and alot of the time have to click in the calculated fields for the numbers to appear.
The calculated fields I will be making not enabled/locked.
Users wont understand or get the wrong figures when changing date.

 

[arnelgp]

after entering new date, press Enter key and the textbox will refresh.

 

[Chief]

after entering new date, press Enter key and the textbox will refresh.

nope.
i have to click into calculated fields to get numbers

 

[arnelgp]

you can try adding a Button to Requery those 2 total textbox.