Error Sorting Subform
Apr 20, 2006
Has anyone ever gotten this error message when sorting an Access Project subform based on an SQL table?
"column prefix [name of subform] does not match with the table name or alias used in the query".
I built a main parts form from a SQL table [Parts] using Access Project. I then added a subform [FSinvPARThistory] based on another SQL table [PartHistory] to record history for the part. The two are linked together using primary key 'ID'. I can sort the subform but then when I go to select another record on the main form I get the above error message.
Can you not sort a subform? If not, how can I prevent users from inadvertantly sorting a subform and getting the same message?
Thanks.
View 1 Replies
ADVERTISEMENT
Apr 10, 2006
The question I have is a bit complicated but I'm hoping someone can help me out.
I am using SQL Express as the back end database, connecting through MS Access and using VBA to do the code. I have created a project in Access and in it I have a form in which I created a subform. The subform has a stored procedure as it's record source, the problem is that in order to keep the data in the subform in sync with the data in the form the stored procedure needs a parameter to retrieve the correct data. How does one pass that parameter as well as the stored procedure on the record source setting?
I have been able to do combolist population, or data retrieval using stored procedure but it doesn't seem to be working with populating the subform.
'create a connection to database
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
'Populate the PO Number text box
Dim cmdUnitPrice As ADODB.Command
Dim prmUnitPrice As ADODB.Parameter
Dim rstUnitPrice As ADODB.Recordset
Dim strSQLUnitPrice As String
'Define a command object to call stored procedure
Set cmdUnitPrice = New ADODB.Command
Set cmdUnitPrice.ActiveConnection = Cnxn
'set the criteria to the stored procedure called
strSQLUnitPrice = "sp_GetUnitPrice"
cmdUnitPrice.CommandText = strSQLUnitPrice
cmdUnitPrice.CommandType = adCmdStoredProc
cmdUnitPrice.CommandTimeout = 15
'define the stored procedure's input parameter
Set prmUnitPrice = New ADODB.Parameter
prmUnitPrice.Type = adInteger
prmUnitPrice.Size = 3
prmUnitPrice.Direction = adParamInput
prmUnitPrice.Value = intProdID
cmdUnitPrice.Parameters.Append prmUnitPrice
'create a recordset by executing the command
Set rstUnitPrice = cmdUnitPrice.Execute()
dblUnitPrice = rstUnitPrice.Fields(0)
rstUnitPrice.Close
Set rstUnitPrice = Nothing
In the case of a list I would use a while loop to enter all the values that the recordset returned.
Rodrigo
View 1 Replies
View Related
Nov 16, 2006
I would like to filter a subform by using a sql statement in vba.
I want to be able to say. afterupdate on a combo box. select "*" where[excd]<>"ne" and [billstatus] = bu then select the items where [excd]="Ne" and [billstatus]<>= bu.
What I want is all the "BU bill status that [excd] does not equal "ne" and all the items were {excd] = ne but the bill status does not equal bu.
Hope H.
View 2 Replies
View Related
Jul 20, 2005
Dear All,W2000Office2000Access adpSQLserver DBProblem:Adding a new property for a company in the subform.The FIRST time I Select a property in combobox CPROP_PRP_ID the subform actstrange.1. The selected value doen't show in the combobox!2. The sortorder of the subform changes!Reselecting the same value in the combobox acts normal and adding gives noproblem.Adding a second property for the same company acts normal.Please HELP, I can't solve this problem!All specifications below.Tables:COMPANY: COM_ID, COM_NAME, etc.PROPERTY: PRP_ID, PRP_NAME, ETC.COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUEFRM_COMPANY: source = table COMPANYSUBFRM_COMPRP:1. SubFrm source = view (see sql)SELECT COMPANY_PROPERTY.CPROP_COM_ID,COMPANY_PROPERTY.CPROP_PRP_ID,PROPERTY.PRP_NAME,COMPANY_PROPERTY.CPROP_VALUEFROM dbo.COMPANY_PROPERTY INNER JOINdbo.PROPERTY ONdbo.COMPANY_PROPERTY.CPROP_PRP_ID = dbo.PROPERTY.PRP_ID2. SubFrm: Order By = ViewComPrpsSubFrmSource.PRP_NAME to sort byPROPERTY.PRP_NAME3. SubFrm: Unique table = COMPANY_PROPERTY4. Controls:4.1.combobox that holds CPROP_PRP_ID (Rowsource=SELECT PRP_ID, PRP_NAME FROMPROPERTY WHERE (PRP_COMPANY = 1) ORDER BY PRP_NAME)4.2. CPROP_VALUE
View 1 Replies
View Related
Feb 11, 2007
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Jul 10, 2015
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
View 6 Replies
View Related
Aug 28, 2013
I have a report that has a drill down option that shows all workgroups within a specific parent group and an interactive sorting ass well on both columns parent group & workgroup
This is displayed in pps combined with a SharePoint
The problem is that after sorting the parent group column by name I then press the drill through icon (+) next to it hopping to see all workgroups related to each parent group - but instead
I receive this Reporting Services Error :
The value for parameter 'SortItem' is not specified. It is either missing from the function call, or it is set to null. (rsMissingParameter)
If I don't press the sorting and just drill through it works fine. the sorting itself also works fine.
View 3 Replies
View Related
Apr 10, 2006
Ok this is what I have
$query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";
But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????
View 7 Replies
View Related
Mar 27, 2008
hi,
i need a query based on sorting.
i have date column and time as varchar in another column and other order details
datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1
so i need to get query o/p as
12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm
based on date and time. pls help!
View 6 Replies
View Related
Jun 27, 2006
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
View 3 Replies
View Related
Oct 11, 2006
I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1
When I sort, it works fine unless I get out to 10 digits.
Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10
Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9
The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?
View 6 Replies
View Related
Aug 17, 2006
I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got
ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
View 2 Replies
View Related
Jun 25, 2007
I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
View 1 Replies
View Related
Dec 13, 2007
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
View 2 Replies
View Related
Jan 7, 2008
Hi,
I hope I can explain what I am trying to achieve (in MS SQL Server):
The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name.
I have tried:
ORDER BY date, surname, forename (gives dates in right order but not names)
and
ORDER BY surname, forename, date (gives names in right order but not dates)
Thanks for any advice!
View 2 Replies
View Related
Feb 26, 2004
I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.
Is it more effecient to sort a dataset within my asp.net program?
View 5 Replies
View Related
Aug 2, 2004
Hi,
i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.
EXAMPLE
Initial situation:
BAKKER 0
AKKER 0
CARNIE 0
Now my question is how can i update the table with a sp in wich te outcome is:
AKKER 1
BAKKER 2
CARNIE 3
Can someone please help me?
Greetings,
Mark
View 3 Replies
View Related
Aug 11, 2004
i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?
View 1 Replies
View Related
Apr 3, 2006
Anybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code
THanks!
View 2 Replies
View Related
May 24, 2006
I'm trying to sort by domain user id, I can pull the user id fine but now I want to sort my SELECT by that name. How do I put the value of getUserIdentity into my SELECT statment.
Thanks
<script language="C#" runat="server"> protected string getUserIdentity() { return HttpContext.Current.User.Identity.Name.ToString().Replace("DOMAIN\", ""); }
protected void Page_Load(Object sender, EventArgs e) { username = getUserIdentity(); SqlConnection myConnection = new SqlConnection("Data Source=CLIENTELE;Initial Catalog=forms;Integrated Security=True"); SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM formTable WHERE userID = @username ORDER BY status DESC", myConnection);
DataSet ds = new DataSet(); myCommand.Fill(ds, "names");
MyDataGrid.DataSource=ds.Tables["names"].DefaultView; MyDataGrid.DataBind(); }
</script>
View 1 Replies
View Related
Sep 24, 2001
hi,
i am trying to figure out how to sort the data in a column in an ascending way when the column contains numeric and char data.
In the example I want to sort by category,lookup value. However, as you can see the top two rows are sorted right but the others are all messed up.
I would really appreciate any hints you can give me.
thanks
Rozina
Category ID lookupvalue
CPU Type4CPU Type 1
CPU Type4CPU Type 2
CPU Number51
CPU Number516
CPU Number52
CPU Number532
CPU Number54
CPU Number564
CPU Number58
View 1 Replies
View Related
Jul 7, 2000
I have a table with a composite primary key (ie a,b, autoInteger)
that includes an auto-incrementing system generated integer.
How do I sort out the records such that if a and b above repeat, I only
get the records with the highest autoInteger?
Thanks for any suggestions
Shelly
View 1 Replies
View Related
Sep 8, 1999
I need help sorting a date field. My field looks like this: dd/mm/yy. I want to sort by month. How would I do that?
Any help would be appreciated...
View 1 Replies
View Related
Jul 18, 2005
Hiya,
I'm back with another question.
I have a view of a table which contains users. the SQL is:
CREATE VIEW dbo.vwTravellers
AS
SELECT TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value
FROM dbo.tblTravellers
I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?
Cheers
Guy
I wish I'd listened in that SQl Server training course I attended :(
View 12 Replies
View Related
May 19, 2006
I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this?
Thanks,
Kris
View 1 Replies
View Related
Dec 29, 2004
Hi,
I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:
mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"
So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!
View 3 Replies
View Related
Mar 3, 2005
Generally speaking, say using normal SQL if u wanted to sort month names in correct order how would u do it???
View 7 Replies
View Related
Nov 6, 2006
hi all..just a quick question about sorting a query. let us say that i am running a select statement that involves 3 or 4 tables and one of the columns in one of the tables is a divisions column. based on the records, there is a specific division that i want to appear first in the list. its not alphabetically possible nor is it the keys associated with it allow it to be selected first. how would i go about pulling that particular record from the divisions column to be listed first in my query? thanks alot
tibor
View 12 Replies
View Related
Jan 9, 2004
Hello,
I have an appointment table that stores the appt time as varchar. For example, 1:00 PM or 9:00 AM. When I say "ORDER BY Appointment.Time", it puts 1:00 PM before 9:00 AM. How do I make it put 9:00 before instead? Could you please help me out.
Please note, I store time as varchar because it is much more convenient for me through out the application that way.
Thank you very much.
View 4 Replies
View Related
Feb 3, 2004
i want to modify this table:
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678
John A. keas 19 9515670
Mike B. highway 2 8652332
Mike B. highway 2 8656543
Mike B. highway 2 8723165
like this :
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678 9515670
Mike B. highway 2 8652332 8656543 8723165
_________________________________________________
may seem silly,i know,but i am stuck here days now and i dont know with what to fill my loop.
thank you in advance.
View 1 Replies
View Related
Feb 3, 2004
i want to modify this table:
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678
John A. keas 19 9515670
Mike B. highway 2 8652332
Mike B. highway 2 8656543
Mike B. highway 2 8723165
like this :
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678 9515670
Mike B. highway 2 8652332 8656543 8723165
_________________________________________________
may seem silly,i know,but i am stuck here days now and i dont know with what to fill my loop.
thank you in advance.
View 5 Replies
View Related
May 14, 2004
Hi,
I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?
Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007
Any help will be appreciated.
View 7 Replies
View Related
Apr 13, 2008
I have a table with a date column. The data looks like this:
11/22/2001 12:00:00 AM
I need to sort the data by the date in ascending order by year, then month, then day.
In my SQL query, I have:
ORDER BY Date ASC
But that's not working. Is there another way?
View 9 Replies
View Related
