Field Expression Help Needed

Nov 11, 2004

Hello,

I created a query that pulls from multiple tables, of which contain the same field name (LastAccessed).

I am getting an error stating that (LastAccessed) "...could refer to more that one table listed...".

What is the correct way to state that I want to pull (LastAccessed) from 'tbldatabases' and not the others.
My goal is to only pulls those record whose (LastAccessed) contains 2004. Right now I have "Year([LastAccessed])"
in the field line and "2004" as its criteria.

Thanks.

View Replies


ADVERTISEMENT

Help Needed With Expression

Aug 8, 2007

Hi,
I have a problem I'm trying to solve using an expression in a query

So far I have a working expression giving me the result I want.
Simply it's:

subtotal: ([ChargeA])+([ChargeB])+([ChargeC]) etc. etc.

However, I also have to apply an effective 'Cap' which I'm calling a 'Full Load Charge'.

Whenever a subtotal exceeds the full load charge I need to cap it so that our customer doesn't get over-charged. This full load charge is agreed with our customers.

Let's say for example that the subtotal for hauling goods from a supplier is £600 for a total of 20 pallets but the Full Load Charge has been agreed at £400.

How can I build an expression that will use the subtotal calculated until it reaches the full load charge limit and then use the agreed full load charge?

The expression might read something like... 'fullLoadChgCap: If 'subTotal' is less than or equal to 'fullLoadCharge' use 'subTotal' else use 'fullLoadCharge'

I don't know how to write this as an expression :o .

Thanks in advance

View 2 Replies View Related

Expression Builder - Help Needed.

Oct 26, 2004

Im looking for a guide to teach myself how to use the expression builder.

I know how to write constraints in SQL, so with a bit of luck it will be similar. :P

A list of every command you can use might be just as good as a tutorial.

Can anyone point me in the right direction?

Thanks.

View 2 Replies View Related

Date Range Expression Needed

Nov 20, 2007

Hi,

I'm sure this is a very obvious question for someone, but not so much for myself. I just need a date range formula for a query. I want the query to ask me the start date and also ask the end date and pull up all dates falling in between.

Anyone know a formula?

View 2 Replies View Related

Conditional Formatting, Expression Needed

Apr 1, 2005

Hello,
I have a date field in my form that I would like to have the date change color sixty days prior to the date entered. Can someone give me the expression to use in conditional formatting? For example: If the date is 9/30/05, I would like it to change color on 8/2/05.

Thanks

View 2 Replies View Related

Help Needed With The IIf Expession In The Expression Builder

Jan 11, 2006

Hi all!

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

View 13 Replies View Related

Some (expert) Advice Needed Please (linking Field With String In Other Field

Oct 3, 2005

Hi,

I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.

The two tables I would like to link are,
Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records.
Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.

I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).

The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.

I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?

The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.

I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.

Thanks in advance (I hope),

Onur

View 1 Replies View Related

Field Relationship Understanding Needed

Jun 5, 2006

ok, let me please ask this question regarding a payment relationship in my database

it will be paid in four ways, cash, creditcard, cheque, paypal

each of these payment types will have their own features..

eg creditcard (name on card, card number)

cheque (cheque date, date cleared)

paypal (username...YOU GET THE PICTURE

so is this below way a good way to store these details, or can i do it with 4 less tables..

http://bb.1asphost.com/antonyx6/untitled2.jpg

if this way is correct, i dont see how each id will be entered..
eg
if i enter some cheque details.. will that lead the chequeid to be linked to the paymentid, and the paymentid to the bookingid, and the bookingid to the booking details etc

View 5 Replies View Related

Urgent Required Field Advice Needed

Jul 1, 2005

I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):

I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".

What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.

Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.

Many Thanks,
Photoguy

View 9 Replies View Related

Memo Field Punctuation - Form Help Needed

Jun 8, 2005

I have a form set up where user's type in an explanation of how a claim is being appealed and resolved in a memo field.

One problem, for some reason management doesn't want these explanations to end with a period. But the users keep ignoring our request and keep using the period.

Is there a way that you can code this memo field to where it will allow punctuation throughout, except at the end?

View 1 Replies View Related

Forms :: Required Field Suddenly Needed During Filter

Feb 25, 2015

I have a form that I have opening in Filter by Form mode. It WAS working flawlessly, at least it was opening in that mode. It IS still requiring me to click on Toggle Fields once data is entered into controls to filter by, but that's another issue (I wrote a recent comment, but haven't gotten any response). Something different is happening suddenly that wasn't happening before. This form is a copy of a form needed to enter data.

Both forms read from a table which has several required fields. Suddenly, when I open the form to find records in ANY mode it's making me enter something into these required fields, even though I'm not searching/filtering by them. The message comes up "You must enter a value in the [Specific Required Field name here] Field." What did I do to make this start happening? How do I stop it? I DO want something to need to be entered in the required fields when new records are being entered and when things are being changed. However, I don't want it to be required during search processes.

View 12 Replies View Related

Creating An Expression From A Date Field (Help Please)

Apr 8, 2006

I one of my tables, I have a date field (DateCompleted) with the format: mm/dd/yyyy

