DSum for queries (1 Viewer)

[Lasidu Chamikara]

Hi there,
In my database, there's a query named "ChargeQuery" which i use to find the relevant journey charge "Cost" when i enter the journey starting and ending location. There i used DLookUp function to find the value of "Cost". In a separate query named "TransactionQuery", I want to sum up the total journey cost infront of each "CardID" into a new field called "Debits". There I used DSum function as mentioned below, but it returns #Error.

Debits: DSum("Cost","ChargeQuery","CardID=" & [CardID])
There is a CardID field in both queries.

Please help me to fix this error.. This is urgent.
Thanks!

 

[theDBguy]

Is CardID a number field? In case it helps, can you please post the full SQL statement of your query? Thanks.

 

[Lasidu Chamikara]

Yes CardID is a number feild

SELECT CardInfo.CardID, PassengerInfo.PassengerName, CardInfpeningCardBalance, DSum("TopUpAmount","TopUpInfo","CardID=" & [CardID]) AS TopUpSum, [OpeningCardBalance]+[TopUpSum] AS Credits, DSum("Cost","ChargeQuery","CardID=" & [CardID]) AS Debits
FROM PassengerInfo INNER JOIN CardInfo ON PassengerInfo.[PassengerNIC] = CardInfo.[PassengerNIC];

 

[Lasidu Chamikara]

Is CardID a number field? In case it helps, can you please post the full SQL statement of your query? Thanks.

Is CardID a number field? In case it helps, can you please post the full SQL statement of your query? Thanks.

Thanks for reminding, I just crossed checked again, could figure out one field of CardID was not set as number. Now it works...
Thanks again!!!

 

[namliam]

You dont use DSum or any D thing in a query or in any database in general.
They are query's in them selves, but a slow and bad habit limitted version of a query. Stop using them ASAP!

Try something along these lines.

SELECT CardInfo.CardID
     , PassengerInfo.PassengerName
     , CardInfo_OpeningCardBalance
     , Sum(Cost) AS Debits
FROM PassengerInfo 
INNER JOIN CardInfo ON PassengerInfo.[PassengerNIC] = CardInfo.[PassengerNIC];
LEFT  JOIN ChargeQuery on CardInfo.CardID = ChargeQuery.CardID
Group by CardInfo.CardID
     , PassengerInfo.PassengerName
     , CardInfo_OpeningCardBalance

Also using a nice naming convention to seperate your objects, try using prefixes like
tbl
qry
frm
for Tables, queries and forms, similar for functions subs etc... You will eventually find life much easier.