472,336 Members | 1,229 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,336 software developers and data experts.

query when a date is between two field values

I'm trying to figure out how to get a query to work by picking up a date in between 2 date entries.

this is (kind of) how the database works:

User put in the 'Start Date' and 'End Date' for when an operator is out.

I run a report to find out what operators are out a date I specify.

So say if I have an op out from (start date) 10/11/2006 to (end date) 13/11/2006.

What kind of query would I have to create so it picks up that that op is out on the the 12/11/06? (when I enter 12/11/06 into the query)

I know its a bit vague and any help/pointers would be great!
Jan 10 '07 #1
17 19280
32,511 Expert Mod 16PB
The idea would be something along the lines of :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tblOpCallout
  3. WHERE [Date entered by Operator] Between [StartDate] And [EndDate]
Jan 10 '07 #2
Im not 100% sure I follow... I get the the between bit... but I'm not sure my MS Access skill strech as far as to how to get it to work.... can I post my DB on here for people to look at or i that a bit much???

Thanks again for the help
Jan 11 '07 #3
32,511 Expert Mod 16PB
Better would be to express your problem clearly.
The SQL query is done already. Is it that you don't know how to create a saved query (QueryDef) in Access from this SQL code?
For more detailed help you will probably have to post some Meta-Data for your database.

Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Jan 11 '07 #4
How would I go about getting my Metadata?

I'm using Access 2003
Jan 12 '07 #5
32,511 Expert Mod 16PB
Tables are easy enough, you open them in design mode and all the info you need is there in front of you. Unfortunately, you can't just copy and paste it across :(
For queries, open them in design mode too, and then choose View SQL from the View menu. This SQL code can be copied and pasted across :)
Jan 12 '07 #6
I think I've fudged it up

I have in my database

Tables -

Operators - containing opertaor info
Jobs - a job entry form (with the ever imporatat start and end date)
Date Request - I thought I could just use a table entry in my query to pick yo the date and use it in a BETWEEN expression... but it deosnt work


Jobs - a printable report of the jobs - current runs on the critera that I enter a specific day, which at the moment has to be the start of end day for it to work.

this is my querie, with now comes back with an error
Expand|Select|Wrap|Line Numbers
  1. SELECT [get request by date].Date, Jobs.ID, Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], Jobs.[Site Name], Jobs.[Site Address]
  2. FROM [get request by date], Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
  3. WHERE ((("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End] And ("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End] And ("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End] And ("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End] And ("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End] And ("WHERE «Expr» [get request by date]![Date]") Between [Jobs]![Date Start] And [Jobs]![Date End]));
Any clues?

Thanks again for your help
Jan 12 '07 #7
32,511 Expert Mod 16PB
To look at this in detail I need the info requested or I'll just have to keep doing it over and over again.
I'm not going to do that.
I need info on all the fields (that are used in the query at all) - especially including the types. Just as was posted quite clearly in post #4 in fact. You can forget about the PK / FK bits as I don't think they come into this.
Your SQL can certainly be improved but, as I say, I won't even start on that without the info being provided.
Jan 12 '07 #8
So sorry...

I understand that you are dedicating time to this, and I thank you for your help so far..

here are the fields for each table that I would like in the query

The Operator Database
Expand|Select|Wrap|Line Numbers
  1. Table Name=Ops
  2. ID; Autonumber
  3. First Name; text
  4. Last Name; text
  5. Mobile; text
The Job Database
Expand|Select|Wrap|Line Numbers
  1. Table Name=Jobs
  2. Date Start; Date/Time
  3. Date End; Date/Time
  4. Site Name; Text
  5. Site Address; Memo
  6. Notes; Memo
is this correct?

I apologise if I get it wrong.
Jan 12 '07 #9
I forgot to mention...

There is a Lookup between the Operator and Jobs database called ID that gets the First and last name + the mobile number of the operator assigned to the job.

Jan 12 '07 #10
32,511 Expert Mod 16PB
That's much better thanks.
I will look at this now (Time allowing).
BTW The [Ops] and [Jobs] objects are referred to as tables rather than databases. They would typically both be stored inside the same database.
Jan 12 '07 #11
32,511 Expert Mod 16PB
I forgot to mention...

There is a Lookup between the Operator and Jobs database called ID that gets the First and last name + the mobile number of the operator assigned to the job.

I've seen this post now (missed it before my last one).
This is all good stuff and I now know you're trying - which is all I expect.
I will, however, need the SQL of the QueryDef [get request by date] before I can continue (See Post #6 for how to get this).
Jan 12 '07 #12
The Table [get request by date] is a table with just one date entry... I tried using it in the query as a workaround, but I dont think my idea would work... so it's obsolete.
Jan 12 '07 #13
32,511 Expert Mod 16PB
I think I'm nearly there (reread most of the posts again a few times) but I can't find anything that tells me how you want to select the date to search on.
I have Start & End dates in the record layout but nothing (that I could find) to say how the operator says which date to select on.
I can easily add a popup question within the query (SQL) if you like but you may prefer to get it from a form or something - in which case I'll need to know those details too.
Jan 12 '07 #14
32,511 Expert Mod 16PB
Don't forget to visit the post above (#14) first but try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT CDate([Enter Date Here :]) AS SelectDate, Jobs.ID, 
  2.        Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], 
  3.        Jobs.[Site Name], Jobs.[Site Address]
  4. FROM Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
  5. WHERE (CDate([Enter Date Here :]) Between [Jobs]![Date Start] And [Jobs]![Date End]);
Jan 12 '07 #15
That is precisely what I wanted!!

Thanks you so much for you help!
Jan 12 '07 #16
Do you know if there is a book or somthing online where I can learn expressions?

once again.. thank you for you help!!!
Jan 12 '07 #17
32,511 Expert Mod 16PB
I'm pleased that sorted it for you.
All I know online are (Links to useful sites) & (Tutorials on Access and VBA), but they can be very helpful indeed - with links on to other resources.
Jan 12 '07 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Willem | last post by:
Hi there, I'm sort of new with doing much record manipulation with queries. Up till now I've been programming VBA and doing record looping to...
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003....
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an...
by: Tony Williams | last post by:
I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the...
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) ...
by: lewie | last post by:
I am trying to construct a semi duplicate query. I want it to match 2 fields and check a range for the 3rd match as follows date = date, title =...
by: David Horowitz | last post by:
Hi. I have a query named Query1. Let's say it looks like this: Select * from Table1 where Field1= So whenever you run the query, it prompts for...
by: Ragbrai | last post by:
Howdy All, I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then...
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year...
by: DeanL | last post by:
Hi all, I'm trying to set up a query that runs from a command button on a form (simple enough so far), what I want the query to do is take values...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.