Dsum was working now it is not working

[ssalem]

Ok I am really confused. I have had this report set up for a couple months now and now a beginning total no longer works. I have not changed platforms or have done any updates. My reports retrieves inventory balances from a selected date range from a form. The parameters are pulled into a table called "Monthly Inventory". The first line on the report contains the beginning inventory from the table "Stamp Inventory" for each denomination of stamps. My control for the total field of beginning inventory is the following:



=DSum("Stamp_01","Stamp Inventory","[Used_Date]<[Forms]![Form Report]![txtStartDate]")

This code was working and now it is not. I do not get an error but I end up with a blank field. If I take off the parameter it will give me the total inventory from "Stamp Inventory". I have checked that both of my designs contain the same type of format. "Short Date". The only other solution I can think to do is to sum my beginning inventory fields in VBA defining them as public variables and populating the fields.

I am confused thought why this worked before and now all of a sudden does not work.


Perplexed.

[June7]

Don't see how that could ever have worked. Date criteria need # delimiters (text use apostrophe, numbers nothing). Concatenate variables. Reference to form control is a variable:

If you you need a start balance for each denomination then the DSum also needs criteria for the denomination, otherwise the sum will be for ALL denominations that meet the date criteria.

In Denomination group header section:

=DSum("Stamp_01","Stamp Inventory","[Used_Date]<#" & [Forms]![Form Report]![txtStartDate] & "# AND [Denomination='" & [Denomination] & "'")

If denomination is not text then remove the appostrophes.

[ssalem]

"Stamp 01" is the denomination. I have a unbound control box on the report for each denomination. Stamp_01 is the penny stamps. I have Stamp_05, Stamp_10...etc. My field on stamp inventory is a short date field and so is txtStartDate. I tried using the format you had and I get an error on the report.

[June7]

Why an UNBOUND box on report for denomination? How does denomination get on the report if box is not bound to field? Why save 'Stamp_' in the data? It's repetitive.

Denomination is correct name for the field?

What error shows on report?

One other edit I forgot. "Stamp Inventory" has a space so must enclose in []. "[Stamp Inventory]". This is why spaces in names is bad.

[ssalem]

I shouldn't say it is unbound...in my control source is where I am putting the dsum statement binding it to Stamp Inventory. If I make the criteria a different field it works. It is only when I try to reference the Used_date field it has problems. I tried to hardcode a date in and it still doesn't work. So it is not a problem with my form or the reference to the table. It is the date field I am referencing in the table that is the problem. But I don't see why. I actually cut and paste a similar reference of a between dates that I have in a Insert Into Statement referencing the same field and it works. I just changed the between to a less the txtStartdate from the form. But again even when I hard code a date it doesn't work.

My table reference works with =Dsum("Stamp_01","Stamp Inventory","[Transaction_type] = 'Refunds'").

I tried =Dsum("Stamp_01","[Stamp Inventory]","[Used_date] , # " & '02/01/2013' & "#") and this still did not work. I aslo tried to enclose the date like "02/01/2013" and [02/01/2013] and '2/1/2013' and [2/1/2013] and "2/1/2013". Nothing seems to work.

My Table has Used_date defined as Short Date.

My error on the report when ran just says #Error in the Control Box.

I picked up the same field names used on the Excel Spreadsheet they use to keep and were use to referring to.

[June7]

Need = sign instead of , beween [Used_Date] and #.

=DSum("Stamp_01","[Stamp Inventory]","[Used_date] = #02/01/2013#")

This is correct syntax for static criteria. If it doesn't work then will have to review your db. Follow instructions at bottom of my post.

You can always change field names. Show whatever you want on forms and reports in labels.

[ssalem]

ok I figured out what was wrong. In my submit button I have alot of VB code and in my clean up I am clearing out the txtStartDate and TxtEndDate's. Since the report is happening afterwards I shouldn't clear out the fields or save my TxtStartdate to a Public Variable that can be used on the report. I need to find out why I was clearing them out in the first place. But problem solved. I was looking in the wrong place. Thanks for all your help it still led me to start looking somewhere else.

[June7]

I don't think forms and reports can recognize VBA public variables or UDF constants directly (I have tried). Would have to call a UDF function from a textbox and the function would return the value of the variable or constant. Be wary of public variables. If code execution is interrupted the variables will lose value.