DSUM proplem

  • Thread starter Thread starter Gale
  • Start date Start date
G

Gale

Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well as
many variations) but the results are either close or I get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND [COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but say
[PRODUCT] = 'HEATING OIL', for example I can get a result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!
 
Assuming that your query is based on this table, don't use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved word in ACCESS, and
ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
 
-----Original Message-----
Assuming that your query is based on this table, don't use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved word in ACCESS, and
ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--
Ken Snell
<MS ACCESS MVP>

Gale said:
Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well as
many variations) but the results are either close or I get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND [COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but say
[PRODUCT] = 'HEATING OIL', for example I can get a result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!
Click to expand...


.
Ken,
Click to expand...

Thanks, but a totals query will not give me the results I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.
 
OK - the problem is that you're not using ' as a text string delimiter
around the Product field's value.

Ty this:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] ='" & [PRODUCT] & "' AND [COUNTER]
<=" & [COUNTER])


You'll note that I've inserted ' characters on either side of the [PRODUCT]
concatenation.
--
Ken Snell
<MS ACCESS MVP>


Gale said:
-----Original Message-----
Assuming that your query is based on this table, don't use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved word in ACCESS, and
ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--
Ken Snell
<MS ACCESS MVP>

Gale said:
Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well as
many variations) but the results are either close or I get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND [COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but say
[PRODUCT] = 'HEATING OIL', for example I can get a result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!
Click to expand...


.
Ken,
Click to expand...

Thanks, but a totals query will not give me the results I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.
Click to expand...
 
-----Original Message-----
OK - the problem is that you're not using ' as a text string delimiter
around the Product field's value.

Ty this:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] ='" & [PRODUCT] & "' AND [COUNTER]
<=" & [COUNTER])


You'll note that I've inserted ' characters on either side of the [PRODUCT]
concatenation.
--
Ken Snell
<MS ACCESS MVP>


-----Original Message-----
Assuming that your query is based on this table, don't use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved word in ACCESS, and
ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--
Ken Snell
<MS ACCESS MVP>

Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well as
many variations) but the results are either close or
Click to expand...
I
get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND [COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but say
[PRODUCT] = 'HEATING OIL', for example I can get a result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!





.
Ken,
Click to expand...

Thanks, but a totals query will not give me the results I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.
Click to expand...


.
Ken,
Click to expand...

Thanks for trying again, but unfortunately that didn't
work either. I even tried taking out the date and counter
from the criteria and doing the Product part by itself.
For some reason, DSUM doesn't like to compare strings
unless you actually type the word out for the field to
compare.
 
Post the entire SQL of the query that you're trying to run. DSum will work
with the syntax that I provided, so there must be something else in the
query that is causing a problem.


--
Ken Snell
<MS ACCESS MVP>

Gale said:
-----Original Message-----
OK - the problem is that you're not using ' as a text string delimiter
around the Product field's value.

Ty this:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] ='" & [PRODUCT] & "' AND [COUNTER]
<=" & [COUNTER])


You'll note that I've inserted ' characters on either side of the [PRODUCT]
concatenation.
--
Ken Snell
<MS ACCESS MVP>


-----Original Message-----
Assuming that your query is based on this table, don't
use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved
word in ACCESS, and
ACCESS can become quite confused. See KB article number
286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-
us;286335


--
Ken Snell
<MS ACCESS MVP>

Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well as
many variations) but the results are either close or I
get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND
[COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but say
[PRODUCT] = 'HEATING OIL', for example I can get a
result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!





.
Ken,

Thanks, but a totals query will not give me the results I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.
Click to expand...


.
Ken,
Click to expand...

Thanks for trying again, but unfortunately that didn't
work either. I even tried taking out the date and counter
from the criteria and doing the Product part by itself.
For some reason, DSUM doesn't like to compare strings
unless you actually type the word out for the field to
compare.
Click to expand...
 
-----Original Message-----
Post the entire SQL of the query that you're trying to run. DSum will work
with the syntax that I provided, so there must be something else in the
query that is causing a problem.


--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
OK - the problem is that you're not using ' as a text string delimiter
around the Product field's value.

Ty this:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] ='" & [PRODUCT] & "' AND [COUNTER]
<=" & [COUNTER])


