Duane Hookom's -Concatenate and filter problem

[BBBryan]

Hi,
I was looking for help.
I am using Duane Hookom's Concatenate Module.
I am using this in my query
CombinedRRs: Concatenate("SELECT DISTINCT [No] FROM [RR tbl] WHERE RR =""" & [RR tbl].[RR] & """")
this works good But I want to filter by another Column [CompleteDate]
If the [CompleteDate] Not Is Null. I only want those to Concatenate.

I am not sure how to write this.
Thanks BBryan

 

[boblarson]

I find it easier to use Chr(34) instead of """ or """". It is less confusing.

So, using that method, it would be:


CombinedRRs: Concatenate("SELECT DISTINCT [No] FROM [RR tbl] WHERE RR =" & Chr(34) & [RR tbl].[RR] & Chr(34) & " AND [CompleteDate] Is Not Null")

 

[BBBryan]

Thanks Bob
Works Perfect!

 

[BBBryan]

Just another question on this.
If I wanted anther filter would I just add & " AND [another thing] Is Not Null" to the end

Like this
CombinedRRs: Concatenate("SELECT DISTINCT [No] FROM [RR tbl] WHERE RR =" & Chr(34) & [RR tbl].[RR] & Chr(34) & " AND [CompleteDate] Is Not Null" & " AND [another thing] Is Not Null")

 

[boblarson]

Just another question on this.
If I wanted anther filter would I just add & " AND [another thing] Is Not Null" to the end

Like this
CombinedRRs: Concatenate("SELECT DISTINCT [No] FROM [RR tbl] WHERE RR =" & Chr(34) & [RR tbl].[RR] & Chr(34) & " AND [CompleteDate] Is Not Null" & " AND [another thing] Is Not Null")

No need to concatenate it in. Just add it to the end of the line:

CombinedRRs: Concatenate("SELECT DISTINCT [No] FROM [RR tbl] WHERE RR =" & Chr(34) & [RR tbl].[RR] & Chr(34) & " AND [CompleteDate] Is Not Null AND [another thing] Is Not Null")[/QUOTE]

 

[boblarson]

The reason being that the part you're adding is all stuff that fits INSIDE the quotes.

 

[BBBryan]

Thanks - Now I understand that (I was thinking it the wrong way)
Thanks again