Adding an expression as a criteria

[b_otto]

I have a query I'm working with that finds data that changes, calculates the numbers, then prints a result in another column. The problem is that if I don't have any changes, then I have a blank result. Normally this wouldn't be a problem, but I'm making reports available to other users and would like this populated.

First I will say that I am a beginner, by far, with access. This has all been a learning experience for me. But.. I'm getting there. So this may seem like an easy question for you guys.

This is an inventory database that I've built from a template that came with access. It's now highly modified and nearing completion. I'm pretty sure my theory is sound on what I'm trying to do, but I struggle with making expressions.

What seems simplest is to add an expression in the criteria field for the column that displays data after crunching numbers. Since this only has a display if something has changed, then I need an expression added if there is no data. Can I add something like an IsNull expression to display the results from another column in the same table? The data will end up being redundant, but I'm ok with that for now. Ideally, the report should probably have the expression, but the column is already here in this table. Might as well have it populated.

Please see the picture to see what I'm trying to do.

 

[Isskint]

Hi b_otto

Thank you for the detailed explanation and the useful image, it helps the community to help questions quicker.

One way to do this is to change the expression you use to determine currentinventory. I would use an IIF() function with the expression you are using to test for 0, blank or Null. EG IIF(isnull(yourexpression),original inventory,yourexpression)

 

[vbaInet]

A much simpler one would be the Nz() function.

 

[b_otto]

Thanks for the replies! I tried using a few expressions and couldn't get it to work. I was able to solve the problem by adding a couple more columns with calculations on it. This works for now but I want to streamline everything eventually. For now I'm just under pressure to get it done, so it will have to work for now. Lol. Thanks again, guys!

 

[vbaInet]

You could have come back to us if it didn't work and we would have advised. This is a very trivial case.

 

[b_otto]

Thanks vba. I definitely will be. My expectations for the database are pretty high. I have a lot to learn to get it there. It's going to be going live soon, but there will be massive updates in the future.