Queries Vs Code
Jan 30, 2008If one has a query and vb routine that does the same thing, in an overall Access environment, is there any advantage to using one over the other ?
Just curious.
ADVERTISEMENT
What Is The Supporting Code With Queries?
Feb 19, 2008As an example, I have 2 separate queries:
SELECT Orders.QtyAdd
FROM Orders
WHERE Orders.ProdID = 1;
SELECT Output.QtySub
FROM Output
WHERE Output.ProdID = 1;
What code do I use to subtract these 2 queries?
And also, where it says .ProdID = 1; what is the variable so it picks the ProdID from a basic single row form being shown? I guess it doesn't follow the Table.Field pattern?
Thanks, hope that isn't confusing.
Code Help In Selecting Queries.
Dec 14, 2004I am needing help with how to set up the code to select different queries based on different conditions.
The database is to be set up so that I can pull studies at different time points based on if they are under long term, accelerated or stressed conditions. When this condition is selected there are only certain time points which are to be run,
example.....
Long term: 3,6,9,12,24,36,48 and 60 months.
Accelerated: 3,6,9 and 12 months.
As you can see I have the dates calculated for all of the weeks and months that the sample could be pulled at, Inventory Transactions Table. And I am able to generate throught the Pull Dates Form a list of only the records to be pulled under Long Term conditions. I would like to have the form set up so that when I choose the Accelerated condition that it would pull from another Query like the Pull Dates Long Term, but only have the Accelerated time points listed.
I am not sure if I need an If/Then statement and if so how do I set this up.
Functions Work In Code But Not Queries
Feb 14, 2007I have a problem where I can create queries in code using functions such as Left() and they will work fine on my clients machines with a complied MDE file but if I try to use the same function in a saved querie they get an error: "Function is not available in expressions in query expression..."
The Queries work fine on my machine but not on those using Access Runtime. From my research it appears to be a problem with them not having the correct Reference on their machine. If that is true then which Reference do they need and is there away of installing that Reference by code?
Thanks for your help....
Queries :: SQL Code For Displaying Max Date
Apr 13, 2015I need displaying the max date sql code below;
PHP Code:
SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack,
TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack
FROM TblDietPlan
WHERE (((TblDietPlan.ClientID) Is Not Null) AND ((TblDietPlan.MealDate)=Max()))
ORDER BY TblDietPlan.MealDate DESC;
Queries :: Using Recordset In VBA Code With Yes / No Datatype
Apr 18, 2013I'm a having a problem with a Yes/No datatype. Fundamentally, my code looks like this:
strSQL = "SELECT * FROM tblAddresses " & _
" WHERE YPID = " & Me.Parent.IDNUMBER & " AND CurrentAddress = yes"
Set rs = db.OpenRecordset(strSQL)
Now this statement returns 3 records even though only 1 Current Address exists.
false also returns 3 records
true also returns 3 records
no also returns 3 records
-1 also returns 3 records
0 also returns 3 records
1 RETURNS 0 records!!
If I look at the table in Access, I only have 1 CurrentAddress record for my IDNumber.
Queries :: Default Value Not Populating With VBA Code
Mar 11, 2014Why the code is showing error.
Private Sub Ref_Click()
Me.Ref.DefaultValue = Date
End Sub
I want that when I click the Ref field in the form the date field in the form get populated with todays date
Queries :: SQL Code - Recordset Is Not Updateable?
Oct 4, 2013I'm fairly new to SQL and I'm trying to input data into this form I've created but it tells me that "this Recordset is not updateable". Here's my SQL code, I'm not certain this is the problem though.
Code:
SELECT [Annual Instructor Report County Summary].*, [Camps, SS Events, Events, Total Members].*,
[Archery Members].[numArchClubs]+[Archery Members].[numArchNon4H]+[Archery Members].[numArchCamp]+
[Archery Members].[numArchSSEvent]+[Archery Members].[numArchEvent] AS totArchMem, [Rifle Members].
[Code] ....
Queries :: Recordset Shows Nothing In The Code?
Aug 21, 2013i have a combo box ,and when i should select "all",this button after update code should show me all the column of table test ,but looks like for no reason the record set shows nothing and its not working.
Code:
Private Sub cboTaskListName_AfterUpdate()
'On Error GoTo cboTaskListName_AfterUpdate_Err
Me.Refresh
Dim db As DAO.Database
Dim SQL As String
Dim rs As DAO.Recordset
If Me.cboTaskListName = "111111" Then
Set db = CurrentDb()
SQL = "SELECT no1 from test"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
End If
End Sub
Queries :: SQL Code To Compute Moving Average
Dec 3, 2013I have a query that pulls data from a table called Data. I'd like to add a column to the query that will be the moving average -- For example, for a Month Beginning Date of 12/2013, it would average the SumOfCount variable for 10/2013, 11/2013, and 12/2013.
This is the sql code for my query without the moving average:
SELECT Data.Behavior, Sum(Data.Count) AS SumOfCount, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
FROM Data INNER JOIN [First Name + Last Name] ON Data.[Full Name] = [First Name + Last Name].[Full Name]
GROUP BY Data.Behavior, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
HAVING (((Data.Behavior)="PRNs") AND ((Data.[Full Name])=[forms]![report parameters]![fullname]));
How to insert that column into my code?
Queries :: Referring To Select Query In SQL Code
Jun 5, 2014I have a update statement as follows
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True
Access is telling me it can't find the record and from what I have tested it seems to be the Update line, not the where line. I am basing the set portion as equals a query - could this be causing the problem? Or can code be based on a query?
Queries :: Select Query Inside VBA Code?
May 21, 2013I'm trying to create a query inside VBA code.
the problem is that my query is a select query and therefore I can't use RunSQL
I tried to work around it withbut had no luck... this is the code:
strSql = "SELECT '" & Me.number & "' ,Karin.[subject] " & "From Karin " & "WHERE '" & Me.number & "'" = done
Queries :: Criteria Code In Query Results
Oct 21, 2013I have a very simple cascading combo box form with three combo boxes.
First - Customer - pulled from tbl1
Second - Item Description - pulled from tbl1, based on Customer
Third - Quarter - not pulled from any table, just 1-4 numbers I manually inputted
The cascading part works perfectly fine, but the problem comes with the query button to get the full report I need. If someone selects quarter 1, I want the query to pull up only the quarter 1 column in the table for that customer and description, not quarter 2, 3, 4 columns too (those should just be invisible).
(This part works fine) In the query under Criteria, I have placed:
Customer -- [Forms]![frmSummary]![CBCustomer]
Item Description -- [Forms]![frmSummary]![CBDescription]
then I have columns Quarter 1 -4 and don't know what code makes the query show only the correct column based on the quarter combo box.
I'm assuming it might look something like..vvv...but I know this is wrong.
Quarter 1 -- [Forms]![frmSummary]![CBQuarter]=1
Queries :: Delete Query Where Code And Client Are Equal
Aug 8, 2013I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid
Here is the sql
DELETE tblinclude.ClientID
FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID)
WHERE (((tblinclude.ClientID)=1));
I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.
Queries :: SQL Code Disappears From Query When Sending EMail
Jan 15, 2015I have a simple SQL query that is e-mailed when there are >0 records. Fairly consistently, the SQL code in the query is wiped out and the query fails. It happens consistently to this query, even though I have many similar queries. I am running Access 2013 in Windows 7
SELECT AR.[Inv#], [History Header].[PO#], AR.[Customer Name],
AR.Date, AR.Mdse, AR.Freight, AR.Amount, AR.[Due Date]
FROM (AR INNER JOIN [History Header] ON AR.[Inv#] = [History Header].[Inv#(number)])
LEFT JOIN [Advanced Engineering Payment Reminder Sic]
[Code] ....
Queries :: Graphs - How To Change Chart Type Using VBA Code
Apr 28, 2014I am trying to change the chart type on a MSGraph control that I have added on my form. However, there is no graph.ChartType
OLE Class: Microsoft Graph Chart
Class: MSGraph.Chart.8
Queries :: Telephone Number Search Area Code (STD)
Oct 27, 2013What I am trying to do is i have a list of area codes connected to other data
What I want to do is do a search on a telephone number and this will check against the area codes and diaplay the required info
Example
Area info1 info2
01234 ab cd
012345 ar mc
01235 fg tr
So if I put a number like 01234567890 in the search box the result it will show is 012345 ar mc as this si the closet match to the telephone number.
I did it in excel with
=INDEX(whatwouldbedisplayed,MATCH(searchinfo,rowof data,1))
Im not to sure if this can be done as a query so would it have to be a macro?
Queries :: SQL Code To Define Field Format - Mismatch
Dec 1, 2014I have created an union query to pull together the same data from 2 ODBC tables (seperate countries).As there is a clash in the client number I have added a "N" prefix to differentiate NZ from Australia, as below:
The problem I now have is that I get type mismatch when linking to another table. How could I change the SQL below to dictate the field format to number (providing it will accept the "N" prefix) or all to text?
SELECT dbo_ClientMaster.ClientNumber, dbo_ClientMaster.Name, dbo_ClientMaster.AddressLine1, dbo_ClientMaster.AddressLine2, dbo_ClientMaster.AddressLine3, dbo_ClientMaster.AddressLine4, dbo_ClientMaster.TradeCode, dbo_ClientMaster.ReviewLimit, dbo_ClientMaster.ClientStartDate, dbo_ClientMaster.TypeOfTrade, dbo_ClientMaster.NextReviewDate, dbo_ClientMaster.LastReviewDate, dbo_ClientMaster.TerminationDate, dbo_ClientMaster.TerminationReason, dbo_ClientMaster.BankSortCode, dbo_ClientMaster.BankAccountNumber
[code]....
Queries :: Order To Get A Sum Of Volume With Only One SKU Code Listed In Column 1
Jun 21, 2013I created a query, and in field one i have a list of SKU codes (of which many are repeats), and in field two I have their corresponding volumes. In Fields 3-5 I have some various algebraic calcuations.
Now, in order to get a sum of the volume with only one SKU code listed in column 1, I use a group by on the SKU code, and a sum for the volume. When these are the only fields in the query, this works great. But when I add in the other calculated fields, the run query returns to showing each SKU code multiple times, and shows the volume and the calculations correctly. When I change the other calculated fields to Sum, then I get the aforementioned overflow error and no output at all.
Is there a way to both set it so that the output is only showing one SKU code, the total volume for that SKU, and the weighted average output of the other calculations in one query?
Queries :: Return Word Unknown For Any Code That Doesn't Have A Match
Aug 20, 2013I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:
Code:
SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];
Table examples:
Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX
[code]...
Queries :: Query Or Any Code That Can Pull Back End Table Stats?
Dec 27, 2013I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
View 5 Replies View RelatedQueries :: How To Find MS Access Version Number In Java Code
Aug 5, 2013Is there a way to find version number of MS Access using java code.
View 3 Replies View RelatedQueries :: Unable To Import Tables From FoxPro Database Using VBA Code
Apr 22, 2013I'm trying to import a few tables from a FoxPro database into an Access Database using VBA code. I know I have done it before and I remember struggling with the format last time.
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=Hiremate;SourceDB=data-wwappshmfox32datahiremate.dbc;SourceType=DBC;E xclusive=No;BackgroundFetch=Yes;Collate=Machine;Nu ll=Yes;Deleted=Yes", acTable, "hires"
Modules & VBA :: Code To Delete Table And Then Run 4 Append Queries To Refresh Data
Jun 11, 2014I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
Please Review This Code, (simple Code) New With Codes
Feb 16, 2006Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
Using Code To Unprotect And Protect Viewing Code
Jan 14, 2007I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Thanks
SHADOW