I want to have a query that will subtract a Due Date from todays date and tell me how many days are left/passed in either a positive number or negative just like the DateDiff Function. how do i do this?
I have an artwork book which I am cataloging. Each page of the book contains 24 pices of artwork. What I need to do is display a page number to whatever record number I enter into the field.
Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.
Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.
Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.
I am a wiz at formulas in excel but am not sure yet how to make them work in access. I have a main form into which I put the date recieved of many items for each facility. Next to each of the dates I have entered I want to have a non inputable text block that will look at the date then do the necassary math automatically. Some of the formulas will also contain conditional statements based off of a date, type and rating. Can anyone suggest the best way to do this or point me toward a resource that explains how to use formulas in access. Here are a few examples of the formulas that are already in place in the excel version.
=IF(I28="Unsatisfactory",C28+60,(IF(F2="AA&E",C28+365,(IF(F2="Major Funds",C28+365,C28+1470))))) in the above formula C28 is a date entered by the user, otherwise most formulas I need to input are simply (date + 1 year or date + 4 years)
What the best way to perform math functions on data in a table? I have a form and a subdatasheet in it. I want to be able to: Average out data in cells. I.E One field will have a list of numbers and I want to be able to average those out.
The other is that i want to be able to check the previous cell(year) and see if the number has gone up or down?
I have 6 fields that display a price based on a combo box selection I can get one to subtract from another but not all add up It should also be a running total so it updates as I pick more options
And how can you get the combo baxes to start out blank they all have selects already made when I open the form
I have created a table that holds the inventory information of some items i have in stock. What I want to do is this:
1.)When a customer orders something, i enter the quantity ordered and hit a process button. When this button is clicked it automatically reduces my inventory count to reflect the reduction
2.)When I receive shipments, i want to enter the information and hit process so that it automatically sets my inventory counts to reflect the addition of the new units
I am having trouble figuring out how to go about doing this. I was thinking of somehow setting up some textfields and then adding buttons that set fields accordingly.
I have a table with some prices that are charged by the pound and some that are charged by each.
tblPrices ID 1 - 1.00 per pound ID 2 - 1.50 per pound ID 3 - 2.00 per pound ID 4 - 5.00 each ID 5 - 7.00 each
My form is set up so that way I count how many containers I have and their weight, separately. I need ID 1 through 3 to do the math based on the pounds (essentially $1 per pound at 36 pounds) and ID 4 and 5 to do math based on how many of each are there ($5 each, and there are 3 of them).
I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.
I have found that where there are null values in my table - the calculation seems to break down.
Let me explain...
In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List ID - name - cost
Table 02 Item List ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart) ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do: I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says: Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
how to do anything with it outside of tables and queries. I have been tasked with creating a form to do fairly basic things. Via tutorials I have managed to use the Expression Builder to do virtually everything I need except one thing.
I need to pull a value from row 2 in a specific column from a subform and subtract it from row 1 in the same column and subform leaving behind a + or - number for the user. Similarly, I need to do the same but subtracting the LAST ROW from row 1. If there is only one row at the time the operation is being performed, a value of No Data needs to be returned instead of a number.
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?
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
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.
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!!!
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)
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.
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??
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 :)
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?
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
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
(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?
I am having an annoying problem that I can't understand. I have the following query:
SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Accom Type] FROM Bookinfx WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=#01/01/2005#) AND ((Bookinfx.[Start Date])<=#31/12/2005#));
Which I am using to find out bookings for a specific holiday park for a specific year. However, if I try to alter the parameters to find monthly figures, only some months work and others (feb, april, june, sept, nov) do not. When I try and use these months it tells me that there is a syntax error in the date in the query expression.
Any help gratefully received! :confused:
ps maybe the answer would be a different query that returned the total number of bookings for each month for a particular year and park? But if this is the case I'm not sure how to do that!