Modules & VBA :: Using And / Or Within If Statement
Aug 2, 2013
I am having some issues trying to use both and & or in a If statement.
The code below is what I am trying to do and it is not working.
Code:
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Or (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "No longer eligible") Then
MsgBox "Please enter Associate Name"
Me.Date_authorization_expires.SetFocus
I tried the code below and it works but as soon as a I enter the Or part it will not work.
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Then
MsgBox "Please enter Associate Name"
Me.Date_authorization_expires.SetFocus
I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.I have button that copies a certain number of fields to the clipboard. The one below is one of them. Here is the coding I am using that works:
Code: =Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))
I would like to add the following condition to this code. I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.
Using Access 2010. I use the following code to search for a client based on ClientID. ClientId is an Auto Number, Long Integer, format is 00000. The code works fine except if I type in the leading zeros for a ClientId that does not have at least a 1 in the first digit. For example it will not find client 00100 but will find client 10001.Is there any way of setting the Dim statement to look for a ClientID with a leading zero?
Private Sub cmdClientIdSearch_Click() Dim rs As Object Dim strCriteria As String strCriteria = InputBox("Please enter Client ID" ) If strCriteria > "" Then
I have the following code on an After Update event:
If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then Me.ProgPriceTxt.Value = "85" End If
This works fine. When I add another "And" to the statement, however, it no longer functions:If
Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then Me.ProgPriceTxt.Value = "85" End If
Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).
I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code: If Nz(Me.txtrefNo, 0) = 0 Then Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1 tmpRefNo = Me.txtrefNo 'testing variable value MsgBox (tmpRefNo) Else MsgBox ("What's going on?") End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
Code: If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero i = 1 Else i = DMax("ID", "tblupdate") End If Dim ssql As String Dim j, k As String j = "P" & i k = Environ("username") ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')" CurrentDb.Execute ssql, dbFailOnError
I want to create a table via SQL. This table should contain records that begin after a specified date.
Something is wrong with this syntax.
Code:
Sub TEST() Dim t As Date t = 1 / 3 / 2014 DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as Price, TRP.Valid_from, " & _ " TRP.Valid_to INTO [New_Prices] " & _ " FROM TRP " & _ " WHERE (((TRP.Customer)= 1223) AND ((TRP.Valid_from)>#t#))" End Sub
I'm trying to create a report that does the following:
If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.
If Not IsNull(Me.Time1) Then If Me.Time1 = "Other" Then Me.Time1.Visible = False Else Me.Time1.Visible = True
I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.
For example:
INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"
The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.
There is a textbox on a form and when I fill in its value, I would like it to lookup whether that value matches any of the values in another form and if it does present a message box. Both fields hold only numerical data, so no type mismatch issues.
I've tried the code following, but it only looks up the first value on the second form, not its entire dataset, which is problematic.
Code: Private Sub Lot_AfterUpdate() DoCmd.OpenForm ("Pot_Pot_ExtraLots") If Me.Lot.Value = Forms!Pot_Pot_ExtraLots!ExtraPotLots.Value Then MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!" DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo Else DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo End If End Sub
I have a form with a sub form based on a query, in one of the fields i use a dbl click to open another form. I have use this in several areas in my DB but i having issues in one section of it not opening to the correct record. i tried a few things but i am limited to what i can use. The Record_Number is an Auto number and CLASS_Num is an entered number buy the user.
I have 2 things that i can use but i can not get the Record_Number to work. When i use the Record_Number i get an Data Type Mismatch Error.
---------------------------------------------------------------------- Private Sub Combo10_DblClick(Cancel As Integer) DoCmd.OpenForm "Documentation_Form", acNormal, , "[Record_Number] = '" & Me.Record_Number & "' and [CLASS_Num] = '" & Me.CLASS_Num & "'" End Sub -----------------------------------------------------------------------
Is it possible to use recordsets inside an sql-statement how described in following example. the error message: access can't find the table or querydef.
Code:
public function useRS (RS_ext_1 as DAO.Recordset, RS_ext_2 as DAO.Recordset) as DAO.Recordset dim sql_RS_int as string dim RS_int as DAO.Recordset
sql_RS_int = "SELECT * FROM RS_ext_1, RS_ext_2 WHERE col1_ext1 = 1 and col1_ext2 = 5" set RS_int = CurrentDB().OpenRecordset(sql_RS_int) set useRS = RS_int.Clone
I have the following SQL Statement which returns the desired result to me
Code:
SELECT tbl_p_stats.i_closing FROM tbl_p_stats WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));
And am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code: INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role ) SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role FROM tblPeople WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code: Dim strSQL As String Dim db As Database Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:
I have a database where I am pulling my data in my subform from a tbl made by a make table query. However, Once a month the data will be updated and sorted by "Retrieve Date". I have made a function through a module that updates the queries with the latest retrieve date everytime new data is pasted into the database.
I am looking for a way to make the make table query only update the new data and not post records into the tables more than once. Should this be done by writing an IIF then statement and using an append query? I will copy the SQL code from my make table query below.
SQL:
SELECT qrySummary.CounterpartyName, Format([CDSsprd],"Standard") AS CDSspread, Format([Gsprd],"Standard") AS Gspread, Format([RatingsGrade],"Standard") AS RatingGrade, Format([BloombergCDS],"Standard") AS BloomCDS, Format([Avgerage],"Standard") AS [Avg], qrySummary.RetrieveDate INTO tblSummaryQry FROM qrySummary;
Code: "SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table ORDER BY Bookings_Table.Booking_Time;"
But when i add the WHERE
Code: "SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table WHERE (((Bookings_Table.Booking_Date)=[TB_CAL_DATE])) ORDER BY Bookings_Table.Booking_Time;"
it doesn't work [TB_CAL_DATE] is a textbox with a Date in it...
I have created several queries that in turn create a report. I am trying to write some VBA script to make them execute correctly, however am having trouble with the loop function.
DoCmd.OpenQuery "Qry_SubjectColleaguesByDivision" Do Until Tbl_ReportSubject.RowCount = 0 DoCmd.OpenQuery "Qry_DataToTrainingReport" DoCmd.OpenQuery "Qry_DeleteDataToTrainingReport" Loop DoCmd.OpenReport "Rpt_TrainingDue28Outstanding" DoCmd.OpenQuery "Qry_ClearTrainingReport" Qry_SubjectColleaguesByDivision populates Tbl_ReportSubject with the relevant colleagues. Qry_DataToTrainingReport calculates the report data for the first user in the list and copies to Tbl_TrainingReport, Qry_DeleteDataToTrainingReport deletes their record in Tbl_ReportSubject. Qry_ClearTrainingReport clears Tbl_TrainingReport.
My issue is getting the DO UNTIL statement to work. I am trying to get the loop to finish when there are no records in Tbl_ReportSubject.I know the data structure does not seem robust based on how the data is handled, however this is to do with a) Legacy issues, and b) data importing from other systems.)
I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:
One Lender to Many Relationship IDs One Relationship ID to Many Tax IDs One Tax ID to Many DocumentsRequired
My forms are nested as follows:
Relationship ID form (contains info for Relationship ID and Lender) -> Tax ID -> DocumentsRequired
I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.
Dim strtext As String Dim strsearch As String strtext = Me.SearchBoxTxt.Value strsearch = "SELECT [Relationship ID] " & _ "FROM CustomerNormQuery " & _ "WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _ "OR [EIN/SSN] Like ""*" & strtext & "*"" " & _ "GROUP BY [Relationship ID]" Me.RecordSource = strsearch
I am trying to build a new sql statement for a query using a string from items selected in a list box on a form. I have the string in place and keep getting syntax errors for the new sql statement. The code is below.
strSQL = " SELECT JG_tbl_LMEMP.DEPT_CODE" & _ " FROM JG_tbl_LMEMP" & _ " Group by JG_tbl_LMEMP.DEPT_CODE" & _ " Where JG_tbl_LMEMP.DEPT_Code IN(" & strCriteria & ")" qdf.SQL = strSQL
