Query To Use Values In Form As WHERE Clause
Sep 28, 2007
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
View Replies
ADVERTISEMENT
May 18, 2015
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.
View 14 Replies
View Related
Sep 28, 2005
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.
View 1 Replies
View Related
Jul 19, 2006
Hello
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?
View 3 Replies
View Related
Oct 13, 2006
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
View 2 Replies
View Related
Dec 1, 2014
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
View 5 Replies
View Related
Jul 21, 2015
I would like to open a form to a specific date that is taken from another form. Date field format is short date and looks like yyyy.mm.dd
I have figured it out that it only works if date format is yyyy/mm/dd
I know format function can do this, but i do not know how to write the code with correct syntax.
For example i have tried:
DoCmd.OpenForm "TreatmentsTB1", , , "DOV= #"& Format(2015.01.11,"yyyy/mm/dd")&"#"
it does not work.
View 3 Replies
View Related
May 30, 2014
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.
View 3 Replies
View Related
Nov 7, 2012
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 5 Replies
View Related
Jun 16, 2015
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"
intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)
The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"
View 10 Replies
View Related
Jan 17, 2014
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
View 1 Replies
View Related
Nov 18, 2014
how I can achieve this in Access
I have a table created in Access- "Master"
FileName Sortorder
ABC_..........4
CDE_..........2
EFG.ss1.......1
GHI.srs........3
I have a Query created in Access whose output is
FileName RowCount Exception RunDate
ABC_20141117.....10...........5........11/17/2014
CDE_11172014......23.........10.......11/17/2014
EFG.ss1................55..........0........11/17/2014
GHI.srs.................15..........5........11/17/2014
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
Required Output:
FileName RowCount Exception RunDate SortOrder
EFG.ss1...............55............0.......11/17/2014...........1
CDE_11172014.....23...........10......11/17/2014......... 2
GHI.srs................15............5.......11/17/2014..........3
ABC_20141117.......10...........5......11/17/2014..........4
View 10 Replies
View Related
Aug 25, 2014
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.
View 3 Replies
View Related
Apr 6, 2008
Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?
I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins
Running this code in VBA, or pasting the SQL directly in a query results in the same error.
Sub test()
sTableName = "Test"
sSQL = "CREATE TABLE " & sTableName & "_Config (" _
& "[idConfig] Int Primary Key," _
& "[Config] Memo," _
& "[Instrument] int," _
& "[Serial_No] Text(25)," _
& "[Firmware] int," _
& "[Orientation] int," _
& "[Sensors] int," _
& "[Sensor_Size] float," _
& "[Sensor_1_Distance] float)"
DoCmd.RunSQL sSQL
sSQL = "CREATE TABLE " & sTableName & "_Leader (" _
& "[idLeader] Int Primary Key," _
& "[idGroup] int," _
& "[idFile] int," _
& "[idConfig] int," _
& "[DateTime] DateTime," _
& "[Heading] float, [Pitch] float, [Roll] float," _
& "[Pressure] float, [Depth_BSL] float, [Height_ASB] float,"
_
& "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
& "[ASM_Bed_Level] float," _
& "CONSTRAINT [FK_Test_Leader_idConfig] FOREIGN KEY ([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
& ")"
DoCmd.RunSQL sSQL
End Sub
The first table is created just fine.
And if I leave the constraint clause out, the second table is created also.
If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.
I have looked up the clause in Access help and even using their example, i get the same error.
Any help will surely be appreciated!
View 2 Replies
View Related
Oct 5, 2012
I have a listbox that is populated with data from a table. I would like to use one of the columns data as the where clause of a sql string that will populate another form, how do I get the selected items column data that I need into a variable? This is in Access 2010 vba.
View 9 Replies
View Related
Aug 3, 2006
I have a table of records, each of which has a date.
I want to create a form which has two boxes on it for the user to input 2 dates and then get Access to query the table of records and display only those which fall between the two dates specified.
I could do set this up using a parameter query but am not sure how to make the query pick the dates up from the form.
How can I do this?
Gary
View 14 Replies
View Related
May 14, 2007
Hi,
I need to build a query where upon form input a different table gets queried.
SELECT *
FROM ((Forms!DCL!PortDropDown))
WHERE ((Forms!DCL!DestinationList=(Reg_Dest.Destination) ));
The "FROM" is my problem. The "WHERE" is working. I am getting a "SYNTAX ERROR IN JOIN OPERATION" error message. Please help.
Thank you:confused:
View 6 Replies
View Related
May 2, 2013
I have a form - RunQuery And I have a query - Q2.
The form has text boxes for dates txtStart2, txtEnd2.
On button click I have a macro.
Query Name Q2
view Datasheet
datamode edit
The query will open
The query has a field Assigned which is Date/Time
In my query design view I have this as the criteria:
>[Forms]![RunQuery]![txtStart2] Or <[Forms]![RunQuery]![txtEnd2]
I also have filter on load set to yes.
However the filter does not seem to work.what is missing or wrong?
View 6 Replies
View Related
May 15, 2005
i am trying to create an append query that gets 2 values from one table and a 3rd value from a form.
It gets the values from the table fine but always gives me a popup asking for the value from the form.
I have used the criteria build... and selected the loaded form and the field value that I am looking for. I have also made sure that there is a value in the field on the form.
is there a reason why the query will not grab the number in the forms field? or am I jsut not doing this right.
I have done this many times on a combo box and it works fine. It is jsut not working for a field that is typed in
Can anyone help me?
View 5 Replies
View Related
Nov 12, 2005
Hi,
I have a popup form called Labor that is working perfectly fine, what i mean is that it is getting populated with data from the query I built using a single table called PrimaryBid_Master. However I have had to add many fields to this table before invoking a query on it, now I'm running close to the 255 field max limitation.
So I have created a new table called Labor_Primary with all the fields that I need and took them out of the PrimaryBid_Master table freeing up about 50 fields.
The query I made combines 2 tables: table1=PrimaryBid_Master table2=Labor_Primary, they are linked by a common field called invoice#.
But now when I’m in the PrimaryBid_Master form and click the command button to go to the popup form Labor non of the values that were populating the popup form from the primaryBid_Master form work, they were working fine the only thing I changed was the popup forms record source to the new query I built.
Here is a print screen of the new query I built using 2 tables, and I also show the old query that works fine using 1 table. http://www.roofmart.net/query1.asp
Maybe you can see some thing I did wrong; do I need some type of filter to fetch the fields? When I open the new query there is no data/values in it.
Thanks--I appreciate any help.
View 1 Replies
View Related
Mar 21, 2006
I have a form with 4 different search fields used to query a table and limit results: Policy#, SSN, Credit_Amt, Debit_Amt. Any matching rows based on the search criteria are returned in a subform.
My problem is how to handle any fields where the user doesn't specify a search value.
In my query, I have the Criteria set to:
Field: Policy_No
Criteria: =[Forms]![form name]![Policy#]
Field: SSN
Criteria: =[Forms]![form name]![SSN]
and so on....
If I place my criteria on different OR lines, the query runs, but if a user specifies more than 1 criteria, I get all the data for one criteria(such as policy# matches) and all the data for the second criteria(such as all the records for a certain credit_amt, regardless of Policy #).
If I place my criteria on the same criteria line, effectively making it AND, I get no results because I assume the database is looking for any blank search field values.
So, how do I make the query an "AND" query, but actually get results by ignoring any NULL search fields? So if a user enters a Policy# and Credit_Amt, the results will be only matching rows by Policy# AND Credit_Amt, and doesn't look at SSN and Debit_Amt.
I've tried different iterations using IIF(Not IsNull(.... to no avail.
Thanks!
View 3 Replies
View Related
Mar 13, 2007
Hi i am having problem creating a query that will pull the price of a property into the price field on to a booking order form depending on the values of the start date and property number enter onto the same form.
i have a two tables property price and booking order.
Fields in the property price are property price no, property no, start date, end date and price.
Fields in the booking order table are booking no, property no, start date, end date, cost, no of days, total cost.
When in the booking order form i need the query to get the price from the property price table matching on the property no i've selected on the booking order form and also the start date i've entered on the booking order form needs to be between the start and end date fields in the property price table.
This is the query i have created but doesn't work
SELECT [Property Price].Price
FROM [booking order] INNER JOIN [Property Price] ON [booking order].[Property No] = [Property Price].[Property No]
WHERE ((([forms]![booking order subform]![start date]) Between [property price].[start date] And [property price].[end date]) AND (([Property Price].[Property No])=[forms]![booking order subform]![property no]));
Thanks for your help
View 2 Replies
View Related
Jun 28, 2005
Hi there. What I'm attempting to do is pass multiple values from a multiple selection list box as criteria for an Append Query. Is there any way to do this? The DB keeps track of Real Estate boards and the forms that they use, I would like to be able to select all the boards in a given state/province, but have the ability to deselect some within that province if I don't need them in the query (this is the criteria i'm trying to pass). I don't need to use a list box, any control that would allow me to pass multiple values would be great. Anyone able to help? Thanks very much.
View 1 Replies
View Related
Oct 20, 2005
I'm currently working on a database which provides copier and postage activity in my department, using Access. I'm trying to debug a query which is called by a form that accepts input for a particular month and year. Whenever the OK button is clicked, it gives a message "The Microsoft Jet database engine does not recognize 'Combination Query' as a valid field name or expression."
"Combination Query" is a union query which calls two intermediate queries, each of which in turn calls a base query. Only one, the query responsible for gathering copier activity, is a crosstab query.
The problem I believe lies in either or both of the base queries only when the form is used. When I use a regular parameter query, I receive no error messages.
Here is the SQL code I used for each:
Query 1: Copier Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;TRANSFORM Sum([big table].Copies) AS SumOfCopiesSELECT [big table].[Copy Code], [User codes].Alias, Sum([big table].Copies) AS [Total Of Copies]FROM [User codes] INNER JOIN [big table] ON [User codes].[Copy Code] = [big table].[Copy Code]WHERE (((DatePart("m",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big table].[Copy Code], [User codes].AliasPIVOT [big table].[Copier Location];
Query 2: Postage Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;SELECT [big Postage Activity].sAcctNum AS Account, Sum(Nz(cBaseRate*lNumPieces,0)+Nz((cRegisteredFee+ cCertifiedFee+cReturnRecFee+cReturnRecMerchFee+cSp ecDeliveryFee+cSpecHandlingFee+cRestrictedDelvFee+ cCallTagFee+cPODFee+cHazMatFee+cSatDeliveryFee+cAO DFee+cCourierPickupFee+cOversizeFee+cShipNotificat ionFee+cDelvConfirmFee+cSignatureConfirmFee+cPALFe e+cResidualShapeSurcharge)*lNumPieces,0)) AS TotalFROM [big Postage Activity]WHERE (((DatePart("m",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big Postage Activity].sAcctNum;
I edited this to make it look similar to a recommendation posted on another forum, but sometimes I would get the message "The Microsoft Jet database engine does not recognize '[Forms]![frmObtainMonthlyInfo]![cmbMonth]' as a valid field name or expression."
I also included PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long; as the first line in the intermediate tables, but I still receive the same error message.
I'm wondering if there was something I overlooked or if there was a way around this. I can post the other queries if you need those. Many thanks in advance for your help.
View 3 Replies
View Related
Oct 16, 2013
I have a form that has a dropbox list and I want the values of the form to change according to the value in the dropbox.For example:
When the dropbox contains the value A,
The form would display x=1, y=2, z=3.
When the dropbox contains the value B,
The form would display x=4, y=5, z=6.
I managed to create a query that opens the QUERY's results according to the value in the dropbox but I can't managed to run the query in the form, return the values according to the query and display them in the form.
I searched the internet and found many options including the Dcount command but I don't think it fits my case..I know I have to write something in the AfterUpdate field of the dropbox but I just can't figure out what it is...
View 8 Replies
View Related
Dec 17, 2013
I have a database and on the main table one of the columns is "Status". The values in that column can be either "Worked, Unworked, or Overdue".
I would like to be able to create a form so that the user can run a query and select which Status' they want to see. For example maybe they only want to see Unworked and Overdue so they can select those and run the query.
View 4 Replies
View Related
