Queries :: Expressions - Earliest Date Of When Documents Received
Jun 12, 2014
I have the below listed expression, which will give me the earliest date of when documents were received.
However, if I only have a date in the "B - Datasheets" field, it will not appear on my report because it starts off "isnull".
How can I end the expression to use this date field if there are no other dates?
Submittal: IIf(Not IsNull([BID_PO]![B - Datasheets]) And [BID_PO]![B - Datasheets]<[BID_PO]![C - Curves (Performance Data)] And [BID_PO]![B - Datasheets]<[BID_PO]![D - Schematics] And [BID_PO]![B - Datasheets]<[BID_PO]![E - Drawings],[BID_PO]![B - Datasheets],IIf(Not IsNull([BID_PO]![C - Curves (Performance Data)]) And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![D - Schematics] And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![E - Drawings],[BID_PO]![C - Curves (Performance Data)],IIf(Not IsNull([BID_PO]![D - Schematics]) And [BID_PO]![D - Schematics]<[BID_PO]![E - Drawings],[BID_PO]![D - Schematics])))
View Replies
ADVERTISEMENT
Sep 8, 2005
Ok, I have a query that returns a case manager, their clients, and a date attributed to each client. Ex:
Case Manager Client Order Date
James Bond Pussy Galore 01/05/2005
Oliver Cromwell Janet Roundhead 05/25/2005
Alan Turing E. Nigma 08/20/2005
James Bond Holly Goodhead 12/18/2004
What I would like is for the first James Bond record to disappear, as it is later than the last record, giving me only the FIRST Order Date for each Case Manager. Is this possible? If so, what is the criterion expression?
View 10 Replies
View Related
Sep 20, 2004
I have a table that contains ID numbers plus appointments that these patients have had with a carer. The appointments are a dd/mm/yyyy entry and there are a number associated with every patient.
I need to isolate the first/earliest date the patient was seen. What is the criteria I use in a query??
View 1 Replies
View Related
Dec 14, 2012
I would like for my report for default to the earliest and latest date ,should the user not select a start date and end date.
I have read about using MIN and MAX , but i am not sure how this would work.
View 3 Replies
View Related
Dec 13, 2013
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
View 12 Replies
View Related
Jan 12, 2013
How to use combo box that drops down the date received from which the user can select todisplay "receipt form" on and after that date. [need to use macro]
View 4 Replies
View Related
May 8, 2014
I have a query that lists the total number of parts received by month. I would like to be able to compare those monthly totals with a rolling 12 month total. I've been able to get the rolling 12 month total, but I can only get it to work for one 12 month period.
Is there a way to get a 12 month total for each month? In other words for April, I would like a 12 month total of May 2013 through April 2014. For May, I would like a 12 month total of June 2013 through May 2014. Is that possible? If so, how would you go about doing it?
I have a field in my table titled REL_MONTH that shows a negative number for how many months ago something is (i.e. 00 = May, -1 = April, -2 = March).
View 2 Replies
View Related
Apr 10, 2015
I would like to create a query to show only the Work Orders that all parts ordered have been received.For the Work Order, each part ordered has a "complete" yes/no checkbox.If I use that field in the criteria of the query, it does return only parts that are "complete." However, it still shows me a Work Order where there are parts ordered but not received "not complete." how to set the query to exclude those cases and only return Work Orders that all parts ordered on the Work Order are received "complete."
View 3 Replies
View Related
Feb 6, 2006
I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.
However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.
View 8 Replies
View Related
Feb 27, 2005
hi all, Im working on a simple timesheet to keep track of time worked, but ran into two problems.
1.) The expression i'm using works fine for some times, and others not so good (get negative number).
2.) It only figures the hours but not the mins. (it rounds them up).
Does anyone one have a sampale DB or know a expression dealing with figuring out time differences.
If you need more explaintion on what i'm trying to do I've attached a sample DB.
Thanks,
Rich M.
View 1 Replies
View Related
Mar 17, 2008
Hello again,
I have a query which has a parameter called [Enter Date as DD/MM/YYYY].
This filters out records from my table which match those entered by the user.
However, when the query is run, Access is asking the user to enter the criteria twice. The first time it is labelled Expr1, the second time it is Enter Date as DD/MM/YYYY. There is obviously something wrong in my query, can anybody point me in the right direction here?
Thanks in advance;)
swifty
View 4 Replies
View Related
Apr 26, 2013
I'm trying to sum up 2 expressions.
1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)
Now with a third expression I like to sum those 2 up.
3rd. Used: [UsedRES]+[UsedAF]
The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.
When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.
View 5 Replies
View Related
May 11, 2014
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
View 7 Replies
View Related
Aug 16, 2007
I need some advice on what is the best way to import data. I have a access 2003 database that tracks converter boxes that my company sends out for repair. Currently they have a excel file that is imported into the tables which establishes the converters that are out being fixed. When they come back from service they enter the date manually from the packing slip by searching for the converter in the forms search. We now are able to get the received converters on a excel spreadsheet. Is there a way to import the date from the excel file to the access table. Any advice would be great.
Gregg
View 2 Replies
View Related
Sep 15, 2006
Hi,
I want to run a query that returns only records where we haven't received 2 items for them from a possible 4. I can't seem to get my head around it!
At the moment I have:-
Recd Date 1Recd Date 2Recd Date 3Recd Date 4
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
But obviously this isn't correct as it would return all records! I only want to show records, which haven't got 2 received dates.
I thought about counting the number of received dates in a record but wasn't sure how to code this up?
Any ideas gratefully received!
Thanks,
Dan
View 7 Replies
View Related
Oct 12, 2005
I'm a novice Access 2003 user who doesn't know any VBA. I've been trying to devise a database for a college library to record which newspapers are delivered each day. I have 2 tables at the moment: Newspapers and Deliveries.
Newspapers consists of Name (primary key), Price, and a Yes/No field for each day of the week. Deliveries consists of Name and Date.
My problem is in constructing a form to deal with selecting which papers have been delivered each day. The Yes/No field for each newspaper should be used to choose which ones to show on the form (i.e. some newspapers are delivered weekly on certain days and I don't want these showing on the other days).
Any help appreciated.
View 9 Replies
View Related
Feb 18, 2014
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Set rs = Nothing
Set rs2 = Nothing
Set db = CurrentDb
[code]....
View 5 Replies
View Related
Jan 29, 2013
I am trying to convert a CSV file that I receive from UPS to a simpler table. I have over 3000 entries from 25 different locations and I need to evaluate the shipping cost. Is there a way in Access to convert the file as attached or should I be trying to use Excel?
View 2 Replies
View Related
Jul 22, 2014
I'm trying to automate some processes. My company uses Outlook 2010 and every day we need to go into our "Sent" folder and rename the Subject line of all of our sent emails. We change the subject to a standardized format depending on the content of the email and who the email was sent to. These are then used as part of an equation to tally our Transactions Per Hour. (TPH)
I've already designed a small Access 2010 utility to track the TPH, but I would like to add the functionality to parse some data from the subject lines of my "Sent" folder, and automatically make changes to save some time.
Here is my thought process:
- Click "Process Sent Emails" button from TPH utility.
- Access runs through each email in the "Sent" folder (or perhaps only selected emails) and changes the Subject line per simple Select Case criteria (I can figure that part out).
View 2 Replies
View Related
May 7, 2013
My department handles all two-way email communication with our customers. We have 8 different email addresses that we use depending on the customer service issue. I'm looking for a way to continuously collect 3 pieces of data from every email that is received: 1) Date Received, 2) Time Received, and 3) To Field (which of the 8 email addresses it was sent to).
As you can imagine the collection of this data to look for trends to assist with staffing needs, as well as analyze build reports for the company to review. I've been working with my IT department on this but they are not sure where to start. My guess is that I need to have them collect the data as it is coming into the email server, right? I'm good with VB and I've built some VBA scripts recently that collect Outlook information, but these only work if the computer I'm using stays on all the time which is not always practical. We'd like to have this database stored on one of our shared drives which collects this data indefinitely from our email server.
View 1 Replies
View Related
Mar 4, 2014
I have a database that I use for keeping track of clients and printing invoices using a form/sub-form and report/sub-report. I want an image to be visible on my sub-report when I choose Received Payment in my sub-form. Right now I have my image set to visible = no.
View 14 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Jun 7, 2007
Please advise how to attach documents within a database. I have Office XP (2002).
View 1 Replies
View Related
Mar 4, 2008
I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?
View 7 Replies
View Related
Jul 29, 2006
Help me what is wrong with this expression:eek: :
=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))
View 4 Replies
View Related
Apr 13, 2007
I need to be able to scan documents into individual records in access and I was wondering if this could be done.
View 2 Replies
View Related
