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 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?
I am setting up a form using a couple combo boxes. I want to populate a text box or 2 from the item record selected from one of the comboBox. Below is the code I am using.I have commented out where I am trying to transfer the value to the form, and try focusing on the query. When I run this code I get a type mismatch.
John
rivate Sub cbx_SlideNo_Change() Me.Refresh Dim StringSQL As String Dim rs As Recordset StringSQL = "SELECT Slides.[SlideTitle] " StringSQL = StringSQL & "FROM Slides "
I've a form that has 2 subforms - which subform is used is dependant on the selection of a combo box in the main form. After much time spend trying to set this up I am happy to report that this section works fine.
My problem is that when I have completed the subform I am not able to continue tabbing through the rest of the form.
I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.
I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?
I have a table called Prem. It contains three fields (Offer, MailCode, MktgProgam). I want to have a form where a user can select. the Offer (from the offer field) Once selected, it takes the MailCode and MktgProgam information from the Prem table and uses that info (MailCode, MktgProgram) to pull from the main table called "Cust" Is this doable?
Hey there! I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D
Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.
The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.
I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.
For example for the invoices field have used: IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])
So: IIf(form checkbox = true, form text box value)
However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.
What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.
Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
I have combo boxes populated with all values in a particular field, then shunt the form onto that record (standard combo box for selecting records from a list in other words).
Problem is, these boxes seem to stop working if I leave the form for another and then return to it.
Here's the code Access puts behind:
----------- Set SerialRS = Me.Recordset.Clone SerialRS.FindFirst "[Serial Number] = '" & Me![Combo60] & "'" If Not SerialRS.EOF Then Me.Bookmark = SerialRS.Bookmark -----------
The form may be opened and closed via other processes (although never unloaded), but when I come back to it these combo boxes always stop working! Why?
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code: Private Sub Command18_Click() On Error GoTo errorcatch Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True Exit Sub errorcatch: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
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.
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
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 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?
