Forum Widgets
Latest Discussions
Update Query - 3 criteria
Hello Experts, I need to update 3 fields in a table Import_ExcelPC but I am not getting anywhere with it. Let me explain what I need in simple plain english Update: Import_ExcelPC.Notes to the value show in [Import_ExcelPC_Copy].[Notes], Import_ExcelPC.OKToPayYN to the value show in [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC.DABYN to the value show in [Import_ExcelPC_Copy].[DABYN] but its for records where [Import_ExcelPC].[Invoice amount] = [Import_ExcelPC_Copy].[Invoice amount] AND [Import_ExcelPC].[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped] here is my sql: UPDATE Import_ExcelPC INNER JOIN Import_ExcelPC_Copy ON (Import_ExcelPC.[Invoice amount] = Import_ExcelPC_Copy.[Invoice amount]) AND (Import_ExcelPC.[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped]) SET Import_ExcelPC.Notes = [Import_ExcelPC_Copy].[Notes], Import_ExcelPC.OKToPayYN = [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC.DABYN = [Import_ExcelPC_Copy].[DABYN] WHERE (((Import_ExcelPC.Notes) Is Not Null)) OR (((Import_ExcelPC.OKToPayYN)<>0)) OR (((Import_ExcelPC.DABYN)<>0)); DABYN, OKToPayYN are Yes No fields. NOTES is long text. in picture: the ouput is 0 records to update. There should be 198 records being updated though. I run this select query and it shows 198 records based on the criteria SELECT Import_ExcelPC_Copy.Notes, Import_ExcelPC_Copy.OKToPayYN, Import_ExcelPC_Copy.DABYN FROM Import_ExcelPC_Copy WHERE (((Import_ExcelPC_Copy.Notes) Is Not Null)) OR (((Import_ExcelPC_Copy.OKToPayYN)<>0)) OR (((Import_ExcelPC_Copy.DABYN)<>0)); here is a screen shot. Its 198 records. Why wont those 198 records update in Import_ExcelPC instead of showing 0 records to update? I am not sure what I am doing wrong? thank you for the help.
Filter Form with Like and Or
Hello Experts, I am having a tough time adding a condition to a filter on a form. It works with 1 condition but not sure about the OR part (sq, dq) Maybe it is correct. The error I get is "Type Mismatch" see pic below. The row highlighted is the Remarks line. It seems odd because the fields are "short text" in the underlying table. Do you see an issue with the OR statement? If not, any ideas why I would be getting that error knowing that the fields are text? Private Sub txtEndUser_AfterUpdate() 'http://allenbrowne.com/ser-28.html If IsNull(Me.txtEndUser) Then Me.FilterOn = False Else Me.Filter = "[Remarks 1] Like '*" & [txtEndUser] & "*'" Or "[Remarks 2] Like '*" & [txtEndUser] & "*'" Me.FilterOn = True End If End Sub here is the table showing they are short text. the table is imported. as a reminder, I have no issues with 1 condition meaning it filters fine.VBA code to check if a user is in a table
A shout to VBA Experts, I am looking for some guidance. I am a novice Access/VBA user I am self taught through forums and U-Tube. I have made a defect record program in Access, I came across a piece of VBA code as shown below to enable or disable buttons on a Log In Form if a user’s name is recorded in a table called DMUsers and the check box for active user is ticked. This allows the user access to the main menu. This works very well, but the problem I have is that if someone who’s user name is no in DMUsers tries to open the access program. The VBA Debugger screen appears when Access tries to load the Log In Form as shown below. I have tied various way to get around this without success. I know I need VBA to check if the StrUserName is listed in DMusers first, if then a message box if not . I would be most grateful for any guidance or help. This Access program sits on a drive on a server, _________________________ Private Sub Form_Load() Dim StrUserName As String 'The Eviron method can retrieve a lot of useful information about the computer with little coding required StrUserName = Environ("USERNAME") ' This retrieves the name of the current logged on user ' You can use this code to see if the user’s computer is on a domain or a workgroup Dim domain As String domain = Environ("USERDOMAIN") ' DLookup will lookup a table and return a value from the specified field ' It used as follows: DLookUp( Name of field to return, Name of table to search, Criteria) ' When using it, you should ensure that the result will return 0 or 1 record only. ' In this example, I have creaed a table called Settings and it has a single field called Domain ' In the table, I have set the name of the Domain that I am expecting this to be running on ' I am also converting value to lowercase (LCase) so it is case insensitive 'Assuming that the user is authenticated by virtue that they are on the domain and their name is in the database 'Then you can use DLookUp to set the status of buttons to restrict user functionality without needing a single password Command27.Enabled = DLookup("ActiveUser", "DMUsers", "[UserName] Like '" & StrUserName & "'") End Sub A Snap of the DMUser table below Snap of the VBA debug which appears when a user whose username is not entered in the DM Users table tries to open the programReturn Value where ID<ID (Nested Query)
Hello Experts, I need to return the [RATE] in tblFXRollsChild WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK) The below is what I have but I know the WHERE clause is wrong (returns 0 records). I dont know if I need to be using "TOP 1". do you see where I am wrong? SELECT T1.IDParentfk, T1.IDRollsPK, T1.Rate AS LastOfRate FROM tblFXRollsChild AS T1 WHERE (((T1.Rate)=(SELECT [RATE] FROM tblFXRollsChild AS T WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK))) ORDER BY T1.IDParentfk, T1.IDRollsPK; In the below tblFXRollsChild, I want to return the RATE for the record above but where there is not a record then return the RATE of the current record.
