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 a table called myTable and it contains two fields Year and Month. Both are type of integer.
Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?
I know how to do this in SQL Server 2000. That is:
Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable
I upgraded my system to Windows 8 and Office 2013, and it's now 64 bit instead of 32 bit as before. Also note that I'm still using Access 2010 as before.So the following problem now arise in this new config that was not there before.
When you do a calculation like '332.16 - 1', the answer is now 331,16, instead of 331.16. Note the point are now replaces with a comma.
Might sound trivial, but everywhere in my code where is have a dynamic SQL like:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331.16), but now it is: INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331,16), so it is seen as an extra value and the statement fails.
So, yes I can fix this one statement, but I have multiple occurrences of this throughout my app. How do I fix this?
And changing something in my Control Panel - Regional Settings would not be the answer, as on all the client PC's it might be different.
How to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"
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.
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...
