Formula for Dsum with Between...And statement is giving me an erro

[J. Keggerlord]

=Hour(Nz(DSum("[FTEnd]-[FTStart]","tblMain","[BEMS]=[PilotAssign] AND
[FTStart] Between Now() And Now()+7"),0))

The formula works perfect if I substitute the Now()+7 portion of the
equation with a dummy date (e.g. - 09/30/2007). Is there a different way I
should be expressing that I want criteria between now and one week from
today? Any help would be appreciated!

 

[Ken Snell \(MVP\)]

Try this:

=Hour(Nz(DSum("[FTEnd]-[FTStart]","tblMain","[BEMS]=[PilotAssign] AND
[FTStart] Between " & Format(Now(),"\#mm\/dd\/yyyy\#") & " And " &
Format(Now()+7,"\#mm\/dd\/yyyy\#"),0))

You need to get the actual date values and then concatenate them into the
string (with # delimiter for the date values). DSum cannot evaluate the Now
function.

 

[J. Keggerlord]

This would have worked, too. What I ended up doing instead was taking
advantage of the fact that I needed "up to" date range expressed with a text
box with a value of =now+7. I then modified the code using two AND
statements in lieu of the Between... and statement. Looks like:

=Hour(Nz(DSum("[FTEnd]-[FTStart]","tblMain","[BEMS]=[PilotAssign] AND
[FTStart]>now() AND [FTStart]<[txtPlusSeven]"),0))

However, that was some good information you provided about the Dsum
function. Thank you, again!!!

Try this:

=Hour(Nz(DSum("[FTEnd]-[FTStart]","tblMain","[BEMS]=[PilotAssign] AND
[FTStart] Between " & Format(Now(),"\#mm\/dd\/yyyy\#") & " And " &
Format(Now()+7,"\#mm\/dd\/yyyy\#"),0))

You need to get the actual date values and then concatenate them into the
string (with # delimiter for the date values). DSum cannot evaluate the Now
function.

--

Ken Snell
<MS ACCESS MVP>

=Hour(Nz(DSum("[FTEnd]-[FTStart]","tblMain","[BEMS]=[PilotAssign] AND
[FTStart] Between Now() And Now()+7"),0))

The formula works perfect if I substitute the Now()+7 portion of the
equation with a dummy date (e.g. - 09/30/2007). Is there a different way
I
should be expressing that I want criteria between now and one week from
today? Any help would be appreciated!