Help with my VBA SQL statement?

[blodzoom]

I'm not even sure that you can structure a select statement this way but I'm trying to subtract the items that have exited from the items that have been damaged to figure my current inventory.

There may or may not be a value in SumExits for each ItemID. I have checked the Control variable and it is giving me the appropriate ItemID but rs!Inventory continues to be null.

strSQL = "SELECT UnitsDamaged - nz(SELECT SumExits FROM qrySumExits WHERE ItemID = " & Control & ",0)AS Inventory FROM [qryUnitsDamaged] WHERE ItemID = " & Control

Set rs = CurrentDb.OpenRecordset(strSQL)
Forms!frmExit!txtInInventory.Value = rs!Inventory

 

[blodzoom]

While that code doesn't help me right now, it is definitely something that I didn't realize that you can do and I will likely use it in the future. Thanks for that.

 

[Joe4]

It actually worries me a bit that you don't understand what I've done. I think it means that I suck at words or I suck at Access.

It tends to be the "nature of the beast". Access projects can get very complex and can be hard to describe without being able to see that data and/or database. On top of that, I am a very "visual" person. Pictures often tell me a lot more than words ever could!

In seeing what you have posted, I think you may be able to implement the last idea I posted, where you have your "Search" button build the SQL code you need to pull back what you want.

 

[blodzoom]

It tends to be the "nature of the beast". Access projects can get very complex and can be hard to describe without being able to see that data and/or database. On top of that, I am a very "visual" person. Pictures often tell me a lot more than words ever could!

In seeing what you have posted, I think you may be able to implement the last idea I posted, where you have your "Search" button build the SQL code you need to pull back what you want.

Yeah, you're probably right. Is there an advantage to recreating a sql query on the fly vs. having a query built that contains everything and running a filtercriteria?

 

[Joe4]

Yeah, you're probably right. Is there an advantage to recreating a sql query on the fly vs. having a query built that contains everything and running a filtercriteria?

Not really sure. I have always done it this one way, and just never really got away from that. Go with whatever works for you (after all, you will be supporting it!). If you are finding issues (i.e. error, time it takes to run, etc), that may be your cue to investigate other methods.

 

[blodzoom]

Thanks, I really appreciate your help today. It took me awhile to get the logic on my filters right, so while I think your way seems more sophisticated, I think I'll stick with what I have as long as I don't see anything wrong with it.

As a side-effect of this thread, I think that I've learned that I under-use queries as a record source for forms.

 

[blodzoom]

Adding the needed fields to my query and then linking the form to the query instead of the table worked great.

 

[Joe4]

Excellent!