Search results

Good catch isladogs. My brain wanted to see them as the same. But that would only affect the total number of rows affected. Error messages would be PK or unique indexes ... not sub query returns too many rows. Still a mystery. Wayne

Isaac, We're just gonna have to wait on the OP. That message could very well come from a trigger. A trigger that was written for single-row updates !!! It might do a nested select from INSERTED that would do return this type of error. But, that would be VERY evident in the error message shown...

I'm also late to the party. The error message seems invalid because ... there is no subquery! The SQL shown can't produce that message. The SQL as shown should result in a message of some number of rows affected. Any message involving "subquery" can only result from a trigger on the table. The...

Howard, I have a real computer now :) You have a code like this: AddToWhere [Look For Title], "[Title]", Mycriteria, ArgCount The AddToWhere routine probable has a line something like this: (you didn't attach it) mycriteria = micriteria & Column & " Like '" & SearchValue & "*'" Note...

Hi Howard, On an iPad so very hard to type Change to --> = replace(myrecordsource, "'", "''") Relax, no ones demeaning you, welcome. Hth, Wayne

I’m on an iPad and can’t type, but ... The newer version of the server will tell you the row/column that violates ... finally :) In lieu of that you can: 1) select * into mytemp from your table where 1 = 0 (no data) 2) design mytemp, allowing all column to be null able. Then you can...

Mittle, I just started reading this thread and I have a few thoughts that might help. SQL Server does quite a bit of planning before it runs any query. Its query analyzer is very adept at working with tables. When you run SSMS and look at a table; a lot is revealed. There are primary keys...

Tegun, Floating point numbers are not good at all. They can't give an EXACT rendition of some very simple numbers. The part to the right of the decimal point is made up by adding (1/2, 1/4, 1/8 ...) for a finite number of bits. Even with double precision there's numbers it can't do (like ...

I finished typing this response; only to find the thread gone. If it helps the OP then here goes ... Kheribus, If your case statement may return NULL on several conditions: CASE WHEN ecsss.transportation.private_program IS NOT NULL THEN (SELECT cluster_name FROM...

I don't know if I should be replying to a SOLVED thread. I just read this and don't think we really finished. And they want activity in this forum. So with apologies to NauticalGent: Post #19 had a Case statement in the Where clause: WHERE CASE WHEN @Comp = 0 THEN...

I'd imagine that your where clause in the TVF should have something like: Where Comp_Date = @CompDate OR (Comp_Date Is Null AND @CompDate Is Null) I'm not using Access much nowadays ... you can't link the TVF and treat it like a table can you? I suppose you're...

Galixiom, Jeff Moden from sqlservercentral.com developed a great splitter: https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function I've been using it for quite a while and its great. It very fast and easy to use. It also PRESERVES THE...

Couldn't resist playing with this a bit ... use demo go Create Table Patients ( Patient_Name varchar(50), Patient_3 as Left(Patient_Name + ' ', 3)) go Delete From Patients Insert Into Patients (Patient_Name) select Char(65 + Abs(Cast(Substring(Cast(Newid() as...

Frosting, I tried the following in SSMS and got: declare @PATIENT_NAME varchar(30) = 'abcd' -- -- Try whole expression -- select LEFT( LEFT(@PATIENT_NAME, (LEN(@PATIENT_NAME) - (LEN(@PATIENT_NAME)- CHARINDEX(' ', @PATIENT_NAME) + 1))) , 3) Msg 537, Level 16, State 2, Line 6 Invalid...

Ksabai, Your where clause does not need the Case statement. The case statement will either return: 1) Null 2) [tblcontract].[DtShip04] if ShipCont in (4,7) Its simpler to just use the criteria in the CASE in your where clause. WHERE ([tblcontract].[ShipCont] = 4 OR...

Heel, The group by will be in each of your data CTEs. The calendar CTE will define the “skeleton” of your query. Investigate CTEs, that’s where your answer lies ... and it’ll change the Way you write your SQL for the better. Wayne

Heel, Hey that’s ORACLE! What you need to do is make 1 query with CTEs (ORACLE has them) 1st part generates a date calendar with one date per row. 2nd part joins Sales to the calendar by date with your group by 3rd part joins Returns to calendar by date with your group by Finally, join...

On iPad so not easy to type. Must add: 1) common table expressions (CTEs) 2) window functions in queries (lead, lag, row_number(), etc) 3) real triggers bound to data 4) real constraints bound to data 5) cross database/ Cross server queries 6) XML support for queries (multirow to CSV) 7)...

Shouldn’t it be acNewRec ? Wayne

Mark, Gina This guy has been using Access for about ten years. He is trying to make a universal display subform. He has a subform with 255 unbound text boxes. By just setting the Recordsource he actually displays the data, but no code works. He has msgboxes in all the events, but none are...