troubleshooting Question

MS Access Database Problems

Avatar of Justin
Justin asked on
Microsoft OfficeMicrosoft AccessMicrosoft SQL Server
12 Comments1 Solution303 ViewsLast Modified:
Hello Experts,

I've spent 2 days building my first Access Database, learning from trial and error, and by attending the University of Google. I've managed to just about reach all of my goals, but I've stumbled right at the end, and need some assistance.

I've built a currency exchange access database. Basically, a customer will present at or shop asking for a currency exchange, the staff member will take their currency, plug the numbers into my access database, and it will calculate the correct amount of currency to exchange. It will keep a record of all these transactions, and eventually print a receipt for the customer etc. We only have 4 currencies we deal with, and need it to be super simple.

My first major problem is my queries. If I have at least 1 of each transactions type recorded (AUD to USD, USD to AUD, AUD to EUR, EUR to AUD, AUD to CNY, CNY to AUD) for the reporting period, my queries run perfect. But, if I am missing 1 or more of any transaction type, the queries fails to run.
I've worked out that this is because of the null values running through my queries (because there are no records for the query to query).
What I can't work out, is how to make a "0" (zero) run through the query, so the result is a zero. I know a zero will work, because if I generate records with zeros in them, it works perfect.
I've tried using the NZ function, and IIF statements, but I think because of where and how I do my calculations, it won’t work.

I've made the database available so you can work out what I’m talking about. Keep in mind that this is my first database, so there are probably things in there that could be done differently, and I’m open to suggestions.

http://www.phseafarers.org/uploads/5/0/9/4/5094800/currencyexchange_-_copy.zip

Thanks for your help.
ASKER CERTIFIED SOLUTION
peter57r

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros