The below SQL gets it's criteria from a form but if the form has no Date1 and Date2 values I would like it to return all records.
I could not tinker it into submission. :confused:
Any pointers would be great.
SELECT [Central Western Region Database].[Date of Term], [Central Western Region Database].[Agent Number], [Central Western Region Database].[First Name], [Central Western Region Database].[Last Name], [Central Western Region Database].[District Name]
FROM [Central Western Region Database]
WHERE ((([Central Western Region Database].[Date of Term]) Between [Forms]![Compliance Lair Reporting]![Date1] And [Forms]![Compliance Lair Reporting]![Date2]) AND (([Central Western Region Database].Status)="inactive") AND (([Central Western Region Database].[Employee Type])="agent"))
ORDER BY [Central Western Region Database].[Date of Term];
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
What I am trying to say is if the received date is null make it 0 or use the date given, and if the complete date is null use 0 or use the complete date.
It then will perform the calculation that the Entry Date mod does.
If you want I can copy and past the Entry Date Mod. If that will help.
I have a query and it can be a null value if it is null I want it to return 1 if not null I want the calculation. This is what I have but it will not work.
Please help!!! repo on sitePercent of copy jobs not rejected:IIF([Rejected Job Percentage]=0,1,(1-[Rejected Job Percentage])
I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product Type Customer Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
simple question. i have an unbound textbox on a form that has its source as =[field1]/100*17.5 when field1 is empty on loading, the textbox shows nothing. when field1 has a value entered, it shows the value when field1 is reset, the textbox shows #Error ( field1 is cleared by code - me![field1].value = "" )
how do i suppress this to show nothing? i have tried =isnull([field1]="","",([Field1]/100*17.5) & =isnull([field1]/100*17.5)
im not great with sql statements ( if thats what you call them )
I am trying to insert a value in a field from an existing fields. I attached a sample of my database to show the query. In the NewProduct field I need "change of product" data to override the "product" data but if "change of product" is null/empty the "product" value should be in the NewProduct field. In other words the NewProduct field must take the value of either/or of the change of product or the product field. If
This is what I have so far: NewProduct: IIf([Change to Product]<>[Product],[Change to Product],"")
I am trying to figure out a formula using the iif and isnull function.
I am doing an on time report that looks at the Date Received, Date Required, Ship Date, Todays date and a few other things.
The formula I am trying to work with is DAYS WORK DONE IN: IIf(IsNull([SHIP_DATE]),[TODAY]-[REQUIRED]+1,[SHIP_DATE]-[RECEIVED]+1)
What I am trying to get it to do is if the ship is empty subtract the todays date from the required date and add 1. If the ship date has a date subtract the ship date from the received date and add 1.
When I run the query I just get #Error.
Is there an easier way to do this or do I just have an error in my code?
I've been searching the forum for a while, so hopefully I haven't missed the answer to this one. It may sound like a small problem, but it's driving me mad.
I have a series of TextBoxes on a Form. Two of these are named 'Department' and 'Shift'. Originally, the ControlSource for the former was 'Department' and for the latter was 'Shift', both from the same source table. All data was being displayed without any problems (any null values resulting in an empty field).
I altered the ControlSource on each to read: =IIf(IsNull([Department]),"Unknown",[Department]) and =IIf(IsNull([Shift]),"Unknown",[Shift])
The Shift field works as I'd hoped it would, but the Department field displays '#Error', whether there is a value or not.
I've tried deleting the problematic TextBox, then recreating it by copying and pasting from one that works (in case there was some setting I was missing). In this case, the only difference between the two is the source field, but given that using the source on it's own doesn't present any problems, I'm at a loss.
This same problem occurs on a small number of other Forms, as well as on certain Reports.
I had a little problem with delete query. I would like to delete the records by checking two fields if both fields are empty. I mean if a records had both these fields are empty only. If one of the field has data , the record should stay there.. Is there any way I can do this?
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this:IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model])this is not working. it is still returning the query blank. if i put valid critera in the dropdown it comes back Fine.how do i make is so if a field is blank it will return it as null or not even there.Note: also tried this;Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Nullworks, But comes back as too complex after a few searches and when i open the query there is a million or's in there. so that wont work.
I am using a frame on form to get report. Below is the code used to filter report.
Select Case Me.fraReportType.Value Case 1 strReportType = "Like '*'" Case 2 strReportType = " Is Null" Case 3 strReportType = " Is Not Null" End Select
The second and Third case is working fine. While the first condition is not working. This filter is on date field. There are three possibilities:
1. If we need all data 2. If we need is null data 3. If we need is not null data
How I can get the first condition using my code mentioned above.
I have a form I am using to either select an Equipment ID or pull all records if no id is selected. My code in the query looks like the following:
([Forms]![frmWOCommentRpt]![cboEquipID]) Or Like "**"
When I run this, however, it only does the Or part. If I select an equipment ID on the form, it doesn't just pull that. I'm sure this is a simple fix, but I can't see it. Any ideas?
I have a macro that runs 2 queries, they both have criteria that needs to be passed over. how can i do this? I currently have it prompt but how do i get it to pull from the form the macro trigger is in? I also have a report that runs from a query in that macro also. i dont' want the user to have to enter anything since the critereia is in the form. thanx
I am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
Not sure if this is in the right forum. :confused:
But here goes, my question is;
Is it possible to update the criteria by using a form?
What we are looking to accomplish, is a report that prints out labels in relation to the customers that are chosen in the query. What we want the user to be able to enter multiple Company ID's on this form, which are then picked up by the query and the companies name and address printed out via the report.
The printing out of the report from the form we have done.
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
I am working on a database which has many queries that count different statistics of clients (race,gender,ect) during specific date ranges. I would like to set up a form that you could go in and enter a date range into a text box and it would update the criteria (date range) for each of the queries. How would this be possible? I have looked at some past threads similar to this but they didn't help.
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
Hey all this is my first post so thanks in advance for any help you can give me.
I am trying to use multiple checkboxes on a form to try and make a select query, when the box is checked the data is queried when unchecked it is not, sounds simple enough, here is my problem.
I set the query criteria with an expression like this: [Forms]![frmMainLookup]![Check Box Alarm Number]="-1" As long as the checkbos is checked everything seems to work fine. If I uncheck the checkbox then none of the data is shows up. I still get the columns to show up just no data.
I am sure there is something simple I am missing but the Force is not with me today.