Search results for query: *

I'm trying to use TOP 3 in a query that I've made the recordsource of a subreport and it returns no records. If I simply remove the "TOP 3" portion of the query, it returns records, no problem. Is there a caveat to using TOP 3 in certain ways? Here's the query I am using as my recordsource (the...

New to SQL Server and I'm having a BLAST! Converting an Access database, and really enjoying the experience. Since I'm not the most experienced SQL guy yet, I was wondering what the best approach might be to this problem: I have a simple query with four tables: tAGProjects, tProjectTypes...

This query requires a lot of tables because I'm giving the user a LOT of flexibility with optional parameters. Everything is working fine except I added four more tables/parameters to the end of the query (Producer, ExecProducer, Director, Contact) and it has caused a huge jump in the time to...

I've created a stored proc with a dynamic where clause - I've declared sSQL as a variable of type nvarchar(max) so that I can then run sp_executesql at the end of the stored proc passing in the sSQL variable. Everything was working fine last week, but now, the sp won't return any results and...

I have created a function that accepts two comma separated text strings sNew and sExisting- I put both csv strings into an array then I check sNew against sExisting to see if any of the sNew are contained in sExisting. If so, I remove the sNew value from sExisting. The problem I'm running into...

I am dynamically creating a WHERE clause in my query, and the the "in" statement is only processing the first of the multiple values I pass it. Here's the statement: sSQL = sSQL + 'WHERE tAGProjects.AGProjectKey IN (COALESCE(' + @AGProjectKey + ', tAGProjects.AGProjectKey))' I pass into the...

I have created a stored proc that does the following: 1. Creates a one column recordset of client numbers from a union query (never more than 150 records) 2. Open a cursor and loop through the recordset inserting a new record into an expense statement table based on the current record. 3. Prior...

I have a query in Access that I need to convert to a stored procedure in SQL Server. This has a lot of Access-specific SQL so any help making it work with SQL Server is appreciated! FYI- the query is doing the following: -The first select determines clientkeys where the expenses have been...

I have a query with many tables and criteria and I'm trying to create a dynamic WHERE clause using COALESCE and Case statements. So far it's working well except in this case: My main table is "Projects" and it is linked to the "Clients" table via a many-to-many table called "ProjectClients". In...

I have a listbox that is multiselect. As I'm grabbing the keys in the first column, I want to grab the descriptions in the second column to cram into a string for display purposes. Here's the code to grab the first column: For Each varItem In Me.lstOtherCriteria.ItemsSelected sID...

I'm trying to get my Access front-end MDB converted to a SQL Server back end. I can't make it an ADP, so I'm stuck with some limitations. I have a report that contains 12 different subreports and as far as I can tell, there's no way to use pass-through queries as the recordsource for the...

I have a query that gathers all information on our projects. (tProjects). All contacts working on the project are linked to this table from tContacts by project many to many tables. (ie: tProjects linked to tProjectProducers which is linked to tContacts. The same for tProjects ->...

I am new to SQL Server 2005 so forgive me if my questions are basic, but I've moved past simple queries and I'm starting to find myself without the proper vocabulary to get some things done! I have a query that joins three tables to extract contacts and their person types. SELECT...

I am just starting a conversion from Access to SQL Server 2005 and I'm using it as an opportunity to learn SQL Server in the process. I am testing extensively but want to occasionally update the SQL Server testing db with data from my live Access database. I need to drop all tables from my...

I have inherited a database that I am converting to a SQL Server 2005 backend from the JET database. The previous programmer created a function for action queries that he passes a sql string into and then uses the db.execute method. Function RunSql(sql As String, Optional plOptions As Long) As...

I'm converting my Access database to an Access front end/SQL Server 2005 back end. I'm using ADO and I want to use pass-through queries to execute stored procedures to populate list boxes, combo boxes, forms and reports. (I'm basically using the pass-through query as the record source, which...

I am loading a record into my unbound form and putting the values in both the fields and tags of the fields. If the user makes an edit, upon close of the form I run a comparison to see if the ctl.value and ctl.tag don't match, at which point I post the edit. The problem is that I had a field...

I'm using unbound forms and I'm having some difficulty trying to check for edits to a form and then post those edits before going to the next record that the user selects. First, here's the code I'm using to check for edits: Private Function CheckForEdit() As Boolean On Error GoTo ErrHere...

I have put together a function that clears controls on my unbound form, but I'm having a strange issue- when I clear my controls (acTextBox, acCheckBox, etc.) I set it to null, but if I try to set the same control's tag value to null it errors out. If I set the control's tag value to an empty...

I was wondering if there was a better way to check for edits on an unbound form? I have about 15 fields that can be edited and I want to check for changes made when the user exits the form. Currently, when I load the form, I populate the field and it's tag property with the same data. On Close...