Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View RepliesI have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.
On the same criteria row for these fields, I have
Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])
Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])
Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])
qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.
This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
I have a field which contains "01501 PRIKLJ. VRHNIKA - LJ / Stevno mesto" .
1.) I would like to extract just text part from that field, how? *number is always on beginning but different 1-10000
2.) Optional, how could I delete in this new only text field part of text after / ...*some rows have some dont /
I am trying to search for specific text in a field and returns its value. For instance some of string includes Sub, L2L, Temp, Model, or MTM and I would like a query to return these values if found.
View 2 Replies View RelatedI'm trying to obtain all of the information in a text field for a query.
The user entered information and used a return (enter key) to enter information on a new line within the same field. The information after the return is not captured in the field in the query. Is there anyway I can capture all of this info?
i am trying to make a query which will give my the result as if Sum of filed is not null.. Criteria 1) (Field Name)in Category "Asset ID Diff" And "Cusip Change" should select first then
2) Fund and Amt
if Fund Amt is not zero than data will comes in query
CategoryFundAmt
Asset ID DiffADF01000
Asset ID DiffADF0-1000
Cusip ChangeADGH2000
Cusip ChangeADGH-2000
Asset ID DiffADF05000
in above data you can see ADF0 funds total is 5000 which is not null or 0.so query will give me the result as.Asset ID DiffADF05000
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.
I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.
Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.
What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.
I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.
How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?
View 1 Replies View RelatedI have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I have an expression in my query and i'd like to return a value of 0 is the expression yields a null value. Here is the expression i have:
IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0)
I'm getting an error "the expression you entered has a function containing the wrong number of arguments". How can i resolve this?
I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.
I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).
I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?
Any help would be appreciated.
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
Database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.
View 5 Replies View RelatedAll. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
View 2 Replies View RelatedI need to add a Yes/No field to a table. I know I can do this in design view. Next I want to loop through the table and set it to Yes if certain fields are null. What I need help with is the VBA looping part and setting the boolean field to Yes if the fields being checked are null.
View 3 Replies View RelatedAccess 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code:
'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record
Private Sub cmdCloseBANANA_Click()
If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
[Code] .....
I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.
The subfrmClientOffers is in datasheet view.
While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:
Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn
Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.
Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?
One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.
I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.
I need to validate if a Field in my table is NULL, details of which will not be included in my report.
I attached sample report wherein the data is sorted by EXPIRATION DATE, first rows displayed the data of NULL EXPIRATION DATE FIELD ...