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 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?
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!
I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.
Field: LogDate Table: tblWorkLog Total: Count Corsstab: Value
Is there anyway to exclude weekends and holidays from that value?
I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?
***Addition***
Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.
So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-
>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]
Simple and it works fine.
I now need to base a second report on a Crosstab query based on Query1. Follow me?
Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."
How do I get round this or can I?
The bottom like is I want to fire off printing the report from dates input to a form.
I`m using a calender to pick a start date and end date shown in two combo boxes. I can store this value in a table with fields StartDate and EndDate I want the user to be able to pick the dates and show query records between these dates I thought i could use something like - Between"StartDate","tblDate" And" EndDate","tblDate" but it brings a syntax error anyone help ?
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 a form where the user can enter a start date and an end date into two textboxes. The default for both textboxes is today's date. The user can then view or print a report of transactions between those two dates. I would like for the default to be the current day's transactions.
The report will not pick up any transactions from a table if the dates entered are the same date. If the dates are different, it will get the transactions. For example, a txtStartDate of 8/29/2006 and a txtEndDate of 8/29/2006 will not pick up any data. A txtStartDate of 8/28/2006 and a txtEndDate of 8/29/2006 will get the transactions.
Here is my query:
SELECT Transactions.*, Hoods.* FROM Hoods INNER JOIN Transactions ON [Hoods].[ID]=[Transactions].[BoxID] WHERE ([Transactions].[TDate] Between [Forms]![DateQuery].[txtStartDate] And [Forms]![DateQuery].[txtEndDate]) ORDER BY [Transactions].[TDate];
The report displaying the transactions is based on the query.
I'm almost certain that the reason for my problem is that the dates are formatted as long in the table - 8/29/2006 11:13:00 AM. What would be the easiest solution? If I format the start and end dates on my form as long, I will need to use a default of today's date at 12:00 am for the start date. How would I do that? I could also reformat the date in the query so it will do an accurate search. What is the best way to do that? Another option would be to change the date format in the transaction table. I would rather not change it.
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?
I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.
I think this might be a typical question for query builders, so I apologize in advance for asking something so basic.
I have a form with two controls (start_date) and (finish_date).
Is there a way that I can create a query that will count the number of times a "source" has been entered into a table?
For example, I have a database where potential customers call and ask about our services. We ask them "Where did you hear about us?", hence the "source" field (which is a drop down combo box to normalize that field's data). With this record is their "dateofcall" which is (obviously) a date field.
I'd like to create a report that will count the number of times a "source" has been entered between two dates "dateofcall" (the start and finish date above).
I have tried many types of queries and haven't found any success. The nice thing about the two form controls is that I can use those two controls for a variety of all types of queried reports. (the user enters a start and a finish date, fires a command button that generates a given report between those two dates). And it works well!
