One Field, Two Criteria!

Jun 1, 2005

Hi there
I have a table of which I need to count the instances of one of the fields.
The trouble is that I need to return two different counts based on two different criteria.

I don't really want to duplicate the table just to be able to achieve this, and I wanted to be able to construct it in a way that I can use in my ASP page which interrogates the database.

Of course everything I've tried applies the two criterias at once to the results. Stuck on this one, any help much appreciated. :confused:

View Replies


ADVERTISEMENT

Tables :: Autopopulation Of Field From Another Table Based On Another Field Criteria

Dec 5, 2012

I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.

This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.

If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If

View 3 Replies View Related

Queries :: Using New Field In Query As Criteria In Different Field?

May 11, 2013

I have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]

Is it possible to use the result from this prompt as a criteria in a different field?

for example, Order_Date based on the criteria of <=[Report_Date]

View 4 Replies View Related

Queries :: Use Another Field With Between Criteria On Another Field

Sep 29, 2014

I have db that tracks vehicle mileage and servicing

I have created query to show which vehicles are due service by the mileage

My issue is some manufacturers set different service intervals

E.g.
car manufacture A = service intervals every 10,000
car manufacture B = service intervals every 20,000

but i would like to give pre warning so I'm using between criteria

here is what i have much appreciate if there is a better why of doing this

im using Dmax to find the last mileage entered for the vehicle

findLast: DMax("[vehicle mileage]","[vehserviceTbl]","([vehicleidimp]= " & [vehicleid] & ")")

now the criteria

Ifs([make] = "A" , Between 9000 and 10000
ifs ([Make] = "B" , between 19000 and 20000

can't get the red text to work shows blank when query runs

View 1 Replies View Related

Criteria To Look Up A Field.

Jan 9, 2007

Nearly finished my 1st database thanks to the people here.
Just need direction on one more thing:-

i need to input a "return date" into a field on a form and then click a button to run a query based on that date field to list all carts to return on that date. We always have to put in different dates and so 1 set query would be no good.
I assume i need some kind of criteria in the query. Anyone help?

Many Thanks Ben

View 2 Replies View Related

Change Field When Criteria Is Met

Jul 1, 2005

You will see it is a simple problem if you know what your are doing.

When entering the joining date I would like the Status to change automatically to Member and when the renewal date is reached I would like the status to automatically change to Renewal but I am unable to work out a formula for this.

Many Thanks,

Fredfox.

View 7 Replies View Related

Query Field Criteria

Oct 4, 2005

I am not sure how to search for this so if there is another thread with this example please direct me in that direction. What I am trying to accomplish in my query is the following:
I am bringing data in from another source in the following table

ID Date1 Date 2

I want a query to do the following based on dates entered on the switchboard in a beginningDate field and EndingDate field

ID Date1 Date2 Final1 FInal2

If Date1 is between beginning and ending date I want it to put a yes in Final1 if Date2 is between beginning and ending date I want it to put a yes in Final2. Both can have yes'.

I tried doing an if statement in my expression as follows: IIF([Date1] between [Forms]![FrmSwitch]![BeginningDate] and [Forms]![FrmSwitch]![EndingDate], "Yes", "") but nothing comes up. My switchboard is always open with the dates in an unbound field.

Any suggestions???

View 5 Replies View Related

Field Indentifiers As Criteria

Oct 5, 2005

I am wondering if it is possible to use a table/field identifier as part of a criteria expression in a query.

The field that I need to use in the expression is not a part of the focus table, so I think I need to use a full identifier, if that is possible. I have seen such identifiers used for controls on forms, but never fields in tables.

For example, something like the following as a criteria expression:

<>[Tables]![tblUserData]![UserId]

Obviously, that didn't work. My question is, is it possible to identify a field from another table as part of a criteria in a query. If so, how?

Just in case it helps, the reason I am doing this is I am trying to create an append query that only appends data that is not already there.

View 5 Replies View Related

Criteria Based On The Value In Another Field

Feb 15, 2006

Hey guys, apologies in advance for a question which you may have answered many times, I have searched the net, my textbook and these forums but to no avail.

I have a table, 'tblStock'.

I have two fields within this table, 'Stock Level' and 'Minimum Stock'.

I want to run a query on this table, finding all records where the value in 'Stock Level' is LOWER than the value in 'Minimum Stock'.

How would I go about setting up this query? What criteria would I type? I have tried typing <"Minimum Stock" in the criteria for 'Stock Level' but it didn't work.

Thanks in advance... this will be a big help. :)

View 7 Replies View Related

Field Specific Criteria

Jun 14, 2006

I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?

The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:

Primary Key___ID___Fault
___1__________ 1____fault 1
___2__________ 1____fault 2

That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.

I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?

Many thanks,
Tom

View 1 Replies View Related

Search Criteria Within Same Field

Aug 10, 2006

Hi All,


I need to make a search criteria within the same field,
for example here 'Demo' should selected from 'xxx' to 'xxx' number.

Thanks for reading, any suggestion would be appreciated

good day :-)

