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
This is what I currently have
Maxvalue = "SELECT MAX[Record Num]FROM Joblog"
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
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'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.
My SQL:
SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;
This returns : Syntax error (missing operator) in query expression 'IF(( etc.
After clicking "OK", access highlights AS in the statement. I'm not sure how to deal with the NULL value or fix the error?
what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.
Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
.fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration
ORDER BY t_asset_personnel.LAST_NAME;
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 a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.
In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".
how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?
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?
Thanks in advance.
help with select statement
I have this select statement need help to fix it up....
lsupervisor1 = "SELECT Supervisors.LicenseNumber " & _
"FROM Supervisors " & _
"WHERE ('" & Supervisors.LastName & ", " & Supervisors.FirstName & " (" & Supervisors.Area & ")" & "') = '" & Supervisor1 & "' "
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 have two tables that look more or less like this:
Member:
idMember Name Surname
1 John Watts
2 Pete Pletz
3 Carl Bekker
History
idHistory idMember DataA DataB
1 2 AAAAAAAA BBBBBBBB
All I want is an access select statement that would show me the members that does not have any History entries, thus, members 1 and 3
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
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?
View 2 Replies View RelatedHi, 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?
Thanks Kevin ....
Does anyone know if its possible to perform a CASE Statement in a SELECT statement in Access and if so what the syntax is? Thanks in advance.
View 9 Replies View RelatedHi 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
Can someone simplify my feeble scratching at a Case Select statement, please?
I have two fields in a table (True/False): IsActive and IsDefault
I would like to convert some If...Else...Then statements to a Case Select statement which details what should be done when any of the possible states of the two fields exist.
Semi-Psuedo example in the (subform) Form_Current Event:
With Me
If !IsActive = True And !IsDefault = False Then
Do Something on the Parent form
ElseIf !IsActive = True And !IsDefault = False Then
Do Something Else on the Parent form
ElseIf !IsActive = True And !IsDefault = True Then
Do Something Else Again on the Parent form
Else
Do Something Entirely Different
End If
End With
What blows my mind is the Select Case Expression bit. I can't figure out how to write this. Any help is greatly appreciated.
Hi!
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
How can I do that in Access Query statement?
Please advise.
Aijun.
Hello,
First off thanks for this forum it has gotten me this far. 2nd I have a question on how the best way to accomplish this. So I have a table that has customer info in it, Account #, Name, city, state, Zip. I have a form that allows users to type in fields to query for particular info. My select statement is below.
Private Sub cmdSearch_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' And"
End If
Everything works but I can only search for one state. So now I want to search for two or more states. I've added multiple text fields on my form and have tried approaching it that way. Unfortunily if I add txtCSOST2 for example then add
If Not IsNull(Me.txtCSOST2) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST2 & "*' And"
End If
tblCONSOLIDATED.STATE has to contain both state codes. If I put an Or instead of And I get both states and all other search critera is ignored. Basically I need to be able to query by two or more states and it still be an and I guess. For example we may have a Rep A (Me.txtCSOSSM) that goes into TX & OK but Rep B also has a peice of TX. If I'm looking all the accounts that are in TX & OK and are Rep A and I use the below code I get all of the TX & OK accounts as well as Rep A accounts.
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' Or"
End If
If Not IsNull(Me.txtCSOST2) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST2 & "*' Or"
End If
I know that has to be simple I just wrap my brain around it. Let me know if this needs further explaination.
Thanks in advance.
Bryan
If I write a select case statement for a field X i.e. 6 to 7 for example will this:1. Select any fields X where the number is greater than or equal to 6 but less than (and not including) 7B. Select any fields X where the number is greater than or equal to 6 but includes decimels of 7 i.e. 7.5 ORC. Select any fields X where the number is greater than or equal to 6 but includes but literally only includes cases where the the whole number 7 appears>I only ask because in an example in a book I am looking at says:Case 10000 to 20000......Case 20001 to 30000I would have through the word "to" means until but not including but in this case the field that equals 20000 would never be selected?
View 14 Replies View Relatedhi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:
SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);
it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.
:o
Can some one please help with my SQL statement. Having trouble with getting the like statement to work with my session variable.
Here's the code I am using thus far.
<% If Session("PhoneN") Then %>
<%
dim objcn1,objrst2,sql4
set objcn1 = Server.CreateObject("ADODB.Connection")
set objrst2 = Server.CreateObject("ADODB.RecordSet")
sql4 = "SELECT * FROM [Bill To Customers] WHERE CompanyPhone LIKE '%' & PhoneN & '%' "
Th following sql select statement works in mssql server but gives an error in Access. Says that there is a "syntax error in FROM clause". Can anyone translate this into access.
SELECT L1.* from log as L1
JOIN (SELECT [vehicle number],Max(Date+' '+time) as maxdate FROM log GROUP BY [vehicle number]) AS L2
ON L1.[vehicle number] = L2.[vehicle number] and L2.maxdate = L1.date+' '+L1.time
data sample
unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30
Select results in ms sql server
00100 01/14/2007 11:00
00200 01/14/2007 11:30
00300 01/13/2007 13:45
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.