Having issue with Access 2013 VBA Code SELECT-WHERE

[kleaverjr]

I am just become comfortable programming with VBA in Access 2013 (I'm used to using MYSQL and VB6 when I used to write programs for work 20 years ago!). So I am am an experienced programmer but by no means am I an expert at it, and I have run into a problem I can't seem to find a solution for, so I understand Office 2013 is about to be no longer supported, but since this program is going on a computer that is isolated from any exterior threat (i.e. not connected to the Internet) and I don't wish to spend more money on software that does the job. VBA for Access 2013 is designed to do what I am asking it to do, I just can't figure out why it's not working. It's a simple SQL SELECT Statement with a few conditions, that when I look at the Table, the record matches, but when I run it, there are no records being found. Here are the specifics:

I have a table "Inventory_New" that has a field "Sold_Location" that is blank until the item is Sold at a show or online and the field is updated to "Online" or "Show". Since there can be multiple quantities of the same Item SKU (Field "Item_SKU") I have the Select statement setup as follows:

TableFindItem = "Inventory_New"
rcdFindItemSKU = The SKU it is searching for

"SELECT * FROM " + TableFindItem + " WHERE Item_SKU = '" + rcdFindItemSKU + "' AND LEFT(Sold_Location, 1) <> 'S'" ' AND LEFT(Sold_Location, 1) <> 'O' ORDER BY Vendor_Purchase_Date ASC"

So it is selecting from the table, where the SKU matches the search SKU AND it is searching for all records that do not have an "S" or "O" as the first character in Sold Location. When I did a simple where Sold_Location = '', sometimes I would get an error that the field was null and would crash the program. Is there any other code that can be used to select the records that match the searchSKU and has no entry in the Sold_Location field?

I have spent hours on trying to figure out why this is happening, and have yet to find an answer.

Thanks for any help that can be provided!



Ken L

[Welshgasman]

Use Nz() ' extra text as message too short

[Micron]

I recommend you use the query design tool and copy/paste into vba if you want to build sql in code. Pretty sure you didn't, otherwise you wouldn't have plus signs in your sql like that. & will only concatenate strings, but + is likely to perform conversions and attempt sums when it can. Or swap your + with & and see if you're OK after that. When troubleshooting vba sql, always a good idea to debug.print and review in the immediate window, and if necessary, copy/paste to a new query and switch to datasheet view to test.

[kleaverjr]

I will try that...

I never took classes to program VB or VBA, I learned on my own, so that shows. I always used "+" signs in my SELECT strings, and up until now they always worked. Maybe that is contributing to the issue.

Thanks.

Ken L.

[Micron]

Maybe this will help explain the danger of using an arithmetic operator for concatenation (in Access).
https://docs.microsoft.com/en-us/off.../plus-operator

[kleaverjr]

Thank you for teaching me something new.

Thanks all for your contributions to the solution. Things seem to be working now.

Ken L

[June7]

& character is preferred for string concatenation. The 'S'" ' appears wrong.

Consider:

"SELECT * FROM " & TableFindItem & " WHERE Item_SKU = '" & rcdFindItemSKU & "' AND LEFT(Sold_Location, 1) <> 'S' AND LEFT(Sold_Location, 1) <> 'O' ORDER BY Vendor_Purchase_Date ASC"

or

"SELECT * FROM " & TableFindItem & " WHERE Item_SKU = '" & rcdFindItemSKU & "' AND NOT Left(Sold_Location, 1) IN ('S', 'O') ORDER BY Vendor_Purchase_Date ASC"

or

"SELECT * FROM " & TableFindItem & " WHERE Item_SKU = '" & rcdFindItemSKU & " AND Sold_Location LIKE ""[!'S', 'O']*"" ORDER BY Vendor_Purchase_Date ASC"

+ character can be useful for some concatenation. Keep in mind: Anything + Null returns Null. Anything & Null returns Anything. So say you have fields for Employee and Spouse and want to concatenate. But what if Spouse is Null? Employee & " and " & Spouse could return: John Jones and; whereas Employee & " and " + Spouse will return: John Jones


Why is the table name dynamic? Why would there be multiple inventory tables?

[kleaverjr]

There are two types of Inventory and two totally different types of inventory "Collections" and "New", each requiring their own Table Design. I found it was easier for several reasons to keep the two in separate tables. There are enough in common where I can use the same SELECT statement, as each of them have a "SKU", "Sold Location" etc fields. Not sure if that makes sense or not, but so far it is working. And the recommendations made fixed the problem. Your suggestions for other SELECT statements will come in handy as I can see other situations where that specific Syntax would be used.

Ken L