Select Statement Searching On Date Criteria
Feb 23, 2006
Hello everyone! :)
I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!
Ok I have this table called CustomerComments. In it are the following columns
CustID
Comment
CommentDate
For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)
Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.
Does anyone know how I could just get that top line per custID rather than all rows returned?
Thanks for reading this... hope it makes sense!
:)
View Replies
ADVERTISEMENT
Jun 14, 2013
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
View 13 Replies
View Related
Aug 19, 2011
I'm trying to create a button that posts an order and before it post certain criterias need to be meet. One of them is a date needs to be selected in the order, if not a popup show to explain. The trouble I'm having is referring to todays date in the VBA code. I've been reading and trying different things but not getting anywhere, hopefully someone can fill me in.
This is the code I'm working with.
If [PayOrderMoneyReceivedSubform].[Form]![OrderDate] = TodaysDate Then
dosomething
else
donothing
end if
The part I'm having trouble with is TodaysDate, what should I be putting there?
View 2 Replies
View Related
May 21, 2013
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
View 1 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
Feb 14, 2005
Hi i just need to correct the syntax here. I need to be albe to search a field with an id entered by the user & where the field Lock = "Unlocked"
Is this statement correct, because i still get an error:
Dim SQLString, ratesid
ratesid = Request.Form("ratesref")
SQLString = "Select * From specialrates Where RatesRef = "& ratesid &" AND Lock = &"Unlocked"&"
please advise and thanks
fas
View 1 Replies
View Related
Jul 1, 2005
Hello,
Im having trouble with Criteria for a query. I have my criteria like this:
Like "*" & [Forms]![NewSearch]![txtSearch2] & "*"
The only problem is, if im trying to search for a last name such as "Aber" the search results come up with not only Aber but also anything that contains Aber, such as the last name Singaber.
How can I make the criteria search from the beginning of the Name, and not anywhere in between?
Please help, going to drive myself nuts :)
Thanks in advance for any help you may be able to provide.
Mateo
View 2 Replies
View Related
Oct 31, 2005
hi. i'm a novice with Access so any explantions need to be pretty simple. I'm trying to create a query that searches 2 fields when 1 word is entered into a parameter value box..... would any1 hav a sugestion. please post a reply if you don't understant what i'm trying to achieve.
Thanks :)
View 1 Replies
View Related
Feb 23, 2005
I have a button with this code attached
Dim SearchStr2 As String
SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _
And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code]
DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal
Forms!FRMASSESMENTHEAD.Filter = SearchStr
Forms!FRMASSESMENTHEAD.FilterOn = True
If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.
It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying
Run-time error '13':
Type mismatch
hope this makes sense and can anyone help :confused:
View 1 Replies
View Related
Sep 19, 2007
i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all.
i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know.
the user then clicks th button to open the query
in the query i have set the criteria for the fields to equall the related combo bo in the form.
however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.
an example
on the table tblFault there is the record
FaultID; 7
Computer id; 2
Name; c
Room-ID; it1
Date Of Fault; 05/06/07
on the form the user enters the following values into the combobox
FaultID; 7
ComputerID;
Nme; c
Room-ID; it1
Date of Fault 05/06/07
the query returns no records insead of the record above.
this is because it is looking for a record in the table where the Computer-ID field contains a null value.
is ther a way to ignore the chriteria if the combobox contains a null value????
i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .
View 6 Replies
View Related
Jan 12, 2007
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID
Employee Name
Date 1
Date 2
Date 3
Date 4
Date 5
Date 6
Date 7
....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
View 14 Replies
View Related
Aug 2, 2013
I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.
The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:
Code:
Public Function RemoveFirstChar(RemFstChar As String) As String
Dim TempString As String
TempString = RemFstChar
If Left(RemFstChar, 1) = "0" Then
If Len(RemFstChar) > 1 Then
TempString = Right(RemFstChar, Len(RemFstChar) - 1)
End If
End If
[code]....
View 14 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
Nov 12, 2005
I have a date base where i am looking for all records which are not scanned every time an order is entered a date is generated. I then want to find all the ones which have not scanned for today. (i know i can do this by a query i.e [Date] =date() .)
Now the problem is that if nothing is entered in today for example i then want to find the next day something was entered.
I am a novice with access and i think there must be some coding which i need to create in a module prehaps but i am unsure. Could any one assist??
View 6 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
Nov 26, 2007
Hi,
i have a START date "DD/MM/YYYY" <-- with 3 combo boxes "cboStartDD", "cboStartMM", "cboStartYYYY".
i have a END date "DD/MM/YYYY" <-- with 3 combo boxes "cboEndDD", "cboEndMM", "cboEndYYYY".
i have a Search command buttom.
upon a "search" button is click,
how can i search the dates in the table with "tblDate" (column) that matches the date ranges from the START date to the END date?
by using sql statement...
thank you.
View 5 Replies
View Related
Dec 2, 2007
Lets say i have records that contain the following dates
09/06/07
14/06/07
20/06/07
31/06/07
14/07/07
And i wanted to know what records contain anything between 09/06/07 and 31/06/07 so it would show the records that have this in the date field
09/06/07
14/06/07
20/06/07
31/06/07
What would be the best way to do this
Thanks in advance
View 10 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
