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 Replies

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

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

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 3 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

Query Expression To Filter Records

Nov 6, 2005

I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.

Thanks.

View 1 Replies View Related

Extract UK Postcode With Expression In Query

Jan 19, 2006

Hi

If i wanted to run a query from customer records in my database and wanted a list of specific postcodes that matched the criteria how would i do this?

For example each customer records has a postcode BD4 4KL, LS9 7YH ETC

I want to categorise each postcode set to see for example all the customers with postcodes begining with have ordered factsheets.

In the criteria part of the query how would i write it so it only extracts specific postcodes from the database from the ones i require

e.g "HU1, HU2, HU3 HU4" - checks database - returns all postcodes beginning with this.

Please help me!

Thanks

View 14 Replies View Related

Execute Expression In Query If Not Null

Jun 28, 2006

Can anyone help...

I am writing a query in which, amongst other things, I also want to perform a dateadd calculation.

I have the following

expr:dateadd("d", [lastduration], [lastlandingslot])

This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null.
I have ran the code below sucessfully.
expr:dateadd("d", 1,[lastlandingslot])

How can I get this to execute only if the [lastduration] is not null??

Tried to search but network is really slow

View 7 Replies View Related

Error In Query In Expression Builder...

Sep 21, 2006

I need to implement the following logic in a report column:

IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""

I have implemented the following query in Expression Builder for the calculated field:

=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))

When I click on Form View, I get "#Name?" in the calculated field.

Any ideas? Thanks.

View 10 Replies View Related

Error In Query In Expression Builder...

Sep 21, 2006

View 12 Replies View Related

Expression In Query Not Formating Correctly

Sep 26, 2006

Hi all,

I have a query that I have an expression in that is returning a text value instead of a number value. The field in my table is a number value but when I use the expression it returns it as text. I have done several searched but can't seem to find anything that matches formatting an expression as a number. Here is the expression I am using, the reason I am using it is because I need to return a zero if no records matched the criteria.

total: Nz(Sum([act_hours]),0)

Thanks

View 3 Replies View Related