I've got an invoices table in my database, and I've got a payment approved checkbox that is false by default and a decision date field.
I want to find all outstanding invoices, and thought the best way to do this would be to check if the decision date is null, but that doesn't work.
How do I check for a blank field?
I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:
I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).
I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.
I have put a Foreign Field in my Sales table for the Price key field.
My dilemma is matching the Sales with the Prices.
When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.
I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
View 1 Replies View RelatedI have 12 fields, each containing a number. I want to display the last non zero value in a query. I've looked at "last" but this returns the items in chronological order, which is not neccessarily the order my data is entered in.
eg.
fields: 1 2 3 4 5 6 7 8 9 10
data: 0 7 3 0 0 5 4 0 0 0
I would want the query to return 4, even if the last value entered was the 7.
To make it more complicated, I have many rows of data for each set I am using, and have to aggregate these based on a key column
eg.
Fields: key 1 2 3 4 5 6
Data: 1 0 0 0 2 0 1
1 0 0 0 3 0 3
1 6 5 4 3 2 1
2 1 2 3 4 5 6
2 0 0 0 0 0 0
Any tips?
Cheers
Kev
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I need to find all MaintItems which field MIC don't exist in either MIC1 or MIC2. So the only result which I need to see is MaintItem '5100161086' as MIC <> MIC1 Unfortunately I can create query which would approve '5100662734' as well
MaintItem ------MIC------ MIC1 ------ MIC2
5100634036 50000173 50000173
5100662790 50000174 50000174
5100662734 50000145 50000145 50000180
5100662734 50000180 50000145 50000180
5100161086 500001711 50000173
So bottom line I need a query which will display only the row below as MIC doesn't exist in neither MIC1 nore MIC2. Also I will have to deal with much more MICs soon so maybe there is a way to concatenate them and compare, isn't it? 5100161086 500001711 50000173
Attached query is what I got so far, but that displays last 3 rows of table above
I am trying to create a query and in the expression builder to find the max value from 3 different fields in 3 other queries.So each of the fields are called "TopSpeed" and the 3 queries are called "Test", "Training" & "Race".So in my new query I would like to return the MAX speed value from the 3 combined "TopSpeed" fields.Something like
MAX(DMAX([TopSpeed], [Test]), DMAX([TopSpeed], [Training]), DMAX([TopSpeed], [Race]))
i need to find sum of three or more fields in a table.
View 6 Replies View RelatedIn a form I have Two fields (which are of interest):
1) EmployeesName which is a lookup from another table named
TBLEmployees that contains two Fields
EmployeesName
EmployeesNumber
2) Second Field named EmployeesNumber which currently also using a lookup from table named TBLEmployees
if possible what I want to try and do when I select EmplyeeName from the dropdown list of names I want the EmplyeeNumber to be inserted into this field automatically rather than searching through the dropdown list again is this possible?
I need to do a query to find a field value from similar fields. Table 1 has fields(customer id,...) and table 2 has fields (customer id, address,...). I need to use customer id from table 1 to find address in table 2. Both customer id fields in both tables is the same.
View 4 Replies View RelatedI have a query based on an SQL database that our company uses to document problems and resolutions. I need to run a report that will show all the reports that were completed in the last 30 day. The field that the approval date is documented is called dbo_approved. Sometimes a report is approved and then at a later date reopened to add something then approved again. The design of this database is such that the first approval is one record and the second approval is a different record. So, using a normal query I will find some records that were previously appproved which I don't care about. I only want a list of reports that were approved for the first time within the last 30 days. Is there a criteria I can use in my query to only look for the first approval date?
As always I appreciate the help.
Jim
I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.
File #
setupdate
transaction
100001
1/1/2014
1
100001
3/5/2014
2
100001
5/5/2014
3
I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.
While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies View RelatedWe are a non-profit that does blind mailings for our membership drive.
The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
I want query to find duplicate records, i have two field in one table
Cusip and category
cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used
like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A
result should be
CusipCategory
123R
123R
123A
I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.
What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.
So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.
I have a date/time format field.
"8/21/2007 10:02:34 PM" is what my column shows.
What would I add to the criteria to the query to find all records added today?
at the end of the day my user needs to run a query that will show everything from today. Thanks.
Hi all,
I have a problem finding the last date entered in a table..The purpose is to find the record which entered last in a table. Is there any way to find the last record with date ? I am attaching a sample db with this. Please look that if my question is not clear here..
Thanks in advance
thanks
I have a database with 8 years of info on customers orders. I need to find out when each customer first used our services.
View 8 Replies View RelatedI have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.
View 6 Replies View RelatedI'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".
Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.
How this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.
It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?
View 2 Replies View RelatedI have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
View 3 Replies View Related