Using Parameter In Select Statement (SQL In Access)

Jun 3, 2012

how we can use a Parameter in Select statement (SQL view)?

( actually I want to get a number from user and use it for selecting some random records)

View Replies


ADVERTISEMENT

Select Statement Help-Translate MS SQL To Access

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

Queries :: Update Statement For A OUTER JOIN Select Statement

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

Queries :: Parameter Value Against A If Statement

May 15, 2013

This statement returns a "date" each time the event occurs. I want to have a parameter between [Start Date] and [End Date] so I can pull events for a specified date range. I have tried this "Expr [EventDate]" Between [start date] And [end date] but it returns "null" results. I am using access 2010

EventDate: IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate]))

View 2 Replies View Related

Fixed Parameter Value In SQL Statement

Sep 11, 2015

In Access, can I hard code a parameter value to suppress the prompt? I'm querying a query that's made up of sub queries, which all require the same parameter.

I'd like to define the parameter value in my SQL statement so there is no prompt. Can this be done directly in my Access query?

Again, the parameter isn't any part of my main query, it's part of the sub-queries that make up the master query if that makes any sense.

View 1 Replies View Related

SELECT Statement

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

Help With SELECT Statement

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

Help With Select Statement

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

Need Help With Select Statement

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

Forms :: Filter Statement Asks For Parameter Entry

Aug 22, 2014

I have a bound form, in its heading I have a combo box which lists three choices. Basically I want to filter all my purchase records by checking a field PUOrderNb (Example: PO200100025) against the choice made in the combo box, namely DE (for demand), PO (for Purchase order) etc. On the after update event of the combo box, I have the following code:

Dim strfilter As String
strfilter = " left ([PuOrderNb],2) like " & cboFilter.Column(1)
Me.Filter = strfilter
Me.FilterOn = True

When I make a choice in the combo box, I get a window asking me to enter a parameter value and it lists the value of the combo box choice as a sort of a title just above the white input fame.When I type in PO for instance, the program does correctly filter all order numbers starting with PO, but the whole point of having a combo box is not to have to type anything.The other odd thing is, when I change the choice in the combo box, after my first choice, I do not get this parameter question but nothing happens as to filtering. The first choice remains active.

View 3 Replies View Related

IIf Statement Multi Select

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

SQL Select Statement Problem

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

Problem With The SELECT TOP X Statement

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

CASE Statement In SELECT

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

Help Needed For Using The Select Statement

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

Queries :: Select Statement To Max Value

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

Parameter Query - Allow User To Select All

Oct 10, 2006

I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?

View 14 Replies View Related

Query With Parameter To Select Certain Fields

Jan 12, 2008

Hi, I need help in creating a parameterized query.

I need to show only certain fields depending on the value of the parameters.
For example, I have a table with fields: ID, Name, Phone Number, Address, DOB.
The parameter can only be value of A, B or C.
If the user input A as the parameter in the query, only field ID, and Name will be shown
If the user input B as the parameter in the query, field ID, Name and Phone Number will be shown.
etc.

Is there any way to do this, rather than to separate the query into 3 queries for each of the parameters? Thank you.

View 1 Replies View Related

Parameter In Select Query Of Recordset?

Jun 25, 2005

hi ,

i want to open a recordset, in its 'where' clause i want to pass the value which i entered in Form textbox? is this possible ?


COde:
rs.open "select * from table1 where id=form.textbox",con

i have written general code above.
when i run my application ..it gives error "Control which is focus can only be reference"
how can i pass my value to select query ?

thanks

View 2 Replies View Related

Not The Best At Case Select Statement Writing...

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

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 4 Replies View Related

Select Statement For Multiple States

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

Select Case Statement - General Q

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

Insert A Select Statement In A Text Box

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

Select Statement With Like And A Session Variable

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

Modules & VBA :: INSERT INTO SELECT Statement

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







Copyrights 2005-15 www.BigResource.com, All rights reserved