Expression Help

[Ahrek]

What is wrong with this expression?

TSales: DSum("[sale]","44sales","[prodfil2_1].[pname1]='" & [prodfil2_1].[pname1] & "' and [ordhfile].[BLdate]>='" & [begdate] & "'")

I need to find the total sales for certain products in each state starting with a paticulat date. The expression works fine with out the "and..." statement used to include the date.

I hope someone can help.

 

[FoFa]

Since it is a date literial, enclose the date in #'s instead of sinigle quotes

 

[Ahrek]

The date is not literal. [begdate] is a prompted/entered date when the query is run.

 

[Rich]

Since you say the query is already filtered you don't need the criteria again

 

[Ahrek]

Ok, I found somethign that works, but not what I want...

TSales: DSum("[sale]","44sales","[prodfil2_1].[pname1]='" & [prodfil2_1].[pname1] & "' And [st]='" & [st] & "' and [bldate]>=#1/1/03#")

Gives me the numbers I am looking for, but I want to be able to run this report again next year without having to go into the expression to change the date. I tried it without the date first because I, too, thought it would not be needed since it was already filtered, but it is.

 

[FoFa]

Your date is a literial, to the DSum function (not to you).
Try:
TSales: DSum("[sale]","44sales","[prodfil2_1].[pname1]='" & [prodfil2_1].[pname1] & "' and [ordhfile].[BLdate]>=#" & [begdate] & "#")

 

[Ahrek]

When I read, what you said try, I thought it would work (it was the onbe thing I had not yet tried), but it still gave me "#ERROR" ;(

I hate having to leave the date a constant that must be changed.

 

[GumbyD]

Make sure that you designate in the query parameters that your input field is a date/time. Then try this:

TSales: DSum("[sale]","44sales","[prodfil2_1].[pname1]='" & [prodfil2_1].[pname1] & "' And [st]='" & [st] & "' and [bldate]>=#" & [begdate] & "#")

 

[Ahrek]

Well, all those exprs give me #ERROR. and now the expr that was working that I have listed above is also giving me #ERROR. Anyone have Bill Gates's address and a gun?

 

[Mile-O]

TSales: DSum("[sale]","44sales","[prodfil2_1].[pname1]='" & [prodfil2_1].[pname1] & "' And [st]='" & [st] & "' and [bldate]>=#1/1/03#")

What is [prodfil2_1] ? Is it another table? A form?

The criteria in a domain aggregate function such as DSum should involve field names in the recordset being used. In this case 44sales.