Quotation Chaos

[plackowski]

I'm trying to build a SQL statement so I can create a recordset from my data. I'm pretty new to this, so I started by working from an existing module. I managed to get this string to work:

strSql = "SELECT CableNumF FROM CableRoute WHERE " & strWhere & " ORDER BY [CableNumF], [Segment]"

...if I set strWhere to "[CondNameF] = """ & [CondName] & """" as an argument in my recordsource.

However, I'm trying to build the function completely in VBA. I tried following the instructions here http://www.baldyweb.com/buildsql.htm and came up with the following:

strSql = "SELECT CableNumF FROM CableRoute WHERE CondNameF = " & Chr$(39) & CondName & ​Chr$(39) & " ORDER BY [CableNumF], [Segment]"

I've tried every variation of quotes and apostrophes I can think of, but when I output the string to my report to test it, I can never get CondName to convert to its contents. All of the fields are text.

[aytee111]

If CondNameF is a text field, then it will be
....CondFNameF='" & CondName & "' ORDER BY....

[plackowski]

That just returns

SELECT CableNumF FROM CableRoute WHERE CondNameF = '' ORDER BY [CableNumF], [Segment]

when it should return

SELECT CableNumF FROM CableRoute WHERE [CondNameF] = "C002" ORDER BY [CableNumF], [Segment]

[aytee111]

WHERE CondNameF = single quote double quote & CondName & double quote single quote

[plackowski]

That's what I used. I copied and pasted.

[aytee111]

Then the CondName must be empty, as it is showing. Put a VBA stop on that line and see what the value is in that field. Is it coming from a form?

[plackowski]

CondName is a field that exists in the table and report I am calling the function from. It is not a field in the CableRoute table. It does come up empty when I stop the code at that line as you suggested.

[aytee111]

Then that is your problem, not quotations - they are working correctly as coded. All you have posted is the SQL string, no idea where CondName is coming from.

[plackowski]

So there's no way for me to hard code my function to work with a particular table? In other words, I can't retrieve fields from the table the function is running in, unless I pass them in with an argument?

[aytee111]

Remove the WHERE condition, then you will get all records - not sure I understand what you are trying to do. In your first post you were truing to pass a parameter?

[plackowski]

I've got three tables, Conduit, Cable, and CableRoute. Each cable can pass through multiple conduit, and each conduit can contain multiple cable. To keep track of these relationships, I created the CableRoute table, which uses CableNumF, Segment, and CondNameF as fields. The F is for foreign key, since CableNum and CondName are the primary keys in the Cable and Conduit tables, respectively. The CableRoute table uses CableNumF and the Segment as a dual primary key. So each cable is now split into multiple segments (1,2,3, etc) and each segment is assigned to a conduit.

Now I'm trying to create a conduit report. And for each conduit, I need to use concatenation to form a list of the cables they contain. So I'm trying to create a SQL query that will grab all CableNumF's from the CableRoute table where the current CondName (in the conduit table) matches the CondNameF (in the CableRoute table).

[aytee111]

The query will handle the linking. The record source for your report will be a query that will have all conduits and each cable that belongs to it, you do that by linking the two tables together. Before you start creating a report, have the query ready, containing all the fields that you want on your report. Then use the report wizard to create the report, use the grouping options to separate the report into conduits showing all the cables which belong to it.

[plackowski]

The problem with that is that each conduit would then take up an extra row for each cable it contains. But I'm trying to mimic an existing table set up in excel, and if I can't summarize all the data for each conduit on one line, the final report size will balloon from 30 pages of conduits to 300.

Once I've got the query built that lists the cables in each conduit, I'm using a while loop to concatenate the query's contents. I'm sure that part works since I borrowed it from another project online. It's just odd to me that I can't build the query completely in VBA and that I have to pass fields into the query using the strWhere variable. If I can pass it in, why can't I hard code it?

[aytee111]

It's odd to me that you have to use VBA for this! There are many ways to handle the multiple-records issue, either you can use a grouped query or you can let the report do it for you. It seems to me that you want a heading for the conduit and a detail line for each cable - that is what reports do, without any intervention. If you don't want to show the cables in the detail section then the summaries can be handled in the group footer, one line on the report per conduit - again, the report will do all of this for you.

[plackowski]

Please, enlighten me! I would definitely prefer to avoid VBA if possible, I just haven't been able to find an example similar to this.