I have a Report that uses a query as it's record source. I have the query ordered by a field, which works as expected in the table view of the query. In the report, however, the ORDER BY clause does not seem to carry through. The field is not sorted Ascending. What's going on?
View RepliesHi
I have a problem with an Order By Clause.
Basically, my database holds records of sites in the UK, and I have a select query (qry_SumGIS3) that opens up a summary report (rptSumGIS3) when a button is clicked on the menu page. The report opens perfectly, showing me all the information I need to know about all the sites in my database. However, it is ignoring the Order By Clause I have specifically told it to do. I want to order the records that appear in the report by a calculated field (OBJ_WS_OVERALL_CALC) in descending order (highest to lowest percentage). Instead, it is ordering it by the site id (RTP_ID). How can I stop it doing this?
This is my code (that was created in the SQL view of the query design):
"SELECT MEASURE1.OBJ_WS_OVERALL_CALC, [Tab 1: Factual].RTP_ID, OBJECTIVES.OBJ_ID, OBJECTIVES.OBJ_NAME, MEASURE1.MEASURE_ID, MEASURE.MEASURE_NAME, MEASURE1.MEASURE_SCORE, MEASURE1.MEASURE_WEIGHT
FROM [Tab 1: Factual] INNER JOIN (OBJECTIVES INNER JOIN (MEASURE1 INNER JOIN MEASURE ON MEASURE1.MEASURE_ID = MEASURE.MEASURE_ID) ON OBJECTIVES.OBJ_ID = MEASURE.OBJ_ID) ON [Tab 1: Factual].RTP_ID = MEASURE1.RTP_ID
ORDER BY MEASURE1.OBJ_WS_OVERALL_CALC;"
The red line is the part of the code that I think is being ignored.
Anyone that can help me is a lifesaver!
I am working on a commercial website that is using ASP and an access database.
I have a pretty elaborate select statement that works, except for part of the ORDER clause.
Here is the scenario.... my client sells automotive parts...
Table 1: (itemsku) holds part numbers
Table 2: (itemveh) holds vehicles that work with those parts along with some descriptive clarification (cab size, bed size, years, ...)
Table 3: (make) holds the vehicles make name (ford, chevy, ...)
Table 4: (model) holds the vehicles model name (F150, Ram, Silverado...)
I need to get a list of parts and their approriate descriptions and sort it by Make, Model, Specific Description, and year.
Ideally, it should look like this:
Chevy: Silverado, Longbed, 2007-2008
Chevy: Silverado, Longbed, 1999-2006
Chevy: Silverado, Shortbed, 2007-2008
Chevy: Silverado, Shortbed, 1999-2006
Ford: F150, Longbed, 2004-2008
Ford: F150, Longbed, 1997-2003
Ford, F150, Shortbed, 2004-2008
My select statement reads as follows (spaced out for readability):
select
itemsku.id, itemsku.item_id, itemsku.sku, itemsku.desc_sku,
itemveh.yr_beg,itemveh.yr_end,itemveh.desc_veh,
model.name as modelname,
make.name as makename
from
((itemsku left join itemveh on itemsku.id=itemveh.itemsku_id)
left join model on itemveh.model_id = model.id)
left join make on model.make_id=make.id
where itemsku.item_id=4
order by
make.name,model.name,itemsku.desc_sku
If I put it as listed, all sorts properly, except for the years. As soon as I add to the end of the order by....
, itemsku.yr_beg desc
Then, the desc_sku portion does NOT sort properly, but the years do. I have tried dropping the desc as well to see if that would help, but the desc_sku does not play into the sort factor if the yr_beg is added.
Am I missing something or is their a limit as to the number of fields in an ORDER?
Thanks ahead of time....
Pete
Can someone please tell me if it is possible to pass in a parameter for the sorting field to a stored procedure in MS Access.
When I tried to execute a query similar to below, neither did the query give me an error nor did it execute correctly.
PARAMETERS ORDERBY_FIELD as Text;
Select * from Employees Order By ORDERBY_FIELD;
It will be great if someone could help me with this.
Thanks.
From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?
SELECT Fields FROM Table WHERE ConditionA and ConditionB
or
SELECT Fields FROM Table WHERE ConditionB and ConditionA
Windows 7
Access 2013
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed.
2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code:
Private Sub btnJobEntry_Click()
'GOAL: open the work order form to the correct entry
'METHOD: store the uniqueID to a variable, then use that in the open command's where clause
Dim strJobID As String
'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
WinXPPro Sp3
Access 2007
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
List_Order
3
1
15
5
4
12
2
7
Hi,
I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));
I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
Is this the query? Or the coding?
Thanks
i posted about this the other day, thought i sorted it and now it keeps coming back to haunt me
i have an append query in my database it is based on 3 other queries
when the query is run using vb
DBEngine(0)(0).Execute "QRY_ImportDuplicatesTopLevel", dbFailOnError
or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL
i get an error message saying too few paramters - expected one
i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field
the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do
does anyonw have any idea why this situation could occur?
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
the query looks like this:
SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));
I'm on access 2007
I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.
I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.
I am creating a search function to search a name. The DB stores it as [First Name] , [Last Name].... This query works
Code:
SELECT *
FROM tblVolunteers
WHERE ((([First Name] & " " & [Last Name]) Like "*bob j*"));
It will show me records for Bob Johnson, Bob James and Bob Jones....
I am trying to duplicate this with a DoCmd.OpenForm statement... Replacing the static name with a field variable...
Code:
Dim whereClause As String
whereClause = "[First Name]" & " " & "[Last Name] Like " '*" & volunteerName & "'*""
DoCmd.OpenForm "Volunteers", acNormal, , whereClause, , , False
I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder
Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress.
viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour.
Try that and you'll also see the SQL I am trying to execute.
The CODE is as follows:
SQLtemp = "UPDATE 'Brukere' SET"
SQLtemp = SQLtemp & " 'navn' = '" & request("Navn") & "', "
SQLtemp = SQLtemp & " 'epst' = '" & request("Epst") & "', "
SQLtemp = SQLtemp & " 'Pass' = '" & request("Pass") & "', "
SQLtemp = SQLtemp & " 'Firma' = '" & request("Firma") & "', "
SQLtemp = SQLtemp & " 'BAdresse' = '" & request("BAdresse") & "', "
SQLtemp = SQLtemp & " 'BPostAdr' = '" & request("BPostAdr") & "', "
SQLtemp = SQLtemp & " 'PAdresse' = '" & request("PAdresse") & "', "
SQLtemp = SQLtemp & " 'PPostAdr' = '" & request("PPostAdr") & "', "
SQLtemp = SQLtemp & "WHERE 'Bnavn' = '" & request("Bnavn") & "'"
Response.Write(SQLtemp)
Response.End()
conn.Execute(SQLtemp)
rs.Update[/COLOR]
The finished SQL statement looks like this:
UPDATE 'Brukere' SET 'navn' = 'Alf Byman', 'epst' = 'alf@baccara.no', 'Pass' = 'alfen', 'Firma' = '', 'BAdresse' = '', 'BPostAdr' = '', 'PAdresse' = 'sdfg', 'PPostAdr' = '', WHERE 'Bnavn' = 'alfen'
I have tried to user single quotes, doubble quotes, brackets etc. nothing works.
The code I use for connection is as follows:
<!--#include file="../adovbs.inc"-->
<%
dim conn, rs, SQLtemp
' DSNless connection to Access Database
set conn = server.CreateObject ("ADODB.Connection")
rs="DRIVER={Microsoft Access Driver (*.mdb)}; "
rs=rs & "PWD=uralfjellet; DBQ=" & server.mappath("../../../../db/kunder.mdb")
conn.Open rs
I'll be very HAPPY for some expert help on this.
How do I let users choose the way they want to sort their reports through a form dynamically?
View 1 Replies View RelatedI have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
View 3 Replies View RelatedHello
Any hellp would be appericated.
I have a pass thorugh query that connets to a Oracle database with a where clause with a between start date and end date.
I need the start date and End Date to come from a form within MS Access.
Is this possible? if so how do I code the where clause to get the start and end date parameters from the form?
I need to append data from Table 2 into Table 1 where the Name and RefID fields in Table 2 have different values to the same fields in Table 1.
Does this make sense?
Ok, more clarification. I have a load of employees in a table (Table 1) They have the fields UniqueID, RefID, and Name. All links go through UniqueID, the name field has duplicates, and the RefID is the internal employee number and can be null where the 'employee' is a contractor.
Now, I have been handed an updated copy of that table (Table 2) in a spreadsheet. I need to load in all the records that are new in the spreadsheet to Table 1. The awkward part is that there is no uniqueID in Table 2. I cannot delete and reload all because i need to retain the original UniqueIDs in table 1 as they have linked data based on those IDs.
Can someone tell me how I can identify the new records in table 2 and then upload them to table 1.
Cheers guys and girls
Morning all,
Am trying to create a really simple form where the user can select one of five fields, an operator (just =, Not, <, and >) and the values are pulled from whatever field is chosen.
I have the form down and can populate the values list based on the field however now i need to use the values in these boxes to create a WHERE clause.
I'm really new to SQL and have only done a couple of online tutorials but i think im on the right lines?
DoCmd.RunSQL "SELECT * FROM qryLinkMasterPrimary WHERE " + cboFields.Value + " " + cboOperator.Value + " " + cboValues.Value
Is this even slightly close? I've tried searching the internet and everything i find is waaaaay too complicated for me to get my head round.
Any help is greatly appreciated
Mike Harkess
I have a TRANSFORMED query:
Code:
TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);
This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.
All those queries calculate data for all departments.
In the form, I have placed a combobox.
What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.
Code:
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats
However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.
Where do I place the vba statement? Is it under combobox AfterUpdate event?
I am planning to use this: (As I never done it before)
[URL] ....
Modifying SQL On-The-Fly section
I have a cool little form for the use of the organization's treasurer wherein she enters checks and deposits. I have a query that generates a running sum, so each line in the accounting report based on the table has the account total as of that date. The query behind the report is exactly what I want and is in the correct order. For example, the four transactions for 9/15/2014 are in the order entered, and the line total is correct. The report insists on listing the 4 in the example in some other order with or without OrderBy specified. With code from someone, perhaps on this forum, I added the following to the OnPage event:
Code:
Private Sub Report_Page()
Const TWIPSPERINCH = 1440
' Offset from right edge of Control where our
' Vertical Line will start - adjust as you desire.
' Expressed in Twips
Dim intLineMargin As Integer
[code]....
The result is a beautiful report (albeit in the wrong order) with vertical lines all the way to the bottom of the page.If I remove the OnPage code, the report is still in the wrong order, so obviously that wasn't the problem. OK, I think it is corrupted. I open a new db, import all into it. Still in wrong order. So next, I begin to recreate the report from scratch. Hooray! Right order! But I still want the vertical lines between the columns. So now, I copy the OnPage code and paste it into the new report's OnPage code. I get error message: "Compile error: User-defined type not defined" with the line "Dim MIPSstr As udtPrtMips_str" highlighted. Ok, I delete all of it and reenter it line for line manually, leaving out the comments. Get the same error message. After trying to research it, the only suggestions were to add Activex reference which can't be the problem, because the other version of the report doesn't throw an error and both are in the same db.
I have attached a clip of the report and a clip of the underlying query. As you can see in the first capture.png, the check numbers are in the wrong order and the amounts in the Total line don't sync with the total above adjusted by the amount on that line.
I understand I cannot easily run a SELECT * WHERE ... query in VBA ?
I want to run a simple Select query but use variables from the Form vba for the WHERE clause selections.
I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters.
Code:
reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "' And ReworkTimeOut = Date()"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut = Date()"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut = Date()"
strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere
How to make a combo box with the months listed so they can pull this report by the month selected but is a side tangent.
Need help pls
I'm designing a report that should be sorted by field A, B, C, D, or a combination of BD or CD.
Problem is (it seems) the report ignores the sort order and lists the results in ascending order of the autonumber field - 1,2,3,4,5 etc.
The reports run off queries and the queries work fine - they sort the fields in the proper order.
Is the autonumbering somehow interfering with the sort order in the report?
HELP!!!
I have a report that generates the position of certain items. When I produce this report it doesn't put the data in order. All associated data with that position is correct, it is just not in numerical order. The attached screenshot shows the issue I am having. In the position column it should read 1,2,3,4 but in some cases the positions are not in order.
View 2 Replies View Related