Insert delimiterinto SQL statement

[dom24]

Is there a way to state where you want a delimiter to be inserted in an sql statement?
I have a query:

SELECT CourseName, ValidationQ1ValidationQ2ValidationQ3ValidationQ4 FROM Validation

But I want to separate each of the ValidationQ"number" with a delimiter so ti looks like:

SELECT CourseName, ValidationQ1,ValidationQ2,ValidationQ3,ValidationQ4 FROM Validation

Thanks.

 

[lupins46]

I think my question is how on earth did you manage to create the query with the commas missing?

 

[dom24]

Ha ha, ok fair point.
I'll try to explain.........

I'm trying to create a dynamic SQL stateemnt where the users selects the check boxes relating to the field names they want included in their report.
This means that when the name/value pair of the checkbox is passed through the querystring it is taken as a text block from the value. ie. validationq1validationq2validation3.

I then need to add that to my sql statement which means inserting commas between each validationq so that it will work.

Make sense? A little bit?

 

[lupins46]

Ok.
So you are saying something like:

stsrsql = strsql & newfieldname

So do :
stsrsql = strsql & newfieldname & ", "

I don't know how you know you've got to the last one but at that point you use an alternate statement that omits the comma or you use code to remove the comma.

 

[dom24]

No that won't work.
The newfieldname is = ValidationQ1ValidationQ2ValidationQ3ValidationQ4

And I need to put the commas in between them, not at the end.

 

[PHV]

And what about this (ac2k or above)?
Mid(Replace(newfieldname,"V",",V"),2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lupins46]

'ValidationQ1ValidationQ2ValidationQ3ValidationQ4'
Ok.
How specific is this?
Are we just dealing with this particular string or do you have other instances where the strings are different? And does it just go to Q4 in this case?

For this particular string you can do:

strsql = strsql & left(myfield,12) & ", " & mid(myfield,13,12) & ", " & mid(myfield,25,12) & ", " & mid(myfield,37)

 

[dom24]

No I have other instances where the straings are different.
For example the next will be ValidationQ5ValidationQ7....up to ValidationQ10.

They change each time because i've got them under section headings, ie. Personal performance is the heading for the first 4 questions, trainer performance is the heading for the next 6 questions, etc. I just don't wnat it to pull the headings through, I want the headings replace with the question field names so that the query works.

 

[dom24]

I'm using Access 97 by the way.

 

[lupins46]

So can you use PHV's suggestion? (which is simpler, anyway).

 

[dom24]

It doesn't like the Replace function for some reason.
Says "function not defined".

 

[JonFer]

What syntax are you using to build the fieldname string from the checkboxes? Can you show an example?


John

http://www.interautomateconsulting.com

 

[lupins46]

Sorry - you said A97 earlier - ther's no Replace in A97.

Can you not set up the values you are deriving with the commas already in place?

 

[dom24]

No because I'm inserting the values in the Row Source section of hte properties on a list box.
i.e. PersonalPerformance;ValidationQ1ValidationQ2ValidationQ3ValidationQ4;Trainer Performance;ValidationQ5;Course Details;Validation6

and only showing the PersonalPerrmance, Trainer Performance, etc in the list box.
This is because I want the user to select the section heading but this is not stored in the table only the ValidationQ is. Therefore i've handed coded the ValidationQ with the section heading (personal performance) so that I can bind it to the 2nd column and pull it into my query.

 

[lupins46]

There is Replace function here:

http://www.mvps.org/access/strings/str0004.htm

which you should be able to use in A97.

 

[JonFer]

If you have multiple column headings for one combo box item, put them in quotes with commas between like this:

PersonalPerformance;"ValidationQ1, ValidationQ2, ValidationQ3, ValidationQ4";Trainer Performance;ValidationQ5;Course Details;Validation6

The quotations are not stored with the string so the SQL should be okay.


John

http://www.interautomateconsulting.com