Recent content by ChopinFan

Yes, we tried the standard method for an access data project, putting parameters in the report properties, but it didn't like it and wouldn't do what we wanted, so this was the solution we got to work. This is my whole procedure. I didn't mention earlier that it also has input parameters...

Yes, only specific columns I would test for nulls.

I have sprocs that execute sql statements stored as a string, i.e.: CREATE PROCEDURE dbo.SprocName AS set nocount on declare @SQL as varchar (2000) set @SQL = 'SELECT yada yada etc.' exec(@SQL) Using the above procedure, I need to be able to check for null values the query may be returning...

Excellent. I had to add additional single quotes to use this inside the @SQL string, but it works. Set @SQL = 'SELECT Stuff(Stuff(FieldName, 5, 0, ''-''), 3, 0, ''-'') AliasName' etc Thanks!

Well, I got around the problem by splitting the field into three pieces in the query and inserting the dashes in the application layer instead. Nevertheless, if it would be helpful to know how to do this in the query for future reference if anyone knows how. Thanks!

Thanks George, but I actually do have a space there in my actual sproc. It just didn't make it into my example above. Good catch, but without the concatenation, the whole thing runs fine.

I need to concatenate into a string used as a select statement that creates a view, and I'm finding it doesn't like the syntax I am using. I already use the same syntax in a case when construct with no problem. Declare @SQL as varchar(2000) Set @SQL = 'Select FieldName From...' etc Declare...

Thanks for your valuable help!

Thanks for the good info. I am runnig SQL2K, but I don't have any need to manipulate or search through the text in any way, so it sounds like Text is the only viable option. It sounds like each text field takes up only 16 of the 8060 bytes available per row (only storing a pointer) while a...

If I'm understanding correctly, using text fields is the way to go. Am I to understand that if the total datalength yielded by the query you give is less than 8060, there's no problem?

Among other fields, there are seven in my table that could potentially contain 8,000 characters each and I am concerned about the memory size that any given row will take. I don't want to go over the memory limit. I understand that the Text datatype actually uses multiple data pages in the...

Excellent. "Datalength" does the trick. Thanks!

How do I query a text field to determine the maximum number of characters entered into it among all the records? Does SQL have an equivalent for a VBA Len() function? Thanks!

"I would like the report to print everyone with a total other than £0.00 in any of those four fields." It sounds like you're just looking for a where clause that says WHERE field1 <>0 OR field2 <>0 OR field3 <>0 etc. Am I understanding your need correctly?

How do I get a pass through query to accept input parameters? I have no problem using the syntax below in regular SQL queries and it prompts the user for the information, but it isn't liking this in a pass through for some reason. --Input Parameters @Value1 varchar(500), @Value2 varchar(2) AS...