I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
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.
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 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 have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.
There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:
Case {T,T,T,T}
Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}
Case {T,F,F,F}
Case {F,F,F,F}
What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.
I have two listboxes. One for Activities and the other for Organizations.
I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.
To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.
Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :
yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.
I've tried using the column property to then cruise to the proper record in the listbox, e.g. :
But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored. I believe the loop is working properly, as when the inserts are happening correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.
I just can't get any other column's value for some reason.
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code: Private Sub Command3_Click() Dim dbs As Database Dim sqlstr As String Set dbs = CurrentDb Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "") Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I am trying to narrow down a list for a combobox by specifing one of the fields value on form as a acriteria SELECT location.LOCNAME FROM location; WHERE (((location.LOCCLIINIT)=[location].[LOCCLIINIT]));
I get all list of locations' name instaed of location names with client code as displayed n current form.
I have a table with two fk's, one is oper_id and the other is oper_detail. Is it possible to write a select statement that will look in the first fk column to determine which table to get the second fk, oper_detail, data?
or I'll describe what I need to do and if someone has a better solution, I would appreciate input:
there are several different types of operations that can take place with each operation having wholly different data associated with it. Each type of operation then has many ways that it in itself can be conducted.
I created a table for each type of operation and populated them with relevant oper_details for that operation. I have another table that has oper_ID and oper_name. The log table, where this all comes together, has fk oper_ID and fk oper_detail (the row in the table oper_ID is referencing), which is supposed to tell me which way that particular oper_ID was conducted from the appropriate operation table. Select statement or change structure?
Basically I have a table called Supervisors with the following columns LastName FirstName LicenseNumber Area
I have a form in which the user select supervisor by lastName, firstName (area)
this is done by a combo box on the form with the row source of SELECT Supervisors.LastName & ", " & Supervisors.FirstName & " (" & Supervisors.Area & ")" AS SupervisorsName FROM Supervisors;
Instead of that I want to enter the supervisor's License Number so I was using the select statement to find supervisor's License Number based on lastName, firstName (area)
I guess I am asking how to create a nested select statement in vba
I am trying to add a value to a column in a query based on the value of another column.
I am using an iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column.
I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes".
I have multiple codes assigned to records in a table. I want to be able to say IIf [code] = "FMIX",[qty]*2.2046/7.1, IIf [code] = "Liqd", [qty]*2.2046 and everything else can just equal [qty]. I have tried this statment Expr1: IIf([family-code]="FMIX",[SumOfqty-on-hand]*2.2046 IIf([family-code]="LIQD",<[SumOfqty-on-hand]*2.2046>,[SumOfqty-on-hand]),[SumOfqty-on-hand]) and it doesn't work. Can anyone tell me what is wrong?
Hi, I have the following Select statement where basically i want to retrieve a customer's first and last name depending on the customer id that is already shown on my form (the customer id on the form is being displayed in a text box, CustomerIDText):
Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnn1
Dim mySQL As String
mySQL = "SELECT CustomerID, CustomerFirstName, CustomerLastName" mySQL = mySQL + " FROM CustomerTBL" mySQL = mySQL + " WHERE CustomerID = '" & Me.CustomerIDText.Value & "' "
myRecordSet.Open mySQL
But when i go to open my form I get a run time error "Data type mismatch in criteria expression"
I think the part of my statement '" & Me.CustomerIDText.Value & "' is the problem, not sure why though. Any help would be appreciated, cheers.
Also once i've got my sql statement to work, how would i get an unbound textfield to display the first name, for example that i have retrived, would it be something like: textfield.value = mySQL FirstName ?
I am using the SELECT TOP 3 statement to select the top 3 values from a given data field. The SELECT works great, as long as there are no duplicate values in the TOP 3 values. For example if the top 3 values are 210, 202 & 199 they are selected correctly but if there three records that have the 199 value I am selecting 210, 202, 199, 199 and 199. Is there a way to only select the first 3 top values, i.e. the first three encountered?
Hi I had been trying to use the "Select" statment of the sql in Access.But how do I actually execute the sql command? For example, Dim sql As String sql = "select * from Name"
What is the next line i should add to execute this in Access? Thanks
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
i want that when i press the space key on my keyboard oi will exit from the form. i searched on the interne t but i couldn't find the proper one. I don't know exactly what i have to write in vba.
I am having an issue with some imports. As you will be able to see from the attached Db file, some fields import into MS Access with a leading space in front.
The problem is, Access doesn't seem to see it. For example, (in Query_1) I try to find the leading space with a Left and a Mid function using: " ", "", and Null... but none of these criteria match.
However if you use Left(string, 1)... the empty space will post. So my question is: How can I programmatically eliminate these leading spaces when I can't state if there is a leading space or not?
