If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSum expression not working
I have a control on a form which displays a running total of tickets
issued year to date (since 1 Jan). The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon |
#2
|
|||
|
|||
DSum expression not working
On 5 feb, 14:35, " wrote:
I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). *The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but *am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. *My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon Because your dates are now variables, you can't use a fixed string as where-clause. Try: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN " & DateSerial(Year(Date()), 1, 1) & " AND " & Date) Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
DSum expression not working
On Fri, 5 Feb 2010 05:57:15 -0800 (PST), XPS350
wrote: What he said, but then also add #-signs to wrap the date values. -Tom. Microsoft Access MVP On 5 feb, 14:35, " wrote: I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). *The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but *am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. *My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon Because your dates are now variables, you can't use a fixed string as where-clause. Try: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN " & DateSerial(Year(Date()), 1, 1) & " AND " & Date) Groeten, Peter http://access.xps350.com |
#4
|
|||
|
|||
DSum expression not working
Gordon -
Let Access evaluate the date - don't include the date functions in quotes - and use the delimeters: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND #& Date & "#") -- Daryl S " wrote: I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon . |
#5
|
|||
|
|||
DSum expression not working
On 5 Feb, 14:18, Daryl S wrote:
Gordon - Let Access evaluate the date - don't include the date functions in quotes - and use the delimeters: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND #& Date & "#") -- Daryl S " wrote: I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). *The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but *am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. *My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon .- Hide quoted text - - Show quoted text - Thanks for the responses, guys. Whilst I understand what I was doing wrong, I still can't fix it. I think your expression, Daryl, may have been missing a delimiter after the AND #, so using this as the expression: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # “ & Date & "#") I still get an "expression has an invalid date value" error. Must be something simple, but for the life of me I can't see it. Help Gordon |
#6
|
|||
|
|||
DSum expression not working
Gordon -
You don't have any variables or controls called "Date" do you? You can try using Date() instead of Date. I also notice a “ (left double quote) in your statement that should be a regular double quote ". Try this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # " & Date() & "#") -- Daryl S " wrote: On 5 Feb, 14:18, Daryl S wrote: Gordon - Let Access evaluate the date - don't include the date functions in quotes - and use the delimeters: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND #& Date & "#") -- Daryl S " wrote: I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon .- Hide quoted text - - Show quoted text - Thanks for the responses, guys. Whilst I understand what I was doing wrong, I still can't fix it. I think your expression, Daryl, may have been missing a delimiter after the AND #, so using this as the expression: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # “ & Date & "#") I still get an "expression has an invalid date value" error. Must be something simple, but for the life of me I can't see it. Help Gordon . |
#7
|
|||
|
|||
DSum expression not working
On Feb 5, 7:10*pm, Daryl S wrote:
Gordon - You don't have any variables or controls called "Date" do you? *You can try using Date() instead of Date. *I also notice a “ (left double quote) in your statement that should be a regular double quote ". *Try this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # " & Date() & "#") -- Daryl S " wrote: On 5 Feb, 14:18, Daryl S wrote: Gordon - Let Access evaluate the date - don't include the date functions in quotes - and use the delimeters: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND #& Date & "#") -- Daryl S " wrote: I have a control on a form which displays a running total of tickets issued year to date (since 1 Jan). *The following expression entered as the control source for a textbox on the form works OK: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #1/1/10# AND #12/31/10#") but *am trying to design it so that the expression doesn't have to be changed every year, so when I use this: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date") I get an "#error" in the control. *My computer's date setting is for the UK if that is relevant. Thanks for any help. Gordon .- Hide quoted text - - Show quoted text - Thanks for the responses, guys. Whilst I understand what I was doing wrong, I still can't fix it. *I think your expression, Daryl, may have been missing a delimiter after the AND #, so using this as the expression: =DSum("[fldQuantityIssued]","[tblCompTicketIssues]","[fldIssueDate] BETWEEN #" & DateSerial(Year(Date()), 1, 1) & "# AND # “ & Date & "#") I still get an "expression has an invalid date value" error. *Must be something simple, but for the life of me I can't see it. Help Gordon .- Hide quoted text - - Show quoted text - Hi Daryl S, The brackets after Date did the trick - your revised code above now works a treat. Many thanks. Gordon |
Thread Tools | |
Display Modes | |
|
|