Search results

This is giving me a real headache. I tried changing strSQL to use CAST as follows: strSQL = "INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes],[IR_Status]) " strSQL = strSQL & "VALUES (" strSQL = strSQL & "'" & strID &...

I have a sub inside an unbound form that appends a record to a table in SQL server using a pass-through query. The sub creates the necessary SQL then runs a Querydef. One of the fields is a date field. The function runs without error and inserts a record, but the date field is blank. However, if...

It has been a while now since I wrote this and I can't remember my reason. TBH it looks redundant because BL_ID will always be over 16 characters anyway. But the function now works so I will leave well alone.

Of course, stupid me should be WHERE c.PurchaseOrderRef NOT IN. It would have helped if double clicking the error message would have took me to that line instead of the very first line.

I am trying to update a field in one table based on the value of a a field in another table divided by another field in that table. I am getting the following error: Msg 235, Level 16, State 0, Line 10 Cannot convert a char value to money. The char value has incorrect syntax. The error...

How would I write 10190 and 10200?

Quick question on late binding, not done it before is this right: Dim xl as object Dim wbk as object Dim wsht as object 10170 Set xl = New Excel.Application 10180 xl.DisplayAlerts = False 10190 Set wbk = xl.Workbooks.Open(strTemplatePath & "\" &...

Not using APIs most if not all of my database objects are DAO databases, recordsets and querydefs.

Many of my Access databases create Excel Workbooks and powerpoint presentations for reports. Currently I use early binding. I have just been upgraded to Office 2016 but others in my company are running 2013. I am keen to convert my early binding to late binding. Do I just need to do this for...

Having just tried that I can confirm that for SSMS V18.2 double clicking the error works, thank you.

I am getting an error message: Msg 8152, Level 16, State 10, Line 16 String or binary data would be truncated. I believe it is occurring in a stored Scalar-Valued function that is being called from a query. How do I find line 16 because: lines have spaces There are comments To view the...

Now it's not working again and the code hasn't changed, driving me mad!

I have admin permissions on the laptop. I am trying to create the database and data in the instance of SQL Server Express that accompanies the book T-SQL Fundamentals found here: http://tsql.solidq.com/books/tf3/ The first few lines of code are: -- 1. Connect to your SQL Server instance...

You are right, it now works, thanks. I have modified it to catch for no spaces: ALTER FUNCTION [dbo].[GetBudgetCode] ( @strInput as varchar(1000) ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @strOutput as varchar(1000), @strInprocess as varchar(1000)...

Thanks, I had narrowed it down to that line myself by debugging the hard way (replacing variables with fixed values one at a time). No I hadn't realised that if there were no space it would return an empty string. 99% of the strings will have a space and the ones I have tested have a space but I...

I have just received a new laptop from our IT department which they always preconfigure with requested software. The laptop was configured with SQL Server Express 2016/7? and SSMS V18.2. The SQL Express is for me to "play" with and I use SSMS to connect to SQL Express and the company SQL...

I have a function that extracts the first "word" in a string and checks if this word appears in another table. If so it returns string else an empty string . But it is always returning an empty string. To make life a little more challenging SSMS V18 has removed the debugger. I have tried...

Thanks for that, I already have "option compare database" and "option explicit" at the top of all modules I have changed to early binding for all objects apart from textboxes because I couldn't find a textbox object. It also enabled me to discover the issue with mySlide.Layout which should...

I am very much learning about automating powerpoint so much of my coding approaches come from reading articles on the internet (of which there are few compared to access and excel). Most declare as objects and I have just copied. I must be honest I don't like declaring as objects because I don't...

Thanks for that, you have given me a lot of ideas to play with and answered my question regarding is it possible to pass objects as arguments to a function and return objects back. I will go away and play. I was originally planning to insert the powerpoint code after each object I created...