between date criteria not working

[wassimdaccache]

Dear All

I am using this function to make the sum of values in my query

nz(DSum("Total","fuel_move","id_fuel_move='"  & barcode_product & "'" & "and date_fuel_move between" & [date1] And [date2]  ),0) AS sofuel

I am not getting the right values!!!

When I am using this part of the function

"and date_fuel_move between" & [date1] And [date2]

the first part of the criteria will be eliminated and printing me just one value.

How may I fix that ?

Any hint would be appreciated.

REGARDS

[OldBirdman]

If barcode_product were xxxxxx and date1 were 3/1/09 and date2 were 3/15/09, then

nz(DSum("Total","fuel_move","id_fuel_move='"  & barcode_product & "'" & "and date_fuel_move between" & [date1] And [date2]  ),0) AS sofuel 

with substitutions would be

nz(DSum("Total","fuel_move","id_fuel_move='xxxxxx'and date_fuel_move between0),0) AS sofuel 

Without going any further into the problem, I see several syntax errors here. I'm using the ~ as a delimeter to avoid confusion with the single and double quotes that already appear in the statement.
1) There needs to be a blank between ~'xxxxxx'~ and ~"and date_..."~ so change to ~" and date_. . ."~
2) ~[date1] and [date2]~ will evaluate to False, or 0 unless both dates are the same, then it will evaluate to True. The center ~And~ needs to be ~ & " And " & ~
3) Date values need the delimeter "#" so they are correctly intrepreted as dates, and not arithemetic operations. ~[date1]~ should be ~" #" & [date1] & "#"~ ~[date2]~ needs similar changes.

[NeoPa]

Nice work OB. It's a shame the OP hasn't returned to thank you themselves but you get that sometimes.

A helpful link on this subject is Literal DateTimes and Their Delimiters (#).

[OldBirdman]

Thankx NeoPa. I read these posts to learn. Very selfish of me. By considering whether I want to reply, I make myself study the problems carefully. I therefore improve myself. That is my reward.

[NeoPa]

Yeah OB, you're all just self, self, self! :D

I'm glad to hear you're not likely to be put off from such threads though, and I know what you mean about learning. Working on this site has given me a much deeper understanding of various aspects of Access that I had never covered in my own work.