The snipet below returns only a single record. I want all the records. I am not sure how to get the results from the query or the syntax.
This is what I want:
Select location from client_loc
where clientID = FORMS!job.ClientId
Thanks
---------------------------------
Private Sub ClientId_AfterUpdate()
GetValidLocations
End Sub
Private Sub GetValidLocations()
Dim varName As Variant ' declare a variable for the name
' Check if either combo box is null
If (IsNull(Me.ClientId)) Then
varName = ""
Else
' Look up the title
varName = DLookup("[Location]", "client_loc", "[clientID] = FORMS!job.ClientId")
End If
If (varName = "" Or IsNull(varName)) Then
Me.location = "(None found)"
' Otherwise, put name into the text box
Else
Me.location = varName
End If
Notes (1 to many relationship with People table, based off Number) ----- Number (lookup to People table) Note Date
I need to take all people of a certain type whose newest note is more than 6 months old. Here's what I have so far (it doesn't take into account the "more than 6 months old" part). Notice that I have the GROUP BY because of the MAX() in the outside SELECT statement: SELECT [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, Max([Note Info].[Eff Date]), [App Info].[App Type] FROM [App Info] INNER JOIN [Note Info] ON [App Info].[Soc Sec #] = [Note Info].[Soc Sec] WHERE [App Info].[App Type] Like 'Apprentice*' AND [Note Info].[Eff Date] in (SELECT [Note Info].[Eff Date] FROM [Note Info] WHERE [Note Info].[Eff Date] Is Not Null) GROUP BY [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, [App Info].[App Type];
Problem is, it's returning multiple notes for a given person.
Hi anyone! I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.
I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.
Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?
My select works fine when I select 3 fields, but trying to select more than three, when I add two fields more, it doesn't give me the last two fields, being these two fields added lattely in the table...
I mean... I added two fields more to the table one it was created, and the script executed. After that, if I try select * from table, the recordset does not have this two fields... only the old ones...
I am trying to do a Count query and can't get a 0 to appear in the count result. Pretty week on code so searches on Nz and IIf have made me more confused than ever
Any help appreciated
Query is
SELECT Count([6qryExpWar1_1].aid) AS CountOfaid, [6qryExpWar1_1].EW1 FROM 6qryExpWar1_1 GROUP BY [6qryExpWar1_1].EW1;
Help! I'm sure this is something stupid, but I can't figure it out. (usine 97 / xp) I have a main switchboard, which calls switchboard A, which has form A on it. I have a command button which closes form A and returns the user back to switchboard A, but I need it to go back to the main switchboard. (Switch was created using Switchboard manager).
I've tried, searching throught the help here & I've also tried
Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click
I have a function that opens a pop off form that basically has a yes or no button in response to a question (sent via openargs) at present i am using a Database Public Variable to store the response to Yes or no buttons Ans d setting to function to this value.
Function YesOrNo(Message as String) Open FormName,,,,,acdialog,Message YesOrNo=IIF(gret=1,"Yes","No") End Function
gret being the public Variable changed by the pop form
I am a liitle concerned as to whether this is the best way to achieve this as the gret varaible is used extensively through the database and i am worried about any conflicts that may occur especially in a multi user enviroment.Any comments?
I have a query that gives me the top 10 values. I would like to be able to use another query that says "only give me the second row or third row or what ever....So say I have column named ColorCode. For example...in this column it has the following:
ColorCode 1 3 2 2
I would like to be able to say give me "row 3 of the ColorCode column" and it would return "2"
IIF function not returning the false value. I have the following results based on the query shown below.
SELECT Trade.Ref_No, Trade.CERS_Price, Market.[USD/EUR_Rate], IIf("CERS_Price_currency = US Dollar.USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro], Market.[USD/CHF_Rate], Trade.CERS_Price_Currency FROM Status INNER JOIN (Market INNER JOIN Trade ON Market.MarketDate = Trade.Trade_Date) ON Status.StatusID = Trade.StatusID;
Price In Euro USD/CHF_Rate CERS_Price_Currency CERS_Price 35 7 US Dollar. USD 5 91 7 Euro. EUR 13 1,715.00 7 Brazil Reais. BRL 245 759.00 33 Indian Rupees. INR 23.00
As you can see returns all calculations whether is USD or not. Can you help please? Ultimately I want to do this for all other currencies by nesting the IIF function.
I was wondering if there was a function in access which allows the user to type in a date, and instead of showing just the date, it shows the actual day of the week as well, in a seperate field. Is this possible?
I've good a very very strange problem... I do an insert of a new row into the table TRA. That insert creates an id for the new row. Right after that I do a select of the new row, trying to get the new id created. Well, the insert is working fine, but the select sometimes gives me values, sometimes not. How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); " DoCmd.RunSQL (sql) db.Close Set db = OpenDatabase(database) sql = "" Set rcset = Nothing sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000. I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a recordset. The "Execute" button on the form kicks off VBA which builds and runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each ProjectID -- based on a combination of a couple of fields. For instance, the following is possible:
If the user wants to select ProjectIDs where Employee="Herman" -- without making a selection on ProjectCategory -- I want only one of the two "Herman" rows above to be returned. And I don't really care which one. (Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both "Herman" rows are returned. I've tried numerous approaches -- this being the most recent. The SQL statement is being built as I expect, and it's executing. It's just not giving the results I want/expect. Also, I've hardcoded selections into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click() Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _ "first(StartDate), first(EndDate), first(ProjectActive), " & _ "first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _ "first(Employee), first(ProjectCategory) " & _ "FROM qryProjectsForReport WHERE (ProjectActive = " Select Case optStatus Case 1 strSQL = strSQL & "True) " Case 2 strSQL = strSQL & "False) " Case 3 strSQL = strSQL & "True or ProjectActive = false) " End Select If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory = " & cmbCategory If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #" & dtStartDate & "#" If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" & dtEndDate & "#" If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient If Len(cmbSector) Then Select Case cmbSector Case 1 strSQL = strSQL & " AND Sector1 = True" Case 2 strSQL = strSQL & " AND Sector2 = True" Case 3 strSQL = strSQL & " AND Sector3 = True" End Select End If strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;" MsgBox strSQL OpenReport strSQL, chkDatasheet
I have a query with two table's in them. One is filled, the other is still empty. Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results. I am starting to think it is because one table is empty it returns nothing. How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty. One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
I have 3 tables - Students, which contains all students; Attendance, which contains dates a particular student was absent; and AttendanceCtrl which contains Quarter beginning/ending dates. I need to be able to calculate the number of days a student was present, absent, tardy in a quarter. The query needs to extract all students regardless of whether or not they missed any days. I tried using a Left join Students to Attendance and then joining AttendanceCtrl but Access said it couldn't do it because of "ambiguous outer joins". It said to create the first join then use that query in the second join. I tried that. I created a query with a Left join joining Students to Attendance. That gives me what I wanted, all students from Students and also those students with matching records in Attendance.
The 2nd query is giving me problems. I tried joining the first query (qryStudents) to AttendanceCtrl and calculating my totals. Problem is, the query only gives me students what have records in the Attendance file; it doesn't give me all the students. If a student did not miss any days, I need to show that he was present 90 days out of 90. Because there's no matching record in the Attendance file, I get nothing for those students.
As I said the first query (qryStudents) seems to be working but I need to take the records from that query, group them together by StudentID, and then calculate my totals. How can I do this? Am I approaching this wrong altogether?
Hi. Can someone help me? I am trying to make a query return all values (please see attached jpg) It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?) In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
I have 10 combo boxes where years are selected. If the last year nothing is selected (meaning is empty) then my query should return the last selected value. It seems the IIF query does not seem to work (Still showing empty). Can you please advise and help me on this?
The query I am using is shown below.
Thank you
dfuas
IIF([Trade].[Vintage_ to] = ' ',([Trade].[Vintage_from9]) OR ([Trade].[Vintage_from8]) OR ([Trade].[Vintage_from7]) OR ([Trade].[Vintage_from6]) OR ([Trade].[Vintage_from6]),[Trade].[Vintage_ to]) AS [Vintage to]
Hi all I have a query that is getting data fron 2 linked tables. The relationship between these 2 tables is 1 to many. The first table(One side) contains Due Dates for services. I have asked the query to return services for the current date which is working fine. The second table(Many side) has additional work required for each vehicle. The work can be either New, On Hold, In Progress or Completed. I only want work that has not been completed to be returned if the vehicle is due for a service today. I tried having the Work Status field in the query and criteria set to <> Completed. This returned all open work including vehicles not due for service. Is there anyway this can be achieved. If I take the criteria out of the Work Status field I get the information I am looking for but it includes Completed work and I do not need to know this. Any and all advice appreciated.
here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Hello, Run-time error ... No value given one or more required paramete
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");
basically i need to create an update query to update the OptionCombo field where: 1. OptionCategory=BODY 2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion) 3. For each InvoiceNumber AND GuitarItem.
NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.
How should I rewrite the SQL to achieve this without getting this error?
I have just joined the forum and spent the last day or so looking around for an answer that I wasn't able to find.
I have been asked by my employer to capture details of who has signed our confidentiality policy. I have made a very simple database which only has three tables, EMPLOYEE, POLICY and VERSION.
EMPLOYEE has three fields, NAME, NUMBER and BRANCH. POLICY has three fields, NUMBER, DATE and VERSION. VERSION has two fields, VERSION NUMBER and DATE. I won't worry about the VERSION table any further in this post as it doesn't figure in the problem query.
Primary key for EMPLOYEE is NUMBER. Compound key for POLICY is NUMBER and DATE (ie. the employee can sign the policy more than once and we want to record when this was, and whether the policy version changed).
I have an Access "type 3" join defined in table relationships from EMPLOYEE.NUMBER to POLICY.NUMBER.
I am trying to write a query that returns the employees that haven't signed the policy. I have no problem returning the employees that have (ie. employees whose numbers match the numbers in the POLICY.NUMBER field), but it is the EMPLOYEE.NUMBER entries that are not present in POLICY.NUMBER that are causing me grief.
I have tried using the expression builder in the query builder window (it gave me <>[tblPolicy]![Number]) which doesn't return any records (I have over 490 in the database and should be getting back at least 50 or so).
I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?
HI all hope you can help. I have a combo box on a form displaying a list of postcodes. These postcodes are split into 5 different areas and so have a corresponding 1 - 5 ID code in the underlying table (table has postcode and postcode ID only). Problem is when I select postcode and 'submit' to save it to the record the combo box returns to the first postcode in the list with that postcode ID. e.g postcode AB = area 1, BG = area 2, EF= area 1. If I select EF when I press submit it changes the value to AB in the combo box. I need the 1 - 5 ID code to perform calculations based on area. And this all works at the moment but for this annoying problem! D Diver
I cannot seem to get my query to return all the records i want it to, i want to search by year group, which it is doing fine, but if a record has a table wich does not have data in it, the query will not return that record, i can't find the solution anywhere!
I have a form with text fields where I am entering customer last names and first names in different fields. Does anyone know how to return UPPER CASE letters automatically?
