DATE FIELD

[Guest]

i have a query that consists of the following fields and corresponding
criteria:

1) date field, criteria= between [start date] and [end date]
2) currency field, which stands for the amount deducted from the cashier at
the date indicated in the date field.

the query allows me to view the amount deducted between the start date and
end date of my choice.
what i would like to achieve is to build a field that shows me the total
amount (sum) deducted before the start date of my choice. could i achieve
that in the same query and how? if not do i have to create a separate query
and how?

thank you in advance for your reply!

 

[Allen Browne]

Try typing something like this in to a fresh column in the Field row of your
query:
PriorAmount: DSum([Amount], "[SaleDate] < " & [StartDate])

Replace Amount with the name of your currency field, and SaleDate with the
name of your date field.

 

[Guest]

i have typed the following expression:

DSUM=([amount];"[date] <" & [start date]) and i get the result error in that
field!

Ο χÏήστης "Allen Browne" έγγÏαψε:

Try typing something like this in to a fresh column in the Field row of your
query:
PriorAmount: DSum([Amount], "[SaleDate] < " & [StartDate])

Replace Amount with the name of your currency field, and SaleDate with the
name of your date field.

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

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

i have a query that consists of the following fields and corresponding
criteria:

1) date field, criteria= between [start date] and [end date]
2) currency field, which stands for the amount deducted from the cashier
at
the date indicated in the date field.

the query allows me to view the amount deducted between the start date and
end date of my choice.
what i would like to achieve is to build a field that shows me the total
amount (sum) deducted before the start date of my choice. could i achieve
that in the same query and how? if not do i have to create a separate
query
and how?

thank you in advance for your reply!

 

[Allen Browne]

Sorry: the example as not clear at all.

Try typing this into the Field row in your query:
DSum("Amount", "Table1", "[date] < " & [start date])

Replace:
- Amount with the name of the field to sum;
- Table1 with the name of your table;
- Date with the name of your date field in that table.

If it still gives wrong results, try declaring the parameter.
Choose Parameters on the Query menu (in query design view).
In the dialog, enter:
[start date] Date/Time
That should help solve problems with regional dates.

Hopefully "date" is just an example and not the actual name of the field, as
Date is a reserved word in VBA.

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

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

i have typed the following expression:

DSUM=([amount];"[date] <" & [start date]) and i get the result error in
that
field!

? ??????? "Allen Browne" ???????:

Try typing something like this in to a fresh column in the Field row of
your
query:
PriorAmount: DSum([Amount], "[SaleDate] < " & [StartDate])

Replace Amount with the name of your currency field, and SaleDate with
the
name of your date field.

i have a query that consists of the following fields and corresponding
criteria:

1) date field, criteria= between [start date] and [end date]
2) currency field, which stands for the amount deducted from the
cashier
at
the date indicated in the date field.

the query allows me to view the amount deducted between the start date
and
end date of my choice.
what i would like to achieve is to build a field that shows me the
total
amount (sum) deducted before the start date of my choice. could i
achieve
that in the same query and how? if not do i have to create a separate
query
and how?

thank you in advance for your reply!

 

[John Spencer]

Allen,
Am I missing something? I would think that you would need to the date
delimiters in the expression.

DSum("Amount", "Table1", "[date] < #" & [start date] & "#")

Also, do we have to worry about the date format on Start Date (for those
that don't use the good ol' USA standard of mm/dd/yyyy)

DSum("Amount", "Table1", "[date] < #" & Format([start date], "yyyy/mm/dd") &
"#")

Sincerely,
John

Sorry: the example as not clear at all.

Try typing this into the Field row in your query:
DSum("Amount", "Table1", "[date] < " & [start date])

Replace:
- Amount with the name of the field to sum;
- Table1 with the name of your table;
- Date with the name of your date field in that table.

If it still gives wrong results, try declaring the parameter.
Choose Parameters on the Query menu (in query design view).
In the dialog, enter:
[start date] Date/Time
That should help solve problems with regional dates.

Hopefully "date" is just an example and not the actual name of the field,
as Date is a reserved word in VBA.

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

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

i have typed the following expression:

DSUM=([amount];"[date] <" & [start date]) and i get the result error in
that
field!

? ??????? "Allen Browne" ???????:

Try typing something like this in to a fresh column in the Field row of
your
query:
PriorAmount: DSum([Amount], "[SaleDate] < " & [StartDate])

Replace Amount with the name of your currency field, and SaleDate with
the
name of your date field.

i have a query that consists of the following fields and corresponding
criteria:

1) date field, criteria= between [start date] and [end date]
2) currency field, which stands for the amount deducted from the
cashier
at
the date indicated in the date field.

the query allows me to view the amount deducted between the start date
and
end date of my choice.
what i would like to achieve is to build a field that shows me the
total
amount (sum) deducted before the start date of my choice. could i
achieve
that in the same query and how? if not do i have to create a separate
query
and how?

thank you in advance for your reply!