I want to create an IF statement based on:
If Right(DMax("[MyField]", "[MyTable]"), 2) < 06 Then.
But VBA automatically removed the preceding 0 showing:
If Right(DMax("[MyField]", "[MyTable]"), 2) < 6 Then.
The IF statement should pick out anything lower than 06 (so 16, 26 etc should be ignored).
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
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 not sure where I am going wrong.
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
[code]...
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?
sort this error out:
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
[Code] ......
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.
I have an issue that can be described as follows:
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
end function
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
Code:
= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)
However, it is showing the control (wf1oi) as a blank control now. When I run the dlookup through the immediate window, the returned result is Null.
A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi
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:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
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:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
If the following works for a single criteria in a where statement.
Code:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblProcedures WHERE ProcedureID = " & lngMoveUpID, dbOpenDynaset)
What would a second criteria look like.
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:
Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"
DoCmd.RunSQL SQLtext4
Note: My DLookup works fine by itself for other uses.
What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.
Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"
[code]....
I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?
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:
Code:
strWhereFinal = strWhere1 & "AND " & strWhere2 & "AND " & strWhere3
You can see that if strwhere1 is NULL, the string will start with "AND [ApplicationStatus] = 1" and will obviously cause a failure.
Is there a simple way to build this string based on the number of search strings that are not null?
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;
I have a SELECT statement which works
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 having a problem with below and getting a run-time error 3134
Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)
CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"
The field 'Order' in Model_types is a Long Integer.