Im looking to have a query that selects the "NAME" from a table "tblPeople" where the NAME field begins with "A". Ive tried using WHERE tblPeople.NAME Like "A" with no luck.
Can anyone help me out, im sure im missing something really simple.
I 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?
I'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
it has a listbox, wich RowSource is a query with 5 fields, BoundColumn is field#1.I have a comboBox with values of field#1 to have an 'AfterUpdate' Event, that highlights the row in the listBox that has the value of the comboBox in in field#1.
View 1 Replies View RelatedI have a database with lists clients across the UK. I have now been asked to provide options where users can select clients grouped by geopraphical area e.g say clients in Scotland.
I can of course do this but having numerous identical forms where the source queries have different parameters depending on the regions required.
The only problem with this is that I would need numerous forms and queries. Additionally, there are options on the form to navigate to linked forms which would all need to be unique.
What I would like to have options on my main (Switchboard Type) Introduction Form to select the region. The code on the relevent command button would include the parameter. I would therefore not require the additional forms.
The open form codes includes:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClients"
DoCmd.OpenForm stDocName, , , stLinkCriteria
I feel i need something after "frmClients" such as qryClients.ClientArea = "Not Scotland". Various attempts to incorporate something has created errors.
Any quidance on code would be appreciated.
Thank you...Paul
I have a multi-select list box for selecting which faculty members apply to a project. The faculty table and project table are linked in a many-to-many relationship. I have the following code which should create entries in the link table:
Private Sub Command5_Click()
Dim varItm As Variant
rs.Open "tblFacultyLink", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each varItm In lstFaculty.ItemsSelected
rs.AddNew
rs!FacultyID = Me.lstFaculty
rs!EntryID = Me.EntryID
rs.Update
Next varItm
rs.Close
Set rs = Nothing
End Sub
It successfully creates new records and enters the EntryID and LinkID (autonumber). However, FacultyID is always left blank. lstFaculty is the unbound list box which has three columns from the faculty table and is bound to FacultyID. Any ideas on why FacultyID isn't created in the link table (I get no error messages)?
Also, any ideas on how to prevent duplicate links being created every time the button is pushed? I was planning on having it first run a delete query for that EntryID in the link table so that it replaces the old links and any that have now been unselected are no longer linked. Comments on that idea?
Thanks again to everyone on the forums for your help.
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri
Masonry NSW');'
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
First query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
Any help greatly appreciated.
I have the following query:
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper.
Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable
Now when you want to execute the query you get an errormessage.
Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
Ok, hope you can help me with this one.
I have a select query that pulls data from one table that includes the following data
MOS Grade Required Authorized
I have a separate count query that pulls data from another table and counts the following data
MOS Grade Assigned
How do I combine these 2 queries so the results I get will be
MOS Grade Required Authorized Assigned
For instance:
Select Query Says
MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3
Count Query Says
MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2
I want the results to read
MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2
Hope you can help.
I have a crosstab query built on a select query.It works perfect. However, when I try to use the "Between [BegDate] And [EndDate]" it tells me access engine can not recognize it.If I use this in the actual select query it works fine. But when I try to use the crosstab query it gives me the above error.
Also, I'm using two undbound textboxes on a form for [BegDate] and [EndDate]. I have the syntax correct in my select query and it works fine. But it won't work with the crosstab query.
I have this code from a Query that works.
SELECT Contacts.*,Contacts.Address1
FROM Contacts
WHERE (((Contacts.Address1) like "*" * (Enter 1st Line of Address here) & "*") AND ((Contacts.Site) = "Miscellaneous"))
ORDER BY Contacts.Site;
I want to replace the data "Miscellaneous" with a Public variable strSite.
If you can help I would be very happy and of course grateful.
Thanks
Ted :confused:
I have been asked to help with a database that someone is writing.
He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.
What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.
If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.
My question to the forum is the following:
With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?
I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.
Any and all help with this question really is appreciated.
Thank you
Bev
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies View RelatedI wondered if someone could help
I am querying a table using a select query. The data I am pulling from the table is in format:
Period Year Value
0 2006 1000
1 2006 100000
2 2006 500
3 2006 5000
4 2006 50000
5 2006 200000
In my query, I wish to seperate:
Period 0 + value
from
Periods 1-5 which should be cumulative.
Thanks
Paul
Hi,
I have tried a fee different methods but none of which are giving me exactly what i need, i was hoping some one out there might be able to suggest a good approach.
I have the following data.
Source
ID , Product , Price , Count
1 , F2 , 10 , 6
1 , F3 , 20 , 7
1 , F4 , 30 , 5
2 , F2 , 10 , 6
2 , F3 , 20 , 5
3 , F4 , 30 , 5
From this i want to be able to get the rows per ID with the highest price (count is inrelevant i am just indicating that there are other fields in the table)
Result
ID , Product , Price , Count
1 , F4 , 30 , 5
2 , F3 , 20 , 5
3 , F4 , 30 , 5
I have tried various combinations of the Max command and it works fine if i just select the ID & Price but i need it to display the product related to that highest price.
Anyone got any suggestions for me, they will be greatly received.
Many thanks
Tim
Hello,
I am trying to run a select query which I can't get to work.
What I want to do is select a purchase order number from table1 then (possibly using a subquery?) do another select where it will return the most recent version number (from table2) for the purchase order number returned in the first select statement...
Does anyone know how I could do this?
Thanks :)
Hi
I have a simple select query that pulls data from a table in a sql db. the query shows all records for "agent1". if agent1 has made numerous entrys it shows all of them
how can i get it to just show the total of all entries?
i've tried selecting the sum option in query design but it says data type mismatch in criteria expression
Any ideas?
Thanks
Ben
Ok I'm having a problem running a query, below is description:
TableA has columns
RecievedDate
ResultsID
TableB has columns
SentDate
SentID
TableC has columns
ResultsID
Sent ID
I want to have a result of all values in Table A that have a value of Recieved Date is less than SentDate in TableB, I can link up through table C but I'm not sure on what way would be the best to do this?
I've created a database that we use to manage our sales orders. Two of the tables are 'Orders' and 'orderdetails'. The order table stores the order header info, such as customer name, account number, etc and the orderdetails table stores the products, qnty's, price, etc. They are linked with a one-to-many relationship using the orderID field.
I want to run a select query to list the orders1111 that include productID = 57 (thats the easy part) but I also need to list of the other order lines on the selected sales order.
I am trying to setup a query where all the data related to one item is in line but all I get are sets of blank spaces and shows the main item three different rows with one item that is related to it one three different rows
So what I am trying to do is have a column for the part number, front, rear, side and for the part number all the related data will show in one row instead of one 3 different rows with one piece of data in each row.
tblPartNumber
qryFront
qryRear
qrySide
What I have now
PartNumber______Front______Rear_______Side
12345___________asdf
12345_____________________asdfs
12345______________________________asdfsaf
What I want
PartNumber________Front________Rear_____Side
12345____________asdf_________asdfs____asdsaf
Sorry if this is a basic question. I'm new to Access and Visual Basic. I'm trying to get a select query to work sort of as a conditional.
My code is
PHP Code:If ("SELECT TOP 1 RF_Connectors.ppl FROM RF_Connectors WHERE (((RF_Connectors.my_part_num)=Forms![Supplier Part Number]!my_part_num));" = "Yes") Then
I can't however seem to get it to work. I also set up a query using the design wizard that is the exact same, and when I run that I will get the correct result displayed.
Any idea why this isn't working?
Also say I set up:
strppl = ("SELECT TOP 1 RF_Connectors.ppl FROM RF_Connectors WHERE (((RF_Connectors.my_part_num)=Forms![Supplier Part Number]!my_part_num));"
Would that fill strppl with whatever the field ppl contains?
Thank you,
Kyle
im new to microsoft access and i need help with a easy problem.
i made a table with Salary information and stuff.
and i made a select query, now i need an expersion to calulate a new Salary from the old one with a 5% increase.
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?