Using Between for dates in NZ

T

Tom Brown

I am trying to use the following code in the Control Source property of a
text box to calculate the sum of entries made between specific dates. The
dates are fed from a user input form to an update query which puts the two
dates in a table that is read by two text boxes in a report header. The text
boxes in the report header with the dates are txt_Start_Date and
txt_End_Date. I will be copying this formula to about a hundred other text
boxes on this report so the other text boxes will be hard coded for all
other factors in the calculation. I can't seem to get the syntax correct.
The part I can't get is [Date] Between #txt_Start_Date# and #txt_End_Date#
Please help!


=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207" &
""" and Activity_Code=""" & "BD" & """ and [Date] Between #txt_Start_Date#
and #txt_End_Date# "),0)

Thanks,

Tom Brown
 
T

Tom Brown

I figured it out. Here is the corrected code.

=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207" &
""" and Activity_Code=""" & "BD" & """ and [Date] Between txt_Start_Date and
txt_End_Date "),0)
 
A

Allen Browne

Tom, if you are in New Zealand, you use d/m/y dates. I strongly suggest you
format them in the Criteria argument, or you won't get consistent results.

Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tom Brown said:
I figured it out. Here is the corrected code.

=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207" &
""" and Activity_Code=""" & "BD" & """ and [Date] Between txt_Start_Date
and txt_End_Date "),0)


Tom Brown said:
I am trying to use the following code in the Control Source property of a
text box to calculate the sum of entries made between specific dates. The
dates are fed from a user input form to an update query which puts the two
dates in a table that is read by two text boxes in a report header. The
text boxes in the report header with the dates are txt_Start_Date and
txt_End_Date. I will be copying this formula to about a hundred other
text boxes on this report so the other text boxes will be hard coded for
all other factors in the calculation. I can't seem to get the syntax
correct. The part I can't get is [Date] Between #txt_Start_Date# and
#txt_End_Date# Please help!


=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207" &
""" and Activity_Code=""" & "BD" & """ and [Date] Between
#txt_Start_Date# and #txt_End_Date# "),0)
 
T

Tom Brown

Thanks for the tip Allen. I am working in the US but we will soon be
creating reports for our European offices so the date Criteria you mention
will be good to know. Thanks again.


Allen Browne said:
Tom, if you are in New Zealand, you use d/m/y dates. I strongly suggest
you format them in the Criteria argument, or you won't get consistent
results.

Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tom Brown said:
I figured it out. Here is the corrected code.

=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207" &
""" and Activity_Code=""" & "BD" & """ and [Date] Between txt_Start_Date
and txt_End_Date "),0)


Tom Brown said:
I am trying to use the following code in the Control Source property of a
text box to calculate the sum of entries made between specific dates.
The dates are fed from a user input form to an update query which puts
the two dates in a table that is read by two text boxes in a report
header. The text boxes in the report header with the dates are
txt_Start_Date and txt_End_Date. I will be copying this formula to about
a hundred other text boxes on this report so the other text boxes will be
hard coded for all other factors in the calculation. I can't seem to
get the syntax correct. The part I can't get is [Date] Between
#txt_Start_Date# and #txt_End_Date# Please help!


=nz(DSum("Activity_Count","tbl_Activity_Count","E_Number=""" & "E13207"
& """ and Activity_Code=""" & "BD" & """ and [Date] Between
#txt_Start_Date# and #txt_End_Date# "),0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top