Is it possible to have a dynamic whereclause to a query?
I.e. one that is generated by a Function?
E.g.
Code:
SELECT a, b, c
FROM MyTable
WHERE GetWhere()
GetWhere will then return a string like 'a > 3 AND b < 3'
I have tested this scenario and it is not working, so maybe there is some work around?
All of a sudden none of my queries work properly- all the criteria and calcs i had in them have been deleted?!
Basic codes do not work any more e.g >now() ;between 1 and 30 etc
If i try and write a new query using the same expressions and code they just don't like it - YET they were working yesterday - very strange
Can anyone help??
Hi Guys
my database is essentially a questionnaire of around 200 questions. I'm trying to add a 'light' version to it.
I've added a 'include in light verion' tick-box to all the questions and a 'light analysis' tick box on the registration page.
The questions are all genertated via queries pulling out the appropriate ones as required. What I'm tying to do is add to these queries so that if the 'perfomr light analysis' tick box is ticked only those questions that have been indicated as light are included, and if it's not all teh questions are included.
I can do either-or but not both dynamically
Can anybody help?
Thanks
scube
I have a form with 2 cbo. I want query to show all data if nothing has been selected, or show specific data to whatever has been selected in cbo1 AND/OR cbo2.My query was working ok for couple of days and then stopped. I don't know why, as no changes has been made.
if both cbo are blank then it works OR if both cbo have a selected value then it works, ok. But it doesn't if one of the cbo has a value and the other one hasn't.
Code:
WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea
AND dShiftFK=Forms!F_MainMenu.cboStatsShift)
OR (Forms!F_MainMenu!cboStatsArea IS NULL
AND Forms!F_MainMenu!cboStatsShift IS NULL)
I have several queries (crosstabs) which are eventually combined together to form the data for a single report. If I want to date select for this report I need to add a WHERE clause to every query via VBA. This isn't difficult but it set me wondering if there was any way to have one dummy query containing the necessary WHERE statement, and this somehow gets appended to all the other queries, i.e. to filter on date I only need a change in one place.
View 7 Replies View RelatedI am inserting a new record into a 'master' table. Primary key is generated using a sequence. Now using that newly inserted primary key value, I need to insert a new record in child table. These 2 insert queries need to run at the time of deployment. how shall I store that primary key in a variable so that I can use it immediately in next insert statement?Can I use a returning into clause?
View 2 Replies View RelatedI have a combo box on a form where I allow the user to pick an office. The primary key is the bound column which I then want to use in a stored query to limit the query results to the specific office selected. I can easily do this with the docmd.runSQL in the module, but I'd like to use a stored query instead. My exact problem is I can't get the syntax correct in the Where clause in the query design. I use the build feature and get the following: [Forms]![frmReports]![cboOffice] which doesn't work. If I manually put the key value in the Where clause, it works fine. tell me the correct syntax for the Where clause.
View 4 Replies View RelatedFrom 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
Is there anyway to search each row within one column for a value ? The "IN" clause appears not to work for multiple values in a row.
SELECT Table_Payee_List.CategoryIDs, Table_Payee_List.PayeeSelectBox INTO GOTIT
FROM Table_Payee_List
WHERE (((Table_Payee_List.CategoryIDs) In ("25")));
Data (two columns with 5 rows) are represented below:
Payee CategoryIDs
Column Column
John Doe 21, 27, 32, 34, 44 - Row 1
Jane Doe 20, 25, 28, 42, 44 - Row 3
Paul Doe 19, 25, 28, 42, 44 - Row 3
James Doe 56, 29, 31, 62, 54 - Row 4
Amy Doe 24, 25, 29, 42, 74 - Row 5
I need to change the below to a where clause to fit inside a union query that is just where clauses.
tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit
FROM tblNEWNONTODATA
GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision
HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));
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 can create a select query with a combo box in it by changing the property of one of the fields - making the display control to combo box and adding the values. No problem there.
What I want to do is to be able to create a query dynamically from vba using a CreateQueryDef statement.
The problem is that you can only pass an SQL string to CreateQueryDef, and the combo box values are not part of the SQL code, but are saved with the query (presumably the same way column layout gets saved).
Is there a way to create a query with VBA and include non-SQL layout criteria so I can have combo boxes?
I'm using Access 2002.
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#;
I have a SQL query to gather data from a number of tables (balances, accounts, currencies)
Quote:
SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD
FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID])
WHERE BalanceDate = #12/10/2013#
How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)
Along the lines of :
Quote:
WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000
I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead
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
I have a table with 2 fields:
CODEDATE (text)
DATE1 (date)(dd/mm/yyyy)
RECORDS SAMPLE:
Code:
-------------------------------------
CODEDATE DATE1
-------------------------------------
TT-10/10/2013 01/01/2012
ASDFDFDF 01/02/2013
RR-18/06/2012 05/08/2012
------------------------------------
I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code:
SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound
FROM MyTable
WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1])
Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies View RelatedWinXPPro 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
I get the following error when trying to compile code int an MDE:
This error is usually associated with compiling a large database into an MDE file. Due to the method used to compile the database, a considerable number of TableID references are created for each table. The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
There are only about 10 linked tables to SQL server, a decent amount of code but nothing that bad in the Access FE.
Any ideas on how to solve this?
As I understand it, the code will run faster when compiled?
I just recieved a access project from a client, apparantly it works fine on their machine. I FTPed it to my machine. Both are winXP.
Most of the app works fine except when I try to bring up one form. I get this error.
Compile Error
Can't find Project or Library.
This is written in access 2000 and that is what I am running it on. I've checked windows update, my computer is up to date. That includes Office update.
I tried running this on a second box that I have, a win2000 server I get the same error.
It apprears something is not getting transfered, any Ideas?????
Thanks for the help.:)
I need some help guys... i have created a code ... in which the user is able to create users passwords and etc....
The problem seems to be with one line of the code... which is
Dim db As DAO.Database
the error says User defined type not defined....its a compile error...
Can anyone help me to solve this???
Thanx guyz
I tried to declare a variable to be type Database and when I compile the program it gives me an error Compile error "Use Defined type not defined"
this is the code that I have written:
Public Sub NewX()
Dim mydb As database ******** here is were is giving me the error, I tried to declare this on several places but it does the same error**********
Dim myrecord As Recordset
Set mydb = CurrentDb
Set myrecord = mydb.openrecordset("log", dbopendynaset)
the rest of the code continues.......
End Sub
Any help will be appreciated, I am trying to save the information on the database by clicking a coomand button .
Thanks.
Zurdo
I used "Option Explicit" at the beginning of a module. When I compiled the code, I received a Compile error that indicated the Set dbs = CurrentDb is not defined.
How do I define the "dbs =" variable???
Any help is appreciated.
Thanks
My Access 2000, created the dummy's way, has been perfectly happy with its query expressions. But when I create a module calling up these queries, I get Compile Error on many perfectly legal expressions such as Left([pfn],1).
I've tried repairng the database but to no avail.
What is going on, please?
Btw, DBicken, it's when I include your text concantenation code (Posting 6/21/06: "A whole lotta text/stitching") but I don't think it's your fault: it's just exposing it. I can't try your suggestion until I solve this problem.
hello to the experts
is there any way or method to compile an access .mdb file to .exe
and don't be the need to open it with the access programm?
(eg. to a computer wirh no access installed)
Does anyone happen to know to compile the different results obtained by passing a query a series of parameters?Say i have a query that returns the total work hours for an employee for a month, and the employee's name is passed to that query as a parameter.And i want to compile the data for all the employees (in a report or query) where the list of employee names to be passed are stored in another query.Just using Access 2000 with some VBA.Thanks for any help :)
View 1 Replies View Related