I am very new at all of this. I am trying to build a filter form to just pick out one item. I am trying to filter out Box Numbers that have been entered into the database to run reports on them. I think that I am missing the correct code to do this. I have tried to create a form off the query by just using the box number and then creating a command button to apply the filter. But this doesn't work. I'm sure that I'm missing a step along the way. Help!
I have a button that opens a report. The code behind the button builds the filter criteria for the report based on some selections in a list box.
Code: DoCmd.openReport "Report", acViewPreview, "", GetCriteria For VarItem = 0 To Me.List2.listcount - 1 strCriteria = strCriteria & "([ProjectNo]= '" & Me.List2.Column(1, VarItem) & "' And [ClientID] = " & Me.List2.Column(0, VarItem) & ") Or " Next VarItem If strCriteria <> "" Then strCriteria = Left(strCriteria, Len(strCriteria) - 4) Else strCriteria = "True" End If GetCriteria = strCriteria
This is what the filter would look like with values after running the report (taken from filter bar in report properties):
([ProjectNo]= '150002' And [ClientID] = 206) Or ([ProjectNo]= '150003' And [ClientID] = 79)
Problem is that i only get records for ([ProjectNo]= '150002' And [ClientID] = 206). I this seems only filter ONE set of criteria ignoring all the others. What am i doing wrong?
Could someone please give me a link or place to find a descreption of all the "More Tools" that you can use when building a form. If I had a list I could pick what is best for my database and explain things better to management...thanks.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
Dear All, I want to make a multiple criteria query from active form, but i stuck in SQL ,Please help to solve my syntac. I am very fool in Sql Syntac. the code like bellow:
Option Compare Database
Private Sub cmdSearch_Click() On Error GoTo cmdSearch_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strUmur As String Dim strJnsKelamin As String Dim strPendidikan As String Dim strPosisi As String Dim strJnsKelaminCondition As String Dim strPendidikanCondition As String Dim strPosisiCondition As String Dim strSQL As String '************************************************ ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "PelamarTemp_Qry" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "SELECT * FROM Pelamar" cat.Views.Append "PelamarTemp_Qry", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "PelamarTemp_Qry") = acObjStateOpen Then DoCmd.Close acQuery, "PelamarTemp_Qry" End If '************************************************* * '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Build criteria string for Umur
strUmur = Me.Umur.Value Debug.Print strUmur '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^ ' Build criteria string for JnsKelamin strJnsKelamin = Me.Jns_kelamin.Value If Len(strJnsKelamin) = 0 Then strJnsKelamin = "Like '*'" Else strJnsKelamin = "IN(" & strJnsKelamin & ")" End If Debug.Print strJnsKelamin '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Build criteria string for Pendidikan strPendidikan = Me.Pendidikan.Value If Len(strPendidikan) = 0 Then strPendidikan = "Like '*'" Else
strPendidikan = "IN(" & strPendidikan & ")" End If Debug.Print strPendidikan '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Build criteria string for Posisi strPosisi = Me.Posisi.Value If Len(strPosisi) = 0 Then strPosisi = "Like '*'" Else strPosisi = "IN(" & strPosisi & ")" End If Debug.Print strPosisi '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Get JenisKelaminCondition If Me.optAndJnsKelamin.Value = True Then strJnsKelaminCondition = " AND " Else strJnsKelaminCondition = " OR " End If Debug.Print strJnsKelaminCondition '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Get PendidikanCondition If Me.optAndPendidikan.Value = True Then strPendidikanCondition = " AND " Else strPendidikanCondition = " OR " End If Debug.Print strPendidikanCondition '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ ' Get PosisiCondition If Me.optAndPosisi.Value = True Then strPosisiCondition = " AND " Else strPosisiCondition = " OR " End If Debug.Print strPosisiCondition '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ '************************************************* * ' Build SQL statement '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^ 'SQL Statement from Query builder 'SELECT Pelamar.No_pelamar, Pelamar.Tgl_lamaran, Pelamar.Nama, Year(Now())-Year([Tgl_lahir]) AS Umur, Pelamar.Jns_kelamin, Pendidikan.Ijazah, Posisi.Posisi 'FROM ((Pelamar INNER JOIN Pekerjaan ON Pelamar.No_pelamar = Pekerjaan.No_pelamar) INNER JOIN Pendidikan ON Pelamar.No_pelamar = Pendidikan.No_Pelamar) INNER JOIN Posisi ON Pelamar.No_pelamar = Posisi.No_pelamar 'WHERE (((Pelamar.Jns_kelamin)=[Forms]![SearchByKriteria_frm]![Jns_kelamin])) OR (((Pendidikan.Ijazah)=[Forms]![SearchByKriteria_frm]![Pendidikan])) OR (((Posisi.Posisi)=[Forms]![SearchByKriteria_frm]![Pendidikan])) OR (((Year(Now())-Year([Tgl_lahir]))<=[Forms]![SearchByKriteria_frm]![Umur]));
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^ 'Please I am very stupid in sql,how to apply all variabel that i had create and insert in the sql in vba? 'i had try write as bellow but the result is error number 2465 with error description "Microsot Access can't find the field"I" reffered in your expression" 'All i need is how to write SQL in vba like the sql statement in query builder for make a query on form. ' note: all variabel string is all from the form '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^6 strSQL = "SELECT Pelamar.* FROM ((Pelamar INNER JOIN Pekerjaan ON Pelamar.No_pelamar = Pekerjaan.No_pelamar) INNER JOIN Pendidikan ON Pelamar.No_pelamar = Pendidikan.No_Pelamar) INNER JOIN Posisi ON Pelamar.No_pelamar = Posisi.No_pelamar" & _ "WHERE Pelamar.[Jns_kelamin] " & strJnsKelamin & _ strPendidikanCondition & "Pendidikan.[Ijazah] " & strPosisiCondition & _ "Posisi.[Posisi] " & strJnsKelaminCondition & (Year(Now()) - Year([Tgl_lahir])) = " strUmur "";"
Debug.Print strSQL '************************************************* * ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("PelamarTemp_Qry").Command cmd.CommandText = strSQL Set cat.Views("PelamarTemp_Qry").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "PelamarTemp_Qry" ' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdSearch_Click_Exit: DoCmd.Echo True Exit Sub cmdSearch_Click_Err: MsgBox "An unexpected error hass occurred." _ & vbCrLf & "Procedure: cmdSearch_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdSearch_Click_Exit End Sub
Private Sub optAndJnsKelamin_Click() If Me.optAndJnsKelamin.Value = True Then Me.optOrJnsKelamin.Value = False Else Me.optOrJnsKelamin.Value = True End If
End Sub
Private Sub optAndPendidikan_Click() If Me.optAndPendidikan.Value = True Then Me.optOrPendidikan.Value = False Else Me.optOrPendidikan.Value = True End If
End Sub
Private Sub optAndPosisi_Click() If Me.optAndPosisi.Value = True Then Me.optOrPosisi.Value = False Else Me.optOrPosisi.Value = True End If
End Sub
Private Sub optOrJnsKelamin_Click() If Me.optOrJnsKelamin.Value = True Then Me.optAndJnsKelamin.Value = False Else Me.optAndJnsKelamin.Value = True End If End Sub
Private Sub optOrPendidikan_Click() If Me.optOrPendidikan.Value = True Then Me.optAndPendidikan.Value = False Else Me.optAndPendidikan.Value = True End If
End Sub
Private Sub optOrPosisi_Click() If Me.optOrPosisi.Value = True Then Me.optAndPosisi.Value = False Else Me.optAndPosisi.Value = True End If
End Sub
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^66 Is there any one can correct my syntac?
Hi, I am desparate!! I work for a high school and need a database to record student community service hours over their high school careers. I have been trying to do this on and off since the summer. I promised I would have it done by the end of winter break but........I may have to go back to a spreadsheet if I can't get this right. That has proved to be a royal pain in the past few years.
I have set up tables but getting the relationships right is a problem. I dont know if it is possible but I would like to set it up so that I have a student form with 4 subforms for each school year. Each subform (4 tabs) will have the title of the school year (i.e. 05-06). We don't allow them to get credit if they go over on their hours for a given year. In other words, they can't do all their service hours in say, freshman year. They have to do 10 as freshmen, 20 as sophomores, 30 as juniors and 40 as seniors.
Each year I can refresh from the school db (for new students, etc.) but I can't go in and create a report in that program as we don't have the capability.
I am trying to teach myself but every tutorial I come across doesn't give reasons why you do something so I can figure out how to convert that info to help me. I have been researching this for a long time and looking at sample dbs.
Can anyone help? I am going to try to attach a copy of the mess I have made so far, Hopefully
I need some assitant in building a simple check reconciliation database. I was wondering if anyone can guide me in doing so? Or if anyone has some kind of sample i can use that would be great. thanks
I have made an Access database containing contract names and addresses. I need to have each of these contracts linked to another table which shows the allocated engineer for the particular contract. I used a "one 2 many" relationship using a field "LinkID" to do this. Each engineer has a unique "LinkID" in the engineer table but the engineer changes each day. The engineer for each particular day comes via an excel spreadsheet. The way it works at the moment is that the engineer name has to be manually changed each day, and this takes time. I have tried importing a linked excel table which looked like it would work, but was not able to use the linked table in the same "one 2 many" related table format as above.
What i need to do, is find a way for the fields containing the engineer name to be automatically updated somehow, possible by reading the values from the excel sheet.
Anybody know of any way for this to be done?
Thanks, and hope this makes sense got its got me baffled.
*** Edit **** Thinking about it, i could have an imported execl sheet which contains all of the engineers for each day. Would there be a way using a macro, to update the data in the engineers table that is related to the contracts, by overwriting it with the data from the linked spreadsheet?
I have at least 15000 records and all of them should be corrected if there is existing duplicates with same EMBG and different name. More precisely if there are 2 persons with same EBMG lets say 123456789 but one with name Naim Arifi and other one Naum Arifi then query should present to me Naim and Naum. Example
So the record 1 3 and 4 and 722 should be highlited because they have same EMBG and I need to find where is the mistake manualy (correcting from Naum to Naim). In this case row number 4 is mistake instead of Naim is Naum. I need to correct it manualy.
I'm running a 9 parameter query, where values for the criteria are drawn from a form. I'm confused as to how I build an "if, then" string so that if one of the parameters is left blank, the program will skip it and go to the next.
Ok, I currently have a query built with requests. What I want to do is initially set each request with a priority. Then when one request is closed the query will take the requests with priority 2 and change it to priority 1, change the request with priority 3 to priority 2 and so on. Also any new requests that are entered I want to be able to set their priority as well. Can anyone help me with this, or is it even possible?
I need to create an sql statement where the having statement can grow depending on how many conditions I pass to it. There maybe 10 conditions or there may only be 1 condition statement. I guess I'm really looking more for proper structure. Should I use "if" statements or is there a way to structure a variable with "OR" statements in it and use that.
I am hoping this is the right place to find some help. I have just started access (2003) and i am trying to build a list. Let me explain.
I have a table that contains cartcode, description, price
and another containing manufacturer, model
I then created a new table that links these tables together so that if i click the little + next to a cartcode i can see a list of models linked with that cartridge.
What i want to do though is create a query/table/anything that will create me a list of cartridges and then in the second column a list, hopefully seperated by a comma or all the printers models related to that cartridge. I hope that makes sence.
So my question is how do i create the list which included related printers?
Hi all, i'm new here and I have a query about a database i'm building with which any help would be appreciated.
I'm designing a form and I need to be able to have an option to save to a temporary table for retrieval by users so that they can continue their saved record at a later time. Does anyone have any ideas on the best way to do this as I am relatively inexperienced and I have to have the whole database complete before Wednesday:confused:
I have been asked to add a building entry log (Session Log) for a gym members database I created 2 years ago, unfortunately having racked my brain for the last week I am no nearer a solution.
Background: I am a novice with a little knowledge (Dangerous I know), the main structure of the database is Contact [Storing address and name], Payments [Storing membership info - renewal date etc] and Sessions [Storing session date & time] this sessions table was recently added as a stop gap measure to allow the staff to enter user sessions manually.
Problem: System needs to log user as IN and record the current time on first entry of userID (5 digit number), then the second time userID is entered it must log user out and record the time.
Resources: I have just a single laptop running windows 98SE, and a USB numeric keypad. I should be able to get hold of a second monitor if required, but beyond that it is a case of beg, borrow, or steal as my budget is ZERO.
Any help, or suggestions to get me on the right track would be greatly appreciated.
Hi i have a assignment in which i have to build a database with documentation (attached file has details about the assignment) I have no prior database experience, and have no idea where to start.I want to build the database for a computer store which sells computer products. I don't want to make it very complex, since my database skills at this stage are very poor. I will appreciate if someone gives me a run down or steps in building this database. I have no clue where to start, since this is probably the first database i have ever built.
I have created an invoicing system for my business, as i was unhappy with MYOB. Basically i have Product ID and Desciption in 1 table. In another, called registry, this is where i input the data for the order.
What i basically want to do is? When i type in the Product ID in the registry table, i want the description field to automatically appear in the cell next to it. As this would save a lot of time
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
I have a query where I would like to include names from a name table. The problem is that I get this "ambiguous outer joins" error when I try to include it in my query. I have a main table, the name table, and a join table with the IDs from both of the other two tables in order to model a many-to-many relationship between main table and name table.
Is there a way around this?
Right now the query contains the main table and the join table so I get the name ID, just not the real names.
Hello. I'm building a history table to keep track of some changes that occur on one of my forms. Here is what I'm using to build my history table: Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("tblDateCycleTestingChanges", dbOpenDynaset) With rs .AddNew ![CycleTestingDatesID] = Me.CycleTestingDatesID.Value ![Changed] = Now() ![ClientID] = Me.ClientID.Value ![ProjectID] = Me.ProjectID.Value ![TaskID] = Me.TaskID.Value ![TaskStartDt] = Me.TaskStartDt.Value ![TaskEndDt] = Me.TaskEndDt.Value .Update End With Set rs = Nothing End If I have 2 questions: 1. I'm not sure where to insert this event. I'm thinking on Before_Update on the updated field? (I don't want to put it into Before_Update for a form event as I have other things that are being filled out/changed and I only want to keep track if certain fields on the forms are updated/changed. 2. I would also like to keep track of Old and New values for those specific fields. Is that at all possible? Thanks.
Iam doing an Access project And i have poblem in calculation in a form. This calculation basically is between a form and a table. I have a form called "Yearly _Amount" in which there is a field called "Total_Hours". Now i want to calculate this Total_Hours as "Sum of Hours" (Hours is record in "Time_Sheets" table) Whenthree fields are equal ie., Employee_#,Fiscal_Year and Project_# in Yearly_Amount form is equal to Employee_#,Fiscal_Year and Project_# in Time_Sheets Table.
Please guys help me out. Iam struggling too hard but iam not finding a solution.
My users wish to have a feature in my db that allows them to compare the records within it and view a report on it.
I have implemented such a form but the functionality is not great. Basically it's a form with two subforms - the top where you choose the projects and the bottom where you view a list of the projects you've chosen. You can search the top subform using combos and select all the records currently in view (a button runs an update query based on the combo box values)
To add them to the list the user is setting a yes/no field to true. So the top subform is where yes/no field is false (and bottom subform is where yes/no is true).
They can then report on this as the report has a criteria of where the yes/no is true.
This works brilliantly, so long as there is only one user!! Otherwise they would interfere with eachother's list-building.
Does anyone have any ideas for an alternative method so that multiple users can use it? I was thinkin of making tables or something to store IDs in but don't know much about this or where to begin!!
