Queries :: Using OR In A Query From Text Box
Jul 8, 2015
I have a form with a text box and a query that has a criteria or [FORMS]![ThisForm]![ThistextBox]
Is i enter say "apples" in the text box then the query shows be all records with the criteria of apples. If i enter "oranges" it shows me all records with a criteria of Oranges, however if i enter "Apples" OR "Oranges" it return nothing
Why I can use multiple items in a criteria.
I have tried IN("Apples","Oranges") too and nothing works.
View Replies
ADVERTISEMENT
May 28, 2013
I have a linked table to Access 2010 which contains tracking numbers for shipments.When I link the spreadsheet to the Table, the FedEx tracking numbers are changed to for example - 9.813842152e+014 instead of 981384215196229. Is there anyway to change these back to its original format? I was thinking of creating a query where I could use Conversion Function, but it kept giving me error. Please note that both the UPS Tracking and FedEx tracking are in the same field.
View 8 Replies
View Related
Jun 19, 2013
I have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit
Table: Master Log Data
Criteria: 1 or 2 or 3
Works fine!
Field: Unit
Table: Master Log Data
Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
View 1 Replies
View Related
May 7, 2014
I was trying to see if there is a way to convert text to a hyperlink in a query.
Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.
One of my columns in the query looks like this:
Code:
Notes Link: "notes://pncpgha66/852570CE0056FEF3/0BFCC83A5C11D7C3852576CD0072ED3E/" & [NoteUNID]
I don't see a way to convert that whole part to a hyperlink though. Like I could with a date and text. Cdate().
View 1 Replies
View Related
Feb 26, 2014
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
View 3 Replies
View Related
Sep 3, 2013
I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
View 3 Replies
View Related
Jun 15, 2013
I'm trying to export query results to a text file and it keeps giving me a "too few" parameters error.
View 7 Replies
View Related
Aug 31, 2014
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code:
Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*"
frmStudentDetails is the form name.
txtSearchFirstName is the search box's name (in the footer).
The AfterUpdate event for txtSearchFirstName is:
Code:
Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.
View 12 Replies
View Related
May 18, 2013
I have a combo box that gathers data based on a query. Is it possible to add a line of text to be displaed every time i pull down the box?
For example I have:
Data1
Data2
Data3
I would like to have:
Not In List (the added txt)
Data1
Data2
Data3
View 2 Replies
View Related
Dec 11, 2013
I have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.
I tried using this as criteria on the "MonthGroupPMC" field:
Code:
Format([Forms]![Report Runner]![Date], "yyyy-mm")
but the results came up blank.
how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?
View 5 Replies
View Related
May 13, 2014
I have a text field that contains a comma delimited list of numbers.
I am trying to read one of these numbers from OLEDB through to an access database.
I am trying to do the following:
SELECT SPLIT(CSVData, ValueNo) as ReturnValue FROM MyCSVDataTable
Where CSVData is a split field containing values like "123,234,345,..." and ValueNo is a value to identify which one i want.
Any way of achieving this using the standard SQL functions. I am reading the data via OleDB and therefore cannot use a user defined VBA function?
View 1 Replies
View Related
Mar 26, 2015
how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
View 1 Replies
View Related
Apr 15, 2014
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
View 5 Replies
View Related
Jul 23, 2015
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working;
2. a combo box to filter events by country name - this is working;
3. a combo box to filter events by event's keyword (category) - this is NOT working;
4. a combo box to filter events by year - this is NOT working
View 2 Replies
View Related
Dec 12, 2014
Am not getting a value from a form text box when using in the criteria line in my query. Am referencing like [Forms]![FormName]![FormLabelName]. If I copy the data in the form and paste it into the query, it works fine, but if I just reference the form, I get no results.
View 13 Replies
View Related
Nov 8, 2013
Is there a way of using a text field value as the heading for query column?
ie change...
Column header: FieldInATable
to
[form1].[field2]: FieldInATable?
View 1 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
Aug 18, 2014
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
View 7 Replies
View Related
Jun 29, 2015
I have a link to a "csv" file. I then run a query to exclude some rows.
Then I need to export the query result as a text file with quotes (as text qualifier) and semicolon (as delimiter). This is not a problem. The problem is, that I only need the text qualifer for the data in row 2+ and not the headings.
The data export should look like this:
ID;Contract;Date
"1";"4700001360";"20150618"
How can I specify this export layout in the export wizard?
View 5 Replies
View Related
Aug 13, 2013
I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below
I have created a query that finds information from 'all information' using
Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"
This is working on all text boxes, It only half works on the combo box's when I use
Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"
If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.
View 11 Replies
View Related
Nov 13, 2013
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code:
DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
View 5 Replies
View Related
Sep 12, 2013
Error I'm getting 'The expression you entered contains invalid syntax, or you need to enclose your text data in quotes.
Code:
If Dcount(Nz([StatFlag]),[Books],[StatFlag]="W") >0 , ((Nz([StatFlag])) ="W" , (((Nz([StatFlag]))<>"R" And (Nz([StatFlag]))<>"M" And (Nz([StatFlag]))<>"H" And (Nz([StatFlag]))<>"P") AND ((Authors.Status)="A"))
View 3 Replies
View Related
May 7, 2014
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value)
SELECT
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
FROM
supply_points s
, codes c
WHERE
s.code = c.code
and s.id is not null
and s.code = 1075
and s.month >=[forms]![main]![gppstart ]
group by
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
pivot
s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
Is this a common phenomena in Access 2010?
View 3 Replies
View Related
Dec 18, 2013
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
View 2 Replies
View Related
Aug 14, 2014
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
View 5 Replies
View Related
Aug 7, 2015
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
View 8 Replies
View Related
