1

Microsoft Access subform filter output looks like this: 

**([qryPOExamDetail subform].[Line Description]="1"" CONDUIT - EMT")**

The actual value in the field is 1" CONDUIT - EMT. I've converted the above argument to [Line Description]='1"" CONDUIT - EMT' but the dynamic query returns zero records.

I've built a SQL statement to create a dynamic query for export to a comma delimited file via VBA doCmd.TransferText function. I filter on other fields (without "inch" marks) and it works fine. I've searched the internet for an answer and cannot find anything.

How do I get SQL to recognize that 1"" CONDUIT - EMT = 1" CONDUIT - EMT?

Below is the SQL string for creating the dynamic query:

strSQL1 = "SELECT tblOpenCommittment.[Job Number], tblOpenCommittment.[Job Name], tblOpenCommittment.[Order Number], tblOpenCommittment.[Supplier Name]," _
& "tblOpenCommittment.[Order Date],tblOpenCommittment.[Cost Code], tblOpenCommittment.[Line Description], tblOpenCommittment.Qty, tblOpenCommittment.Tax, " _
& "tblOpenCommittment.Price, tblOpenCommittment.Unit, tblOpenCommittment.[Total Line Value],tblOpenCommittment.[Total Line Value]-tblOpenCommittment.[Line Total Amount invoiced]" _
& "AS [Open]FROM tblOpenCommittment WHERE (((tblOpenCommittment.[Job Name])=[Forms]![frmPrintByProject]![txtBoxJobName]) AND ((tblOpenCommittment.[Cost Code])" _
& "Like" & Chr(34) & Chr(42) & Chr(34) & Chr(32) & Chr(38) & Chr(32) & "[Forms]![frmPrintByProject]![txtBoxFrameValue]) AND " & strWashedstrFilter & " )"

The very last argument strWashedstrFilter represents the output of the subform filter listed at the head of my original post. As I've stated earlier it works as long as the value filtered on does not contain ( " ).

I've read this site for years and gotten outstanding help. This is the first time I've ever posted a question. Thank you all in advance for taking the time to comment.

GregCarr
  • 31
  • 2
  • 1
    It's not too clear from your question exactly where the text you show sits in your full process, so a bit more context around that would be useful, maybe along with an example of the SQL produced. – Tim Williams Aug 09 '18 at 15:11
  • If I understand your question correctly, I would suggest using parametrized queries. [See here](https://stackoverflow.com/a/43522802/8597922) – Victor K Aug 09 '18 at 16:46
  • Those are better known as *embedded quotes*, and you can probably find an existing question here using that search phrase. – Ken White Aug 09 '18 at 16:51
  • SQL works for me with the parameter as a literal string in query object. If you are building SQL statement in VBA, edit question to provide your code. – June7 Aug 09 '18 at 18:05
  • How does the filter string get the doubled quotes? – June7 Aug 09 '18 at 21:42

2 Answers2

1

As an example, (as I am not really certain of your data structure or columns) you would use something like this for MS Access:

SELECT "My Quote goes > "" < right there"

So, to compare the values it would be like this:

SELECT ...
WHERE [MyColumn] = "1"" CONDUIT - EMT"

Or (if you prefer)

SELECT ...
WHERE [MyColumn] = '1" CONDUIT - EMT'
ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
0

You are missing some spaces in the construct so words don't run together. And concatenate references to form controls:

strSQL1 = "SELECT *, [Total Line Value]-[Line Total Amount invoiced] AS [Open] " & _
          "FROM tblOpenCommittment " & 
          "WHERE [Job Name]='" & [Forms]![frmPrintByProject]![txtBoxJobName] & _
          "' AND [Cost Code] LIKE '*" & [Forms]![frmPrintByProject]![txtBoxFrameValue] & _
          "' AND " & strWashedstrFilter

If code is behind frmPrintByProject, can use Me.:
"WHERE [Job Name]='" & Me.txtBoxJobName & "

June7
  • 19,874
  • 8
  • 24
  • 34
  • you're awesome!! Here's a paste from the immediate window: ** SELECT *,tblOpenCommittment.[Total Line Value]-tblOpenCommittment.[Line Total Amount invoiced] AS [Open] FROM tblOpenCommittment WHERE [Job Name]= 'Adidas' AND [Cost Code] LIKE '*100M' AND (tblOpenCommittment.[Line Description]="1"" CONDUIT - EMT")** – GregCarr Aug 10 '18 at 19:40
  • How do those doubled quotes get into the string? Query works for me with single quote mark and apostrophe delimiters. – June7 Aug 12 '18 at 17:56
  • (maybe duplication) Filter By Selection property = `Forms!frmPrintByProject.[qryPOExamDetail subform].Form.Filter` Returned Variable = `([qryPOExamDetail subform].[Line Description]="1"" CONDUIT - EMT")` Because the exported data is drawn from an underlying table the variable “strFilter” has to be modified using the replace function `strFilter = Replace(Forms!frmPrintByProject.[qryPOExamDetail subform].Form.Filter,"[qryPOExamDetail subform]", "tblOpenCommittment")` Returned variable = `(tblOpenCommittment.[Line Description]="1"" CONDUIT - EMT")` – GregCarr Aug 12 '18 at 19:14
  • The last two lines of the strSQL1 statement using your guidance look like this: `"' AND [Cost Code] LIKE '*" & [Forms]![frmPrintByProject]![txtBoxFrameValue] & _ "' AND " & strFilter` – GregCarr Aug 12 '18 at 19:19
  • I tested data with quote mark. Applied filter to form. Pulled that filter string from FilterBy property. There is single quote. I don't use the Replace() function and instead surround with apostrophe delimiters. The SQL statement constructed in VBA works for me. The apostrophes can be within the strFilter variable but if they are not, include them in the SQL construct. – June7 Aug 12 '18 at 19:20
  • One other question regarding the SQL statement. The calculated [OPEN] field exports to column A in the CSV file and the wildcard fields populate column B and beyond. Is there a way to reverse that without listing the fields in order? It would be better if [OPEN] was the last column. – GregCarr Aug 13 '18 at 10:54
  • AFAIK, if you want the export in a particular order the fields must be explicitly listed in the SQL. – June7 Aug 13 '18 at 17:59