How To Format A Field In The Select Statement?
Sep 1, 2005
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.
View Replies
ADVERTISEMENT
Nov 28, 2013
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?
View 4 Replies
View Related
Jun 11, 2015
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;
View 7 Replies
View Related
Feb 12, 2014
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?
View 2 Replies
View Related
Jun 20, 2013
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]...
View 2 Replies
View Related
Oct 27, 2005
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.
View 1 Replies
View Related
Jan 15, 2005
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.
View 1 Replies
View Related
Jan 11, 2005
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
View 2 Replies
View Related
Jun 28, 2006
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
View 1 Replies
View Related
Jul 13, 2005
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 Related
Jan 9, 2006
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 ?
View 8 Replies
View Related
Mar 9, 2007
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 ....
View 1 Replies
View Related
Mar 21, 2007
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 Related
Oct 16, 2004
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
View 5 Replies
View Related
Jul 12, 2013
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"
View 2 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Mar 3, 2013
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:
Currentdb.EXECUTE "INSERT INTO Tasks (TaskID, MyNumber)
VALUES ("Tsk123", Var1 - Var2)",
the result was always something 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.
View 3 Replies
View Related
May 14, 2007
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.
View 6 Replies
View Related
Mar 14, 2006
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
View 3 Replies
View Related
Feb 6, 2008
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 Related
Aug 1, 2006
hi 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
View 4 Replies
View Related
Oct 28, 2004
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 & '%' "
View 3 Replies
View Related
Jan 18, 2007
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
View 1 Replies
View Related
Mar 4, 2014
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.
View 4 Replies
View Related
Sep 29, 2014
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...
View 3 Replies
View Related
Aug 23, 2012
using the Select statement on a form;I have it looking up 3 fields, Ordered by the first and bound by the first.the other 2 are being stored in different fields.What is weird is that I need to have the first field entered twice in order for it to show up, which means I'm looking up 4 fields.When I go to store the selection after, only 2 are available... know this confusing; here is the Select Statement;
SELECT [ITEM CODE1].[ItemCode],[ItemCode],[Customer],[Project Description] FROM [ITEM CODE1] ORDER BY [ITEM CODE1].[ItemCode] ;
If I don't type ItemCode twice, it shows Project Description in the drop down box. When I use the code above, only ItemCode and Customer are available to store in other fields;
Me.Customer = Me.ComID.Column(2) which actually stores Customer (should be column 3)
Column(3) and Column(4) won't do anything.
View 3 Replies
View Related