Now, I want to create a query which would create another field (DateExpected) by using the date in the above table and adding 5 months to the date.

The only issue is that for the new field in the query I want it just to have mm/yyyy format.

For example, if the original date in the table is 04/05/2006, after running the query, I would have a new field 09/2006.

Does anyone know the exact expression I would put in the query? Thanks. The expression would be put in the "build" area.

Thanks for your help!!

View 2 Replies View Related

Queries :: Using Total Of Field In Expression?

Oct 7, 2013

I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.

What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.

I have the following SQL for it:

SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent]
FROM ATB
GROUP BY ATB.FC, ATB.MMDDYY
HAVING (((ATB.MMDDYY)=Date()-Day(Date())));

Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.

View 1 Replies View Related

Modules & VBA :: Return The Field Name From Expression

Mar 13, 2014

I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.

Example:
(Minimum is a working module that finds the smallest number in the array)
Field1 = 34
Field2 = 53
Field3 = 21
Field4 = 62

GetFieldName(Minimum([Field1],[Field2],[Field3],[Field4])) = "Field3"

I've searched but can't find a way to select the field name anywhere...

View 4 Replies View Related

Queries :: Use Expression For Field Header

Jul 22, 2014

I'm writing a group by query to transform data and need to use an expression to set a column header such as max(Date([cDate]).

View 2 Replies View Related

General :: 2 Fields From Separate Tables Needed To Create Calculated Field?

Aug 19, 2013

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

View 7 Replies View Related

Extract Regular Expression From Text Field

Sep 5, 2007

I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.

"something C4CLE01"
"something H5STT02 Something else"

What I would like to do in a query is to extract just the ID number part and put that in a new field e.g.

"something C4CLE01" => "C4CLE01"
"something H5STT02 Something else" => "H5STT02"

So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.

Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.

Im using Office 2003 if that makes any difference. Thanks in advance.

View 5 Replies View Related

Reports :: Expression Builder - Sum Fields If Another Field Is Less Than 30

May 21, 2014

I am trying to sum fields if a another field (Accurate/Complete (30 pts)) is less than 30

Right now it works to provide my total if the yes/no button for a certain field has been clicked.

=Sum(IIf([Procedure Error],1,0))

now I want it to only total the numbers if

[Accurate/Complete (30 pts)]<30

How would I add the if less than to the top expression?

View 3 Replies View Related

Queries :: Copy Result Of Expression In A Field

Dec 5, 2013

I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.

exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)

"exp4" is the clean name of the song.

Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?

View 2 Replies View Related

Queries :: Format Expression Field As Currency

Jun 4, 2014

I have this expression I created and I want to know how to format it as currency. I know how to do it with a regular field but the option is not available for an expression.

The expression:

Code : Charge: IIf([Rate] Is Not Null,[Rate],"0.00")

View 1 Replies View Related

Reports :: Creating Field Using Expression Builder

Dec 18, 2014

I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.

So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?

View 1 Replies View Related

Format Field Output Of A Complex Expression In Query.

Feb 8, 2006

I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).

So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like

36.7768595041322
38.6666666666667
15.6448202959831
etc


the expression i use is:

Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)

Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?

View 7 Replies View Related

Modules & VBA :: Format Single Field As Part Of Expression In SQL

Mar 22, 2014

Expr1 is a field in a report that is bound to this query.

timeline.CompanyName doesn't usually have any data in it, and when it does it needs to be removed and manually input into companies. Company Name.Is it possible to make timeline.CompanyName output as italics? Otherwise we dont know which field is populating.

Code:
SELECT [timeline.CompanyName] & [companies.CompanyName] & " (" & [companies.city] & ", " & [companies.state_id] & ") ~ " & [timeline.Title] AS Expr1, timeline.showitem, timeline.EntryId, timeline.EmployeeId, timeline.CompanyName, timeline.EmpStartDate, timeline.EmpEndDate, timeline.Super, timeline.CoPhone, timeline.Notes, timeline.Title, timeline.company_id, timeline.start_date, timeline.end_date, timeline.assignment, timeline.notes_staff, timeline.showitem
FROM timeline LEFT JOIN companies ON timeline.company_id = companies.company_id
WHERE (((timeline.showitem)="false"))
ORDER BY timeline.end_date DESC
WITH OWNERACCESS OPTION;

View 3 Replies View Related

Tables :: Field With Calculation Expression - Round Function

Jul 28, 2014

I create e field with calculation expression and I need to round the results. I insert the following expression:

Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)

I have back the following error message: Could not find field 'Round'.

I do not understand why, because Round is not a field, but a function.

View 6 Replies View Related

Queries :: DLookup To Return Value Of Field In Query Expression

Oct 30, 2013

I have a field created in a query expression

BirthMon: Format([DOB],"mmmm")

I would like to create another field in the query using DLookup to return the value of the BrithMon.

BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")

This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'

Can I used DLookUp in a Query expression to refer to another Query created field?

View 2 Replies View Related

Forms :: Added A Field Using Expression Builder Which Is Returning Name?

Jan 18, 2015

So I have built a form and added a field using the expression builder which is returning #Name? There is a relationship between the table on which the form is based and the record I am adding to a control.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved