Expression Referencing Same Query

Oct 24, 2006

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!

Thanks!

View Replies

Referencing A Field On A Form From A Query

Jul 24, 2005

Hi,

My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.

I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.

Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.

I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again

hope i explained it well

Thanks

Ricky

View 7 Replies View Related

Referencing Form Field For Query Between

Mar 10, 2006

Hey All,

I have a question regarding referencing a form using dates between.

Currently I have it setup as:
Between [Start Date] And [End Date]
on the query.

I want to reference a form, which i figured would be like this
Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]

But this doesnt work! :confused: What did I do wrong here? Any help as always is greatly appreciated!

Thanks!
Mateo

View 1 Replies View Related

Queries :: Referencing Other Tables In A Query?

Dec 4, 2014

I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.

I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.

So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.

View 1 Replies View Related

Referencing CDbl Value In Stored Query From A New Query

Aug 13, 2007

Hello - am hoping someone can help with the below query.

I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.

I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.

I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?

Thanks!

View 2 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

Jul 23, 2015

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..

View 9 Replies View Related

Queries :: Crsosstab Query Doesn't Recognize Expression In Source Query

Aug 12, 2015

I have a query where these are the fields:

ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC

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?

View 4 Replies View Related

Referencing "Current" From In Query

Dec 14, 2005

Hi guys, I think that I am being dumb but here goes.

I need the query associated with a Combo Box row-source to filter records based on another field on the form.

I can get this to work just fine if I explicitally include the form name in the query, ie SELECT Client.Client FROM Company INNER JOIN Client ON Company.Company = Client.Company WHERE (((Company.Company)=[forms]![allcalls]![company])); My form name is AllCalls.

However, I will be having many similar forms so do not want to reference the form by name. I am sure that the form can be referenced by me! or something like that but try as I might I cannot get it working.

Am I making sense? and does anyone have a solution?

Cheers

Wayne

View 1 Replies View Related

Query Expression

Jul 13, 2005

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.

Please advise.

Thanks

VC

View 1 Replies View Related

Query Expression

Jun 7, 2006

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

to go in the column for the query.

Thanks

View 2 Replies View Related

Query Expression Help

Nov 16, 2007

Hello All,

Need some help with a expression Why would this equal nothing...if Ontime PU.CountOfP Performance is 32 and Late PU.CountOfP Performance is 0?

[Ontime PU.CountOfP Performance]-[Late PU.CountOfP Performance]/[Ontime PU.CountOfP Performance]

Shouldn't it be 1 or 100%......?????

Any Ideas??

View 6 Replies View Related

Query Expression

Jan 15, 2008

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.

Appreciate the help!

B.

View 9 Replies View Related

Query And Is Null Expression

May 28, 2005

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.

This is what it looks like:

http://img.photobucket.com/albums/v332/youmnac/qryND.jpg

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

http://img.photobucket.com/albums/v332/youmnac/form.jpg

View 6 Replies View Related

How To Simplify This Expression In A Query?

Oct 13, 2005

I have a form field whose value is as follows:

=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)

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.

Thanks!

View 1 Replies View Related

Problem With Query Expression

Feb 22, 2006

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 :)

View 3 Replies View Related

Build Expression For Query

Jul 5, 2006

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

View 1 Replies View Related

Query Expression Trim

Jan 30, 2007

Hi Guys

Another expression that's troubling me. I need an expression that can take this field.

Z:MusicDownloadDDale DaviesUntitled EPDale Davies - Untitled EP - 05 - No Perfect Child.mp3

Trim the front to the 4th ie. Dale DaviesUntitled EPDale Davies - Untitled EP - 05 - No Perfect Child.mp3

Then the end of it ie Dale Davies

So all I have left in a seperate field is the Name. Also I'd like to know what parameters to change to trim to the 2nd or 3rd .

Thanks alot guys

Adam Greer

View 5 Replies View Related

Query/Expression Assistance

Feb 22, 2007

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?

View 6 Replies View Related

An Arithmetic Expression Query

Feb 26, 2007

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

View 1 Replies View Related

Append From A Query With Iif Expression.

Apr 30, 2007

Hey folks,

I'll try to be succinct.

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.

Would changing any of the query properties help?

thanks

View 4 Replies View Related

DLookup In A Query Expression

Jun 14, 2007

Hi,

I have a DLookup expression that is working in a Control on a form :

=DLookUp("[Class] ","Class","([Forms]![Orders_Crosstab]![Indcat] = [Clind]) AND ([Forms]![Orders_Crosstab]![2005] Between [From] AND [To])")

I simplified it for test purposes as an expression in a query:

Expr1: DLookUp("[Class] ","Class","([Indcat] = [Clind])")

This gave an error - MS Access cant find the name [Indcat] in the expression.

After some R&D on the web I found a solution that works:

Expr1: DLookUp("[Class] ","Class","([Clind]=" & [Indcat])

[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.

Regards

Tony Randell

View 2 Replies View Related

Query Expression Too Complex?

Sep 26, 2007

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?

Thanks,
Sup

View 1 Replies View Related

Query Criteria Expression

Feb 8, 2008

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.

Many thanks

Stuart :)

View 3 Replies View Related

Labelling A Query Expression

Dec 9, 2004

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?

View 1 Replies View Related

Queries :: Using A Query In Expression

Aug 17, 2014

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?

View 12 Replies View Related

IIF Expression With Date In Query

Aug 29, 2014

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.

View 3 Replies View Related