General :: Query Criteria - Data In Fields Are Numbers
Nov 7, 2014
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix, IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias FROM tblItemIDCrossReference WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
A while back, I asked about the now-removed function of exporting Reports to Excel. I was told that there's no way to do so in Access 2007 and that the best best would be to export the underlying Query.
I did so and I found out that the query was showing the record number of some data pulled in via combo box (which gets data from a separate table) instead of the data itself. Logically, I can understand why that would happen, but is there a way to change that so that the intended data, not the record number, is shown?
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).
The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.
I am endeavouring to filter a form based on the position of an individual. I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Hello, I am a new member...thanks for any help anyone can provide. I have a table with a column in which the fields will come in with various lengths and combinations of characters. I need to look up certain values from this field, and assign a value to another field based on that. For example if field1 begins with an "H*", then field2 will equal "INS".
My problem is that I need to write a statement saying something like if field1 starts with "H" and is followed by only numbers for any length, then assign field2 "blank"
so I cant use "H*" because that includes letters...and Ive tried criteria like "H[!a-z]*" but cant get anything to work(i know the last example only looks at the space after H) Does anyone have any ideas?
I have a table [maintable] that holds print records, with the following fields :
id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.
I want to create a query that will show the following:
Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this
"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..
So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)
here are all of the criteria fields i want:
1) - papersize=A4 2) - papersize=A4 and Color=True 3) - papersize=A4 and Color=False 4) - papersize=A4 and Duplex=True 5) - papersize=A4 and Duplex=False 6) - papersize=A4 and Color=True and Duplex=True 7) - papersize=A4 and Color=True and Duplex=False 8) - papersize=A4 and Color=False and Duplex=True 9) - papersize=A4 and Color=False and Duplex=False
I have a large application that was built 8 years ago and the user now wants to remove some of the data fields. Is there an efficient way to figure out what objects would be affected if we delete these columns?
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.
Is it possible in access 2010 to create a a query that only shows a text box if a combo box criteria is met?
Example: On a form there is a combobox (Result) that can either be negative or positive. If the value is Negative then a query is already setup that populates a mailmerge with some text. If Results=Positive can a query be created that will show the textbox (Data). It only needs to show if the positive criteria is met.
I have a database structure with a series of queries which are all inter-linked.
I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
"Dynamically search multiple fields" using my own customer data.
I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.
Right now the data is in excel so I have a completely blank page to fill.
I've recently moved up to Access 2013 (from Access 2010) and everything went well, until one day my splash screen was blank and the program had empty fields...after seeing that all the links to the BE were OK, I re-booted and everything worked fine....until the next day the same thing happened...... The program randomly falls over about once or twice a week and I have to re-boot to cure it.....
My splash screen has a standard dlookup ( [=DLookUp("Banner","tblCompanyInfo")] ) to display the active company name...and sometimes its populated and other times its not..... if its not populated then loads of other fields on the forms are also blank - even if they point to valid data in the database!
I run Win7 (both 32 and 64bit) with Access 2013 32bit on three PC's - same problem on all!