View 4 Replies View Related

Enabling A Field When Certain Criteria Are Met

Jan 22, 2006

Hi, I have a field in a form that says when you are able to book a certain event, and i want the field in which the event can be booked, to remain unable to be edited until the tick box says yes. Is that possible?

Chris

View 4 Replies View Related

One Field Meets Criteria Of Another?!?!

Jan 16, 2007

Hi,
I have one field, for this example, FieldA and another FieldB.
I would like the following:
If fieldA =1 then FieldB must = 1 or 3 or 4. If not then I would like a message box saying it needs to check. Otherwise I don't want anything to happen.

How can I do this? and where do I put any expressions?
thanks
Sue

View 7 Replies View Related

Queries :: Possible To Set A Criteria On A Field?

Aug 23, 2013

In an Access 2010 query is it possible to set a criteria on a field (Results), that If it is "Negative" then another field (Variants) has the text "None Detected". Or is it easier to do that in the Variants field?

View 1 Replies View Related

Criteria Based On Value In Another Field

Apr 3, 2012

I have a query in Access with the following fields (grouped):

"Name" "Facility" "Number of Visits"

I want to limit the query to only pull those names that have at least one visit to a specific facility (let's say facility "A"), but I also need to see all of the other facilities that those who visited Facility A visited. So in this case "= "A"" in the criteria section of the Facility field wouldn't work. I think I need to plug something in the criteria section under the Name field, but I am not sure what.

View 1 Replies View Related

Criteria In Date Field

Dec 18, 2014

In the query I am trying to put a criteria in a date field. What I want is to get all the data that fits in one month.

I use Microsoft access 2010

View 5 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

Nov 16, 2007

I think the title pretty much sums it up....

I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.

I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.

The query is refusing to return any results...

Any ideas?

View 10 Replies View Related

Double Criteria On One Field Problem

May 27, 2005

Apologies, this was posted as a reply to a previous thread I submitted but its not related to the title anymore (dlookups).

I have a query that looks up a payrate value for an employee in a payrate table. It uses the appropriate value to determine the pay rate at teh time an employee has worked (DateWorked). On top of this I also need to introduce a parameter criteria on the DateWorked to allow the user to run the report for a time frame. This is where I get stuck as I cant seem to get it to work properly if I introduce another criteria on the field

The query that works great (without the second criteria) is as follows;

SELECT tblTimeCard.EmployeeId, tblTimeCard.TimeCardId, tblTimeCardHours.Task, tblTimeCardHours.Hours, tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.DateTo, tblPayRates.PayRate,
tblTimeCardHours.Hours*tblPayRates.PayRate AS Cost
FROM (tblTimeCard INNER JOIN tblTimeCardHours ON tblTimeCard.TimeCardId = tblTimeCardHours.TimeCardId) INNER JOIN tblPayRates ON tblTimeCard.EmployeeId = tblPayRates.EmployeeId
WHERE tblTimeCard.DateWorked Between [tblPayRates].[DateFrom] And [tblPayRates].[DateTo];

This is where I need to introduce another criteria where tbl.TimeCard.DateWorked Between [Enter Date From] And[Enter Date To]

when I try this I must have one of the joins wrong as I get all the payrate values for each date worked even if they are outside the dateworked criteria set by the second criteria.. I hope I have explianed this ok. Can someone please advise where I should be looking - many thanks

View 1 Replies View Related

Criteria Matches 1st Part Of Another Field

Jun 22, 2005

undefinedHi, apologies if this has been answered before but can't find anything relevant!

Trying to match one field to the first 5 digits in another i.e.

Field 1 = 55667

Field 2 = 5566785431 so would want to match the 1st five digits in field 2...

Any ideas?

Thanks

View 3 Replies View Related

Use A Criteria To Search For Part Of A Field

Jul 28, 2005

I want to ba able to search for only part of the field in a query. I want to run a reort from a query where the user only needs to enter part of the information from a product list ie the items listed may be as lisetd as make model in the same field. the user won't be able to get to the query to alter the criteria. i have tried:
Like "*" & [Enter Product Name] & Like "*" Which I found on a previous post but this returns a syntax error, and highlights the second Like

View 3 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Updating A Field Based On A Certain Criteria

Nov 6, 2006

Is there a way in a query (using SQL) to go to the first record to update a field based on a criteria ?
For example if my amount field shows two amounts for $300.00 I want to go to the first record that has $300.00 to update a field in that record.

Is that possible?

View 9 Replies View Related

Criteria For Calculated Field In Query

Dec 8, 2006

Hi,

I have a calculated field in my query called "outstanding".

this is how the query looks:


Expr: SubFormTotal………………[qty]*[price]

Discount …………………………...[Discount]

Expr:Total………………………….[SubFormTotal]-[Discount]

Deposit…………………………….[Deposit]

BalanceToDate…………………….

[B]Expr: Outstanding…………………..[Total]-[Deposit]-[BalanceToDate]



PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields

what am i doing wrong and how can i solve it?

thanks

View 7 Replies View Related

Can You Query Using Multiple Criteria From Same Field?

Mar 9, 2007

Hi all,I posted something similar to this beforehttp://www.access-programmers.co.uk/forums/showthread.php?t=124289But i didnt get it figured out.Is it possible to use the same field for multiple criteria in a query?the one i would like to base it on is taskID.i just want the total time to be called admin time if taskid=2 and investigative time if taskid<>2.Ive tried it with single and multiple queries in one and am recieving errors with both. help is always appreciated!Woohoo for 100 posts!

View 3 Replies View Related

How To Choose Field Based On Criteria

Jun 1, 2007

Hi,

I am building a database that calculates freight costs for shipments.

I have one table that has order data with fields such as [Order Number], [Origin], [Destination], [Service Level], [Weight].

The service level is one of 4 values: PRE 9, PRE 12, NEXT DAY, NEXT DAY +1.

The origin and destination use country codes such as BE, AT, CZ, PL for Belgium, Austria, Czech, and Poland respectively.

I also have rate sheets from carriers such as DHL and UPS and they provide their rates in the following format (numbers are made-up as real rates are confidential): [Origin], [Destination], [Service Level], [0 to 0.5 kg], [0.5 to 1.0kg], [1.0 to 1.5kg], [1.5 to 2.0kg], etc all the way up to 30kg. The rate is then in the appropriate column and the row gives the origin-destination-service level combination.

(sample freight rate table uploaded as zipped excel sheet in attachment)

I need to return the rate from the appropriate row based on the [Origin], [Destination] and [Service Level] fields (easy enough), which is easy enough.

The hard part is selecting the correct field to return. I tried usign a Choose expression but Access rejected it saying it was too complex (maybe because too many choices?).

So I'm stuck. And working towards a deadline... :-)

Can anyone help please?

Many thanks,
Martin.

View 1 Replies View Related

Multiple Criteria Set Via Form Field...

Mar 24, 2005

Hello,
...I'm not sure if I'm making this question more complex or not or if it's even possible? (I would think so) Anyway...What I am trying to do is create a form in which I can input more than one criteria into a field - We will cal it txtSetValues. Then via a button open a query that will use txtCode as the criteria for searching records in a table. The
table is called: tblMASTERTABLE-EmpCount
query is called: qryEmpCount

The table currently only has two field; ORG and Name. The criteria I have in ORG is: Like [Forms]![frmEmpCount]![txtCode]
I currently can find any specific ORG just fine. I can also use the wildcard and that works fine. But how can I set it up so that I can enter in multiples... For example, I would like to find all employees that there org is either; AL001 OR AL010 OR AL100 - The wildcard will 'Almost' do it in some circumstances, but it may gather some orgs that I don't want in this list. I hope I am explaining this in an easily understood fashion?

Thanks,
---roystreet
P.S. Now I know I could just make multiple fields in the form (ie; txtCode2, txtCode3,etc) and then just add those as criteria in the query, but I was hoping that I wouldn't have to do that.

View 1 Replies View Related







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