Received Dates Query
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 Replies
ADVERTISEMENT
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
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
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
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
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
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 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
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 12, 2014
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
View 1 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Mar 17, 2008
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Anyone.....
View 5 Replies
View Related
Oct 30, 2005
I have a report that once clicked a form comes up to choose the dates one would like to filter by. It has a Start Date and End Date. After the user picks the Start Date and End Date the report will be shown for those dates. The record source for this report is a query
I wanted to use this same date picker form for another report. I included the same expression into my query but I keep getting an error.
Field: Expr2: [LogDate] Between [forms]![frmWhatDatesWorkLog].[txtRecvStart] And [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Or [forms]![frmWhatDatesWorkLog].[txtRecvStart] Is Null Or [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Is Null
Criteria: True
When I tried that I get the following error: "The Microsoft Jet database engine does not recognize '[forms]![frmWhatDatesWorkLog].[txtRecvStart]' as a valid field name or expression.
I don't know why I get this because I use the exact same thing in the other query and it works perfectly fine.
Any suggestions?
View 6 Replies
View Related
Feb 9, 2006
Hi All
This is pretty urgent so your help would be greatly appreciated! I need to do a query where i can find companies who were telemarketed between the dates of 01/01/2006 and 31/02/2006. When i type this in the criteria box however, it fails to do anything. PLEASE HELP!!!
Many thanks
View 7 Replies
View Related
Apr 12, 2006
I am trying to create a query in Access to select records from a table depending on the value in a Date field.
I need the query to select records which have dates between or equal to 01/09/last_year and 31/07/this_year.
Can anyone advise how I can do this?
Thanks in advance if you can help.
Gary
View 6 Replies
View Related
Jun 1, 2006
Hi all...
I have two queries feeding into one query that gives me a desired result. However I need it to do this..
Right now is show everything that has an incident On 07/04/2006 ( #07/04/2006# ) and before.. that part works.... What I would like it to show is if it has a hit for any 07/04/YEAR..... so if there is an incident for 07/04/2005 and an incident for 07/03/2004 show me...
Right now all of my dates look like this in the criteria section of the queries:
(one query looks for same locations on 7/4/2006, one query looks for that same location on or before 7/4/2006 and the last one tells me if there are any matchs)
q1= #7/4/2006#
q2= <=#7/4/2006#
q3= <=#7/4/2006#
How do I show for any year??
R~
View 14 Replies
View Related
Nov 22, 2006
Hi there,
I'm making a database for a small library collection. I want to make a query for all overdue books, so if the Date which the book is due has passed, it will come up in the query. What do I put beneath the 'Date due' field?
So If 1 day has passed that date, it will be overdue.
Thanks for any help.
View 3 Replies
View Related
Dec 15, 2006
Hi,
i want to create a booking system but i've a problem
i've these tables
cars
_ car_id
_ car_name
booking
_ book_id
_ book_start
_ book_end
_ car_id_fk
and the following data:
car 1: booked from 11.01.2007 until 14.01.2007
car 1: booked from 19.01.2007 until 24.01.2007
car 2: booked from 02.01.2007 until 28.01.2007
car 3: booked from 25.01.2007 until 29.01.2007
car 4: no booking
how can i make if i want that a query show the available cars
for the period (example): 12.01.2007 - 16.01.2007 ??
'cause i've found something and it'll show me car 3 and car 1 available..but the problem is that car 1 is still out on the 12th of january..(cause this query will show only the cars already booked in other periods that don't interfere with the request).
How can i make a query that shows the cars available and NOT those periods??
thx in advance
View 3 Replies
View Related
Mar 19, 2007
Im trying to build form with two calendar controls which asks query for records between two dates.
Calendar control works fine, no problem with that, but my query isnt.
What im trying to do:
I have a table with records, each record has field with date of creation (for example 2007.11.25.) formated as date/time (im working with latvian local settings)
In my form, I have a two text fields which referes to each calendar control, so in query (whith is trigered by comand button) i have formula like this:
Between [Forms]![Form1]![text5] And [Forms]![Form1]![text7]
Everything seams to be fine (for me :)) but if im searching dates between 2007.01.01. and 2007.03.01. im getting not only correct records between those dates, but also random records from last year.
I have tryed many diferent formulas, but nothing is working, Im stuck in this.
Ofcourse, my problem is that Im not good with VBA :) I dont understand it at all :)
Sorry if someone allredy posted solution of my little problem somewhere in Jungles of these Treads :)
View 14 Replies
View Related
Jul 29, 2007
Hello,
I've created a query that finds all of the dates for specific types (monthly, quarterly, semiannual, annual) of evaluations. It works great, shows all evaluation dates for each person.
What I really need it to do is just show the most recent (i.e. latest) evaluation date for each person. Each person could have 10 or more records; just need the latest one. Eventually want to update a table that has a last evaluation field on it for every person.
I have set the top values to 1, but that just showed the latest of all the evaluations (i.e.one person). I couldn't seem to find any clues in the forums. Anyone have some suggestions/pointers?
I've attached the query and main tables.
Thanks:confused:
View 5 Replies
View Related
Apr 18, 2008
Hi everyone, I am a relatively inexperienced access user and I am having problems with a query on my database.
Im creating a hotel reservation booking system and I want to run a query that will show active bookings on a certain date. For example, if i enter the date 2/1/2008 I want it to show bookings made from dates that span this date.
Example
Booking Start Date Booking End Date
31/12/2007 4/1/2008
1/1/2008 3/1/2008
So now if I enter 2/1/2008, I want these 2 records to appear. Any advice about how to do this? It all seems a bit complicated to me
View 2 Replies
View Related
Apr 5, 2006
Dates drive me nuts!!
attached is a db. I'd like a query to count BMdetentions and BmRTC in a 30 day period where the count is >2.
for the life of me I can't seem to figure it out.
Any help is appreciated
View 1 Replies
View Related
Apr 9, 2015
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
View 4 Replies
View Related
