Query Returning Case-sensitive Results!?!
May 22, 2006
I am using the following:
SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS
WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));
As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.
The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.
If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.
The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.
View Replies
ADVERTISEMENT
Jul 12, 2013
I want to make this bit of code below case sensitive :
Code:
SQL_Entire = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "*';"
SQL_Beginning = "WHERE [" & ReplaceStringSelectedField & "] LIKE '" & SearchedString & "*';"
SQL_End = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "';"
View 14 Replies
View Related
May 2, 2005
Is there someone who can help me please???
I have a list with data like below
S123
s123
E456
e456
I would like to keep them separated when I do a grouping via a query, but Access does not seem to be open to case-sensitive characters. Who can help me???
Thanks!!
View 1 Replies
View Related
May 26, 2007
Is there any reason as to why, when searching a MDB file via ASP, that it would be case sensitive by default?
View 5 Replies
View Related
Mar 20, 2014
I'm trying to open a report and highlight certain text. So I have a textbox that takes the original field and replaces certain text (based on user input) in bold red. Here is my formula:
Code:
=Replace([Language],[txtKeyword],"<font color = ""red""><b>" & Upper([txtKeyword]) & "</b></font>")
It works fine, but the only problem is, I don't know how to make it case sensitive. So let's say the user input (txtKeyword) is "contract." If it's the beginning of a sentence, I want the C to remain capitalized, but if the C is not capitalized, I don't want it to show capitalized. Is there a way to do this without making the formula really long?
View 7 Replies
View Related
Jan 20, 2014
I'm new to access vba and I'm trying to create a login form. I have already my code to login but i want to validate the password in 'case sensitive' basis. Below is only what I've got so far.
Code:
Private Sub cmdLogin_Click() Dim login_validation As Variant login_validation = DLookup("Password", "tblLogin", "Username='" & Nz(txtUsername.Value, "") & "'") If Nz(login_validation, "") <> Nz([txtPassword].Value, "") Then MsgBox "Incorrect Password. Please try again." txtUsername.Value = "" txtPassword.Value = "" txtUsername.SetFocus Else MsgBox "Hi " & txtUsername.Value & "," & vbNewLine & vbNewLine & "you have successfully login!" DoCmd.OpenForm "frmMain" End If End Sub
View 5 Replies
View Related
Nov 19, 2014
I found a useful password protected form using input box and tried it successfully. However there are two issues. The password is not case sensitive and the password entered displays in the box. Is it possible to turn the password to case sensitive and the entered password is displayed as *s.
'Attached to On Click event of cmdOpenEmpForm
Dim strPasswd
strPasswd = InputBox("Enter Password", "Restricted Form")
'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.
[Code] ....
View 3 Replies
View Related
Jun 23, 2005
Hi,
I have a query with two table's in them. One is filled, the other is still empty.
Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results.
I am starting to think it is because one table is empty it returns nothing.
How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty.
One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
Anybody any idea?
Thanks
View 1 Replies
View Related
Jul 6, 2007
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Thanks!
View 3 Replies
View Related
Jul 3, 2006
Hi everyone,
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
Any ideas?
View 3 Replies
View Related
Mar 2, 2007
Hi all:
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
thanks:confused:
View 3 Replies
View Related
Sep 12, 2012
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies
View Related
May 7, 2014
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
View 11 Replies
View Related
Oct 30, 2013
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
View 7 Replies
View Related
Dec 30, 2014
I am building a prototype database and currently has two records in it (I know for certain they are not duplicates). Attached is the relationship and below is the query. The query keeps returning the correct results but twice. (Duplicate results.)
SELECT DISTINCT WeekdayName([Weekdaynumber],False,1) AS Weekday, [Master Data].Date,
[Master Data].Machine, [Master Data].Shift, [Master Data Query].[Master Data_Product Code],
[Master Data Query].[Cases/Pallet], [Master Data].[Expected Pallets to Produce],
[Master Data].[Actual Pallets Produced], [Master Data].[Damage (Units)],
[Code] .....
View 6 Replies
View Related
Jul 31, 2013
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
View 2 Replies
View Related
May 15, 2007
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
View 1 Replies
View Related
Aug 18, 2005
I have built a Project/Resource database for our department, and I am having an issue with 3 computers in the group. First, all of our computers are setup the same (as far as I can tell), however, there are 3 users that have the same issue...when selecting an item from a dropdown, it should populate the dropdown below it with a new list, based on the first dropdown. It is only on certain forms, and only happens on these three computers. If the users log into any other computers, it works fine. I know it's not alot to go on, but does this sound familiar to anyone? I'm at a complete loss!
View 5 Replies
View Related
Jan 17, 2005
I have built a query that should append to another table. Please see SQL below -
INSERT INTO tblMyBackupContent ( LoginName, Title, Type, Code, [Date Assigned], [Date Started], [Last Accessed], Progress, [Date Completed], [Time Spent (min)], Score, Result )
SELECT tblConentImport.LoginName, tblConentImport.Title, tblConentImport.Type, tblConentImport.Code, tblConentImport.[Date Assigned], tblConentImport.[Date Started], tblConentImport.[Last Accessed], tblConentImport.Progress, tblConentImport.[Date Completed], tblConentImport.[Time Spent (min)], tblConentImport.Score, tblConentImport.Result
FROM tblConentImport
WHERE (((tblConentImport.LoginName)="iderand"));
When I run this query, it shows that 41 records are going to be appended. I click on yes, but when I go into the table to view the appended records they do not show.
Please help me.
View 1 Replies
View Related
Mar 26, 2014
Here is my VBA Dlookup Code:
Code:
Public Sub test()
Dim frm As Form
Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
View 4 Replies
View Related
Jan 13, 2014
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
View 9 Replies
View Related
May 4, 2006
I have the following two querydefs. Here is the SQL;
qry1
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE DateDiff("n",[dtReading],[dtFind]) Between 1 And CInt([intMins]);
qry2
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE dtReading Between DateAdd("n",-1*[intMins],[dtFind])
And DateAdd("n",-1,[dtFind]);
[dtFind] and [intMins] are parameters.
I use the querydefs is VBA code as such
Dim db As DAO.Database
Dim rstDataSQL as DAO.Recordset
Dim qdfData As DAO.QueryDef
Dim strQdef As String
Set db = CurrentDb()
strQdef = "qry1" ‘or qry2
Set qdfData = db.QueryDefs(strQdef)
‘Set values of parameters
qdfData![dtFind] = dtDate
qdfData![intMins] = intMins
Set rstDataSQL = qdfData.OpenRecordset
qry2 executes significantly faster than qry1, but I am having issues getting the correct results.
If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.
However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will.
:mad:
I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.
The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.
I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.
Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.
What am I doing wrong?
:confused:
View 4 Replies
View Related
Oct 6, 2014
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
View 10 Replies
View Related
Jul 19, 2006
Hi there,
I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.
I'm selecting only records :
where the DeliveryCountry field is within the EU - works
where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work
where the Demo/Sale ID is 2 - works
I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.
The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).
One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?
Thanks in advance for your help!
Abby
SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS [Line Total], [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID
FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
ORDER BY Orders.ShipDate DESC;
View 5 Replies
View Related
Oct 25, 2013
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
View 3 Replies
View Related
Sep 11, 2014
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
View 14 Replies
View Related
