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 Null
works, 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.
now i want to split it up. i want to count all the records for each month. my field name is datein_now but its just a normal textfield and not a date field. example in field: 05/02/02 10:24:31 AM. also got a datein textfield:05/02/02 i had Count(*) where datein_now between 05/05/01 and 05/05/31
When I enter a date into the [Return Text Date] field on my table and then select a value from the [Upgrade Fee] combo box followed by a selection from the [Upgrade Investment Class] combo box i want the value (or Date) in my Eligibility Date field to be amended by the given amount.
I have an expression, listed below, that i want to repeat (copy + Paste would be good) and amend the values. I have entered 2 and that seems to be all it will allow. I want to have 30 possible permutations to the fields 0 - 300 (in 50 intervals) by 5 (1,2,3,4 & 5)
The Code works below but I cant have anymore code in the expression builder
IDEAS please people.......
=IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=1,[Return Text Date]+180,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=2,[Return Text Date]+150,""))IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=3,[Return Text Date]+120,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=4,[Return Text Date]+90,""))
Hi all, i have a field that uses Date/Time as the data type and what i want to do is to create records that only have the time between the hours of 9:00am and 9:15pm.... i think i need to use the expression builder for this, but im not really sure on the expression to use.. any ideas ???:confused:
Hi, I am fairly new to Microsoft Access and can't get to grips with the expression builder. I have several fields where I need to add validation rules and no matter what I try I can't seem to get it right. E.g I'm trying to set a validatio rule for only allowing locations into a certain field. Could someone please give me some help or point me in the direction of a good guide?
Hi, i am making a stock control program in Access. I have designed several tables, forms and queries etc. but have come stuck at the point where i want to create a button so when i click it, it automatically changes the stock level of all the products that are low in stock, to their "target stock level".
I am confused about how to do this. I tried making a button and going into the expression builder and putting:
Should you write an expression the criteria field in a query and use IIF, an interesting little bug appears that I have not managed to get around. Should you want the expression to return a range as the criteria (i.e. Between ... And ... ) it can't do it because IIF tries to evaluate the range before it passes the result back to the query. I need it to actually pass the Between And to the query to use as it's criteria. It can't do this, so it passes back a null and of course the query fails to produce any meaningful result.
I need to do this as I have two date text boxes in a form so that people can search through specific dates, but in the table these date fields can be blank. (Basically it's a Date Completed field, so if the job hasn't been completed, the date is left as Null) So i need users to be able to leave both text boxes blank so that the resulting query will display the Null values as well. If they leave one blank, it auto sets itself to the a minimum/maximum date. When both boxes are blank i need to return nothing so the criteria is effectively blank or = Is Null And Is Not Null to display all records.
Example of WIP
IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True And IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,#15/01/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True,Between #01/01/2007# And #01/04/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,Between [Forms]![frmListImgChng]![txtDateComp2] And #31/12/2030#,Between [Forms]![frmListImgChng]![txtDateComp1] And [Forms]![frmListImgChng]![txtDateComp2])))
The red bits of code are test values. The first one passes back to the query as it is not a range, the second does not as it is.
Now I have been working for a few months with ACCESS but one thing that keeps giving me trouble and that is the "Expression Builder". I look at the Microsoft Help and it is not good in explaining this part of ACCESS. I have Access books and they are not good with explaining this aspect of Acess. Is there anyone with a good source to point me in the direction to learning the Expression Builder better then I know now?
This seems so simple and it probably is, I've probably been staring at my database for too long doing too many other things but here goes:
I want to create thank you letters for a list of people. I've made my query and gotten all their info together but now I can't make a report for the letter. I can make my text box but after that nothing seems to work out right. I'm not that good with VB code so I have been using Expression Builder which follows logic statements and not real code. I can't get even the address showing in one text box with 3 lines. Once I get that far I can get the remainder of my letter created because its all the same thing but I really need some help to get beyone the address!
Hi, I need some help using functions in queries. I can calculate simple arithmetic in calculated fields, but fail when using functions. For example I have attached a doc file showing a query. One table displays record ids, the other four have data for consecutive dates for each record. I cannot make the query calculate the standard deviation of the data for each record. What expression would I use, I know it is stdevp but when i try and create the expression i get error messages galore.
Hi, can anyone help me? I'm using an access database to collect information and pull reports. One of the fields in one of the tables contains dates and time. In my query I would like to be able to state that records created after 17:00 and before 08:00 of the next day, get counted in next day reports. (i.e. Record 1 created 26.05.05 at 18:01 would show in my report as coming in on 27.05.05 at 08:00 am). Thanks,
I need to write an expession in the Expression Builder but I cant seem to get it correct. Here is what I want to do:
I have 2 forms and 1 query.
I want the criteria for my query to take the data from the loaded form, ie if form1 is loaded then take the value in field1/form1 as criteria, otherwise, if form2 is loaded then take the value in field2/form2 as criteria.
Form1 and form2 are never loaded at the same time.
Can someone give me a suggestion? Thanx for your help
I have built a first time simple (2 table) db to track office addresses and related contact names, My address table contains both address1 and address2 fields that I use for Street and Mailing. I'm trying to build a query that will first look at address2 fields (mailing) and if blank populate with data from address1 fields (Street). Thanks for any help and Happy New Year.
Hi. I am using Expression Builder to total 3 columns for a query.
Total: [BaseTotal]+[ClickTotal]+[Adj Total]
The [Adj Total] is sometimes empty, and when this happens, Total is empty even though there are values for [BaseTotal] & [ClickTotal]. How can I make the Total show up regardless of whether any of the three items used to calculate it are Null?
having a bit of trouble trying to create an expression. I basically want to let field1 = field2 / field3 in a particular table.
In the design view of the table, I have highlighted the field, and in the default value of the field properties, I have clicked on the expression builder. I was expecting to see all of my tables with all of the fields in it, in the bottom left box of the expression builder, but all that is there is: Functions, constants, operators.
I tried to write the expression anyway: =[AmountUsed]/[AmountIssued]
but when I try to save the table, it says that it doesn't recognise table 'AmountUsed' or Default Value.
Hi - I am new to this site, and have searched for an answer to my question but, can't find anything that has worked. I am trying to use Access to figure Grade Point Averages, what I need it to do is add up the GPA points and the Potential Earned Credit amounts, for each student, starting over when it hits a new student number. At the end of each student number I need those two fields added with totals, and another field to divide the total GPA Points and the Total Potential Earned Credit Hours to give me the GPA. What I have tried - Using Report Wizard, grouped by ID#, did the sort order and summary by GPA Points and Earned Credit Hours- Summary Options set to SUM. Once I get it done, it does add up the GPA Points and the Credit Hours - so I go into the Design View and try to set up another box using the Expression Builder to divide the GPA Points and Credit Hours - but, have had no luck in getting it to show up. I must be missing something that makes it show up but, I cannot figure out what it is. There is probably another way to go about doing this - any suggestions or help I would really really appreciate.
When I use the Expression Builder to get an argument for the Run Code action, and navigate to a module, I see only functions. Besides wrapping it in a fucntion, any way for subs to be used for this action?
I apologize if this is the wrong forum for this question but I was not sure where to post.
My question is about the expression builder for a query. I am having trouble finding help in the access help file on syntax. I forget how to build an expression properly.
I want the query to take two dates (text01 and text02) from a form and run a date range check. So, the criteria has to be equal to the two dates or inbetween them. I know the logic behind this, but I cannot get the correct syntax. I am pretty sure this is an easy formula, but I need someone to help me get started in learning the syntax of the expression builder.
Somehow I have lost the use of the Expression Builder. Now when I either click on the [...] of the Event List, or right-click and select "Build", instead of the Expression Builder popping up, it goes to the vba window. I have tried to find what Library Reference relates to this and have run Detect and Repair, without success.