Problem with DSUM and alphanumeric compares

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a query with a running sum that reurns a null value for the
sum. I have narrowed the problem down to the alphanumberic part number
compare for the field "part". I have changed the table to include only
numeric values in the field "part" and changed the attribute from 'text' to
'integer' and the query works fine. Unfortunately, I have aphanumeric parts
in the full table. What is the problem with the comparison using text fields?


SELECT Table2.item, Table2.part, Table2.Change,
DSum("Change","Table2","[item] = '" & [Item] & " ' and part<= ' " & [part] &
" ' ") AS InvLvl
FROM Table2;
 
You need to watch the spaces when you insert your single speech marks
for text values:

SELECT Table2.item, Table2.part, Table2.Change,
DSum("Change","Table2","[item] = '" & [Item] & "' and part <= '" &
[part] & "'") AS InvLvl FROM Table2;

Untested.

Regards,
Andreas
 
whouser said:
I have created a query with a running sum that reurns a null value for the
sum. I have narrowed the problem down to the alphanumberic part number
compare for the field "part". I have changed the table to include only
numeric values in the field "part" and changed the attribute from 'text' to
'integer' and the query works fine. Unfortunately, I have aphanumeric parts
in the full table. What is the problem with the comparison using text fields?


SELECT Table2.item, Table2.part, Table2.Change,
DSum("Change","Table2","[item] = '" & [Item] & " ' and part<= ' " & [part] &
" ' ") AS InvLvl
FROM Table2;
Click to expand...


You have spaces inside the quoted part value:

DSum("Change","Table2","[item] = '" & [Item] &
"' and part<= '" & [part] & "' ")
 
Back
Top