I have a short module using the transferspreadsheet command importing an excel worksheet into a table. (blahblah_import).
Before I can append those records to the permanent table (blahblah) I need to pull the correct # values from various lookup tables.
I have a query written which does the lookup. For the user name / user id lookup I have an IIf statement so if the lookup finds nothing, it will use "UNKNOWN" (no quotes).
When used as a select query, it works great. Where it can find a user id it puts that in the user field, where it can't it puts "UNKNOWN" (no quotes).
When I set it up as an Append query, it appears to work fine (appends correct # of records, no violations, etc. BUT wherever it did find a user id - the field in the appended to table (blahblah) is blank. This is even a required field (if i click in on the the blank fields, i then have to hit ok a bunch of times for each empty field)
Here is the IIf statement
user: IIf([lookup_user.value] Is Not Null,[lookup_user.value],"UNKNOWN")
Any help is appreciated, I've tried using a Make-Table query instead, but with similar results, which leads me to believe that the Iif is where the problem lies.
hi Guys, I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
Simple this one but it's something I can't seem to get working so I thought I would ask as I'm missing something obvious.
What is the correct expression to use in a query to select records with particular dates. The database contains information on quotations and each record has a follow up date field and a status field.
The query should find records where the follow up date is today and the status is "Live".
I have =NOW() in the date field and "Live" in the status field but no records are being found even when I know there are some that match the criteria.
Good Morning. Have a quick question for the gifted.
First let me say I have inside my table Target Install, Received Date, and Type of Circuit
In the Query, I have created a column with the following statement. Due Date: [Target Install]-30 which works great to give me 30 days from the target install. The issue is that this statment is not correct for all products. So I need to understand how to build a formula for this in the query.
If [Type of Circuit] = "Branch Network Frame" than [Target Install]-30 or If [Type of Circuit] = "Class I" than Received Date+7
Could someone provide me with an expression that would always return data from last week? I've been playing around with serialdate and datepart but can't quite figure it out.
I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria: ([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report. If that date fields are left blank.. I want it to display all the records
Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.
Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?
Please note that I need this query to be updateable as I have some other form fields that needs to be edited.
Hello all, I have ran into a bit of a problem with a database of mine that is for an architectural firm.I have 3 tables in total.They all have a relationship between them and the main table is ERGO.This table has general info about the work that the company has undertaken on behalf of the client as well as the total price.Now a house can have two predetermined prices one for the study of it and another for the supervision.These two pieces of info are divided into two fields in the main table.The other two tables have info on the various deposits the client makes for either the "study" or the "supervision" or both. What i want to achieve is to be able to print in a report the balances for all the clients that have (balance > 0) either for study or supervision.I have tried to make an expression in a query and use that in the report but i get an error when trying to run the query.On the other hand when I tried to do the calculation in the report itself the calculation is computes just fine but I have instead of one "output per house" I have multiple outputs as many as the deposits of the client. I have included a copy of the tables , if someone has any clue plz help me out :)
Hi all, I have a field that stores software versions as text. The version numbers can be simple (4.0, 5.0) or unknown or more complicated (4.5.200512, etc). The user wants to be able view data in a pivotChart without seeing interim versions- so 4.5.200512 would not show up but its data would be counted as part of 4.5. Anybody know how I can do this? thanx
Hello, I am trying to write a simple query that references calculations in the same query - but I keep getting the "enter parameter value" box.
Basically, I have some columns in a query calculating average and sum values for a column. I would then like to multiply some of those columns together in a new column, using an expression like this: =[AvgOfColumn1]*[SumOfColumn2]. Access doesn't want to do this for me. It seems very simple so I'm sure I'm missing something!
I have created a query based on two akwardly designed tables that ultimately relate a member of Congress to a specific Committee and Subcommittee. Unfortunately I cannot alter the tables, as I receive weekly updates from an external source.
This being the case, once I have created the query, a "-" appears in the Subcommittee column, where the Member of Congress serves on the Committee, but no Subcommittee or the Committee does not have a Subcommittee. Although I could exclude the "-", however when I do this certain committees are removed from the final results. So I attempted creating an expression, where "<Not Assigned to a SubCommittee>" appears if the Committee does not have a SubCommittee. Unfortunately there are a few committees that fall under this. I created the following expression:
=IIf([HMC_Name]=("Committee on Budget""Committee on Standards of Official Conduct""Committee on House Administration"),"<No Subcommittee Assignment>",[HSC_Name])
Yet this expression returns #Error. Any suggestions?
I'm currently trying to calculate what each employees hourly rate of pay would be if everyone was given a 3 percent increase, using expressions but I cant seem to be able to work out how to get the correct calculation, as everytime a run the query it doesnt match up to what its supposed to be. So in other words what I was wanting to ask was "how can I work out percentage calculations using Expressions, any help with this would be most grateful.:D
[Indcat] and [2005] are part of the recordset where [Clind], [From] and [To] are values in the Table 'Class'
MY CHALLENGE:
I cannot find the correct way to add the extra selection criteria to the query expression (as per the form expression above) that checks the value [2005] is between the [From] and [To] values in the table. Have tried a number of combinations without success.
Any help, suggestions and/or guidance very welcome.
The recordsource of a listbox is a query which is filtered by a combo box on the form. There are 4 possible groups to be viewed and the values for each group are Group 1=(Null or " "), Group2="Denied", Group3="Restricted" and Group4=("Approved" or an approval number). The query worked fine until I added the code to get Group4 by eliminating all of the other values. It is causing an 'expression too complex' error. Here is the sql for my query:
SELECT vi_AuthApproval.AuthID, nz([Approval_Number]," ") AS Approval FROM vi_AuthApproval WHERE (((nz([Approval_Number]," "))=IIf([Forms]![frmAuthApprovList]![cboApproval]="Approved",(nz([Approval_Number]," "))<>" " And (nz([Approval_Number]," "))<>"Denied" And (nz([Approval_Number]," "))<>"Restricted",[Forms]![frmAuthApprovList]![cboApproval])));
Can someone help me simlify the query or point me in another direction to accomplish this?
Hi all, I wonder if anyone can help me? What I am trying to do is relativley simple but I am just having a problem executing the expression.
I have a search form which returns the list of results in a subform, in the search form (attached), there is a field which is to bring back results for contracts due in X amount of days, where the user inputs X. In the query results I only want this field to queried on if something is actually typed into the field. The field in the query I am writing this under is called [RenDate]. I have written a criteria expression in my query as follows, but am getting no results:
IIf(IsNull([Forms]![F Search]![days for renewal]),"",[RenDate] Between Date() And Date()+[Forms]![F Search]![days for renewal])
Everything else is working fine with this form/query. If anyone could help or advise on my expression it would be much appreciated.
In a query i want to know the date of a patient on the day the report is printed which is Date()-[DateOfBirth])/365.25. This works fine however i want to give it an expression name that says Age at (and then insert the current date). How do i do this?
Users add transactions via a form after they put in the total of the transaction I want to run a query that gets a running balance of the client's account to make sure that paying this transaction will not over draw the clients account.What would be the best method of approach?
I have a query that I want to return "due by 9/8/14" if the [Grace] field is blank or if the date in that field is before 9/8/14. If the date in the field is after 9/8/14, I want it to return "valid through (the date in the field)"
I wrote: MedWords: IIf([Grace]=Null,"due 9/8/14",IIf([Grace]<9/8/14,"due 9/8/14","valid through " & [Grace])) and all I get is "valid through" and either blank, or whatever date is in the [Grace] field.
I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option. I then want to be able to 'delete' the relevant records from one table.
The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling. I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed? I'm at a loss! :) Thanks
I have a database that contain foreclosure records. I'd like to create a query that will ask for a date and all records that are LESS than the date will be moved to a different table.
I'd also created an icon on my form and I'd like to attach this query to it.
Please how can i use a procedure to create a query, then append the content of the query to a table (am using MS Access Project)? i did it in Microsoft acess database but now i need it on Microsoft access Project beacuase am transfering to SQL server. Thanks
Now i want to create an append query that will add in table ZALGA fealds PROIZ_I, TIP_I, DN_I, KOS_A, KOS_I by critera if TIP_A = TIP_I AND DN_A = DN_I then calculate KOS_Z = KOS_I - KOS_A?
