DSum() Question

[Alan Fisher]

I am using the following line as a control source bu keep
getting an #Error in the text box.

=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [
DateStart] > " & "#" & 1/1/2004 & "#")

I used the same line but had "[Evergrren] = -1" in the
criteria instead of the date criteria and it worked fine.
The field name [DateStart] is valid.

 

[fredg]

I am using the following line as a control source bu keep
getting an #Error in the text box.

=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [
DateStart] > " & "#" & 1/1/2004 & "#")

I used the same line but had "[Evergrren] = -1" in the
criteria instead of the date criteria and it worked fine.
The field name [DateStart] is valid.

My guess is the name of this control is either "DateStart" or
"HeloHoursFlown".
A control cannot have the same name as the name of a field used in
it's control source expression.

I believe you can code the expression a bit cleaner like this:
=350-DSum("[HeloHoursFlown]","tblHeloMisnSched","[DateStart] >
#1/1/2004#")

If you were going to use a variable date instead of hard coding
"1/1/2004" you could use:
=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [DateStart] > #" &
VariableHere & "#")

 

[Alan Fisher]

Thanks that did the trick.

-----Original Message-----

I am using the following line as a control source bu keep
getting an #Error in the text box.

=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [
DateStart] > " & "#" & 1/1/2004 & "#")

I used the same line but had "[Evergrren] = -1" in the
criteria instead of the date criteria and it worked fine.
The field name [DateStart] is valid.

My guess is the name of this control is either "DateStart" or
"HeloHoursFlown".
A control cannot have the same name as the name of a field used in
it's control source expression.

I believe you can code the expression a bit cleaner like this:
=350-DSum

("[HeloHoursFlown]","tblHeloMisnSched","[DateStart] >

#1/1/2004#")

If you were going to use a variable date instead of hard coding
"1/1/2004" you could use:
=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [DateStart] > #" &
VariableHere & "#")

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

 

[Marshall Barton]

I am using the following line as a control source bu keep
getting an #Error in the text box.

=350-DSum("[HeloHoursFlown]","tblHeloMisnSched"," [
DateStart] > " & "#" & 1/1/2004 & "#")

I used the same line but had "[Evergrren] = -1" in the
criteria instead of the date criteria and it worked fine.
The field name [DateStart] is valid.

1/1/2004 is an arithmetic expression that evaluates to
..000499, and #.000499# is not a valid date. When you use a
literal date, you need the # and the date inside the
criteria:

. . ., "[DateStart] > #1/1/2004#")

You do need to use concatenation when you're building the
criteria string using a variable:

. . ., "[DateStart] > #" & datevariable & "#")