You'll note that I've inserted ' characters on either side of the [PRODUCT]
concatenation.
--
Ken Snell
<MS ACCESS MVP>



-----Original Message-----
Assuming that your query is based on this table, don't
use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved
word in ACCESS, and
ACCESS can become quite confused. See KB article number
286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx? scid=kb;en-
us;286335


--
Ken Snell
<MS ACCESS MVP>

Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as
Click to expand...
well
as
many variations) but the results are either close
Click to expand...
or
I
get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND
[COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself
Click to expand...
but
say
[PRODUCT] = 'HEATING OIL', for example I can get a
result,
but I need it for all products.

Any help would be appreciated, especially if it doesn't
involve using code.

Thanks!





.
Ken,

Thanks, but a totals query will not give me the
Click to expand...
results
I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.




.
Ken,
Click to expand...

Thanks for trying again, but unfortunately that didn't
work either. I even tried taking out the date and counter
from the criteria and doing the Product part by itself.
For some reason, DSUM doesn't like to compare strings
unless you actually type the word out for the field to
compare.
Click to expand...


.
Ken,
Click to expand...

Ok here it is.

SELECT [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER,
[BULK PURCHASES - CONVERTED FOR FIFO].SORT, [BULK
PURCHASES - CONVERTED FOR FIFO].PRODUCT, [BULK PURCHASES -
CONVERTED FOR FIFO].[MOVEMENT DATE], [BULK PURCHASES -
CONVERTED FOR FIFO].VOLUME, DSum("[VOLUME]","[BULK
PURCHASES - CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" &
[MOVEMENT DATE] & "# AND [PRODUCT] = '" & [PRODUCT]
& "'AND [COUNTER]<=" & [COUNTER]) AS TotalGallons
FROM [BULK PURCHASES - CONVERTED FOR FIFO]
ORDER BY [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER;

Thanks
 
-----Original Message-----
-----Original Message-----
Post the entire SQL of the query that you're trying to run. DSum will work
with the syntax that I provided, so there must be something else in the
query that is causing a problem.


--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
OK - the problem is that you're not using ' as a text
string delimiter
around the Product field's value.

Ty this:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] ='" & [PRODUCT] & "' AND
[COUNTER]
<=" & [COUNTER])


You'll note that I've inserted ' characters on either
side of the [PRODUCT]
concatenation.
--
Ken Snell
<MS ACCESS MVP>


message

-----Original Message-----
Assuming that your query is based on this table, don't
use DSum at all.
Instead, use a Totals query:

SELECT Product, [Date], Sum(Gallons) AS TotalGallons
FROM Tablename
GROUP BY Product, [Date];

Also, don't use Date as a field name. It's a reserved
word in ACCESS, and
ACCESS can become quite confused. See KB article number
286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx? scid=kb;en-
us;286335


--
Ken Snell
<MS ACCESS MVP>

message
Hello,

Here is an example of my data:

COUNTER PRODUCT DATE GALLONS
1 HEATING OIL 5/3/04 10,000
2 HEATING OIL 5/5/04 20,000
3 HEATING OIL 5/6/04 5,000
4 GAS 5/3/04 7,000
5 GAS 5/3/04 8,000
6 KEROSCENE 5/6/04 3,000

I am trying to get a running total for the gallons,
by product by date. My new column should be:

TotalGallons
10,000
30,000
35,000
7,000
15,000
3,000

I have tried using the following expression (as well
as
many variations) but the results are either
Click to expand...
Click to expand...
close
or
I
get
a message saying there is a missing operator:

TotalGallons: DSum("[VOLUME]","[BULK PURCHASES -
CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" & [MOVEMENT
DATE] & "# AND [PRODUCT] =" & [PRODUCT] & " AND
[COUNTER]
<=" & [COUNTER])

My problem seems to be with the Product field in the
expression. If I take it out or use it by itself but
say
[PRODUCT] = 'HEATING OIL', for example I can get a
result,
but I need it for all products.

Any help would be appreciated, especially if it
doesn't
involve using code.

Thanks!





.
Ken,

Thanks, but a totals query will not give me the results
I
am looking for. I need a running balance on each line of
the query, not a single line showing the grand totals by
product. Also, I am not using the field name DATE. I
only
used that as an example of my data.
If you or anyone else has any other ideas, I would
appreciate it.




.
Ken,

Thanks for trying again, but unfortunately that didn't
work either. I even tried taking out the date and counter
from the criteria and doing the Product part by itself.
For some reason, DSUM doesn't like to compare strings
unless you actually type the word out for the field to
compare.
Click to expand...


.
Ken,
Click to expand...

Ok here it is.

SELECT [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER,
[BULK PURCHASES - CONVERTED FOR FIFO].SORT, [BULK
PURCHASES - CONVERTED FOR FIFO].PRODUCT, [BULK PURCHASES -
Click to expand...
CONVERTED FOR FIFO].[MOVEMENT DATE], [BULK PURCHASES -
CONVERTED FOR FIFO].VOLUME, DSum("[VOLUME]","[BULK
PURCHASES - CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" &
[MOVEMENT DATE] & "# AND [PRODUCT] = '" & [PRODUCT]
& "'AND [COUNTER]<=" & [COUNTER]) AS TotalGallons
FROM [BULK PURCHASES - CONVERTED FOR FIFO]
ORDER BY [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER;

Thanks

.
Ken,
Click to expand...

I found a solution. I setup the DSUM like you said but
then I changed the movement date criteria. Instead of
saying movement date = movement date, I said movement date
= #1-1-04# and then I got the correct results. It doesn't
Click to expand...
make much sense but I guess if you have more than 1
criteria, DSUM doesn't want each one compared to itself.
Thanks for all your time and help!
 
The issue is not that DSum doesn't like having a criteria "=" to something.
It more likely has to do with the data you have in the table and that they
don't match the criterion that you were trying to use. Note that, if you're
storing a date and a time in the MOVEMENT DATE field, then a simple "="
criterion likely won't find just a date -- because there's also a time
factor with the date in the value. Dates are stored as double precision
numbers, where the integer portion is the number of days since midnight,
December 30, 1899, and the fraction part is the fraction of a 24-hour day
represented by the time.

Thus, if, for example, your field contains the value for January 1, 2004
8:00:00 AM (which is 37987.3333333333), it will never match just the date of
January 1, 2004 (which is 37987).


--
Ken Snell
<MS ACCESS MVP>


Ok here it is.

SELECT [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER,
[BULK PURCHASES - CONVERTED FOR FIFO].SORT, [BULK
PURCHASES - CONVERTED FOR FIFO].PRODUCT, [BULK PURCHASES -
Click to expand...
CONVERTED FOR FIFO].[MOVEMENT DATE], [BULK PURCHASES -
CONVERTED FOR FIFO].VOLUME, DSum("[VOLUME]","[BULK
PURCHASES - CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" &
[MOVEMENT DATE] & "# AND [PRODUCT] = '" & [PRODUCT]
& "'AND [COUNTER]<=" & [COUNTER]) AS TotalGallons
FROM [BULK PURCHASES - CONVERTED FOR FIFO]
ORDER BY [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER;

Thanks

.
Ken,
Click to expand...

I found a solution. I setup the DSUM like you said but
then I changed the movement date criteria. Instead of
saying movement date = movement date, I said movement date
= #1-1-04# and then I got the correct results. It doesn't
Click to expand...
make much sense but I guess if you have more than 1
criteria, DSUM doesn't want each one compared to itself.
Thanks for all your time and help!
Click to expand...
 
-----Original Message-----
The issue is not that DSum doesn't like having a criteria "=" to something.
It more likely has to do with the data you have in the table and that they
don't match the criterion that you were trying to use. Note that, if you're
storing a date and a time in the MOVEMENT DATE field, then a simple "="
criterion likely won't find just a date -- because there's also a time
factor with the date in the value. Dates are stored as double precision
numbers, where the integer portion is the number of days since midnight,
December 30, 1899, and the fraction part is the fraction of a 24-hour day
represented by the time.

Thus, if, for example, your field contains the value for January 1, 2004
8:00:00 AM (which is 37987.3333333333), it will never match just the date of
January 1, 2004 (which is 37987).


--
Ken Snell
<MS ACCESS MVP>

Ok here it is.

SELECT [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER,
[BULK PURCHASES - CONVERTED FOR FIFO].SORT, [BULK
PURCHASES - CONVERTED FOR FIFO].PRODUCT, [BULK
Click to expand...
PURCHASES -
CONVERTED FOR FIFO].[MOVEMENT DATE], [BULK PURCHASES -
CONVERTED FOR FIFO].VOLUME, DSum("[VOLUME]","[BULK
PURCHASES - CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" &
[MOVEMENT DATE] & "# AND [PRODUCT] = '" & [PRODUCT]
& "'AND [COUNTER]<=" & [COUNTER]) AS TotalGallons
FROM [BULK PURCHASES - CONVERTED FOR FIFO]
ORDER BY [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER;

Thanks

.
Ken,
Click to expand...

I found a solution. I setup the DSUM like you said but
then I changed the movement date criteria. Instead of
saying movement date = movement date, I said movement date
= #1-1-04# and then I got the correct results. It
Click to expand...
doesn't
make much sense but I guess if you have more than 1
criteria, DSUM doesn't want each one compared to itself.
Thanks for all your time and help!
Click to expand...


.
Ken,
Click to expand...

The movement date field does not have a time associated
with it. I also did a query for the field to show the time
and the time for each record was 12:00:00 AM, so it
doesn't appear that's the problem.
 
By chance, is your Local Setting for dates not the US standard (mm/dd/yyyy)?
If not, then you need to convert the date values into the US format when you
use them in a query -- Jet database engine expects dates to have the format.

You can cast the date in the correct format using the Format function
wrapped around your date value (snippet from your SQL shown here):

"[MOVEMENT DATE] =#" & Format([MOVEMENT DATE], "mm/dd/yyyy") & "#
--
Ken Snell
<MS ACCESS MVP>

Gale said:
-----Original Message-----
The issue is not that DSum doesn't like having a criteria "=" to something.
It more likely has to do with the data you have in the table and that they
don't match the criterion that you were trying to use. Note that, if you're
storing a date and a time in the MOVEMENT DATE field, then a simple "="
criterion likely won't find just a date -- because there's also a time
factor with the date in the value. Dates are stored as double precision
numbers, where the integer portion is the number of days since midnight,
December 30, 1899, and the fraction part is the fraction of a 24-hour day
represented by the time.

Thus, if, for example, your field contains the value for January 1, 2004
8:00:00 AM (which is 37987.3333333333), it will never match just the date of
January 1, 2004 (which is 37987).


--
Ken Snell
<MS ACCESS MVP>


Ok here it is.

SELECT [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER,
[BULK PURCHASES - CONVERTED FOR FIFO].SORT, [BULK
PURCHASES - CONVERTED FOR FIFO].PRODUCT, [BULK PURCHASES -

CONVERTED FOR FIFO].[MOVEMENT DATE], [BULK PURCHASES -
CONVERTED FOR FIFO].VOLUME, DSum("[VOLUME]","[BULK
PURCHASES - CONVERTED FOR FIFO]","[MOVEMENT DATE] =#" &
[MOVEMENT DATE] & "# AND [PRODUCT] = '" & [PRODUCT]
& "'AND [COUNTER]<=" & [COUNTER]) AS TotalGallons
FROM [BULK PURCHASES - CONVERTED FOR FIFO]
ORDER BY [BULK PURCHASES - CONVERTED FOR FIFO].COUNTER;

Thanks

.
Ken,

I found a solution. I setup the DSUM like you said but
then I changed the movement date criteria. Instead of
saying movement date = movement date, I said movement date
= #1-1-04# and then I got the correct results. It doesn't
make much sense but I guess if you have more than 1
criteria, DSUM doesn't want each one compared to itself.
Thanks for all your time and help!
Click to expand...


.
Ken,
Click to expand...

The movement date field does not have a time associated
with it. I also did a query for the field to show the time
and the time for each record was 12:00:00 AM, so it
doesn't appear that's the problem.
Click to expand...
 
Back
Top