Help with variable in SQL (1 Viewer)

irish634 · Mar 23, 2019

I have been driving myself insane with this, because I know it's something simple.

I am building a SQL statement that requires the use of a string variable. For the life of me, I cannot get this to work.

Code:

sSQL = "SELECT [lng_RecordID_PK], [txt_QMSCA_CAID], [txt_QMSCA_Source_FK], [dtm_QMSCA_DateInitiated], [txt_QMSCA_Initiator], " & _
                        "[txt_QMSCA_AssignedTo], [dtm_QMSCA_CADueDate], [dtm_QMSCA_VerificationDueDate], [txt_QMSCA_Status_FK], [txt_QMSCA_Category_FK] " & _
                "FROM BE_tbl_QMS_CA_CorrectiveActions " & _
                "WHERE ((([txt_QMSCA_Status_FK]) = [B][COLOR=Red]'" & sStatus & "'"[/COLOR][/B] & _
                "ORDER BY [lng_RecordID_PK] DESC;"

I have tried every which way I can think of to get this to work, but it returns 0 records every time.

Can someone point me in the right direction?
Access 2016

Dreamweaver · Mar 23, 2019

"[txt_QMSCA_Status_FK]= """ & sStatus & """" & _

irish634 · Mar 23, 2019

Thank you!
Told you it was simple... LOL

The_Doc_Man · Mar 23, 2019

Also, when doing that kind of substitution, be sure that you remember to leave a space inside the quoted string to act as an element delimiter. Access CAN sort this out sometimes, but with substitution, you could run into issues of being accused of missing an operator or having some general syntax error. In the string-builder sequence you showed, for example, there would be no space in the resolved string just before the ORDER BY clause.

Help with variable in SQL (1 Viewer)

irish634

Registered User.

Dreamweaver

Well-known member

irish634

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread