469,330 Members | 1,290 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

query when a date is between two field values

12
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 18945
NeoPa
32,181 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
MrChris
12
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
NeoPa
32,181 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
MrChris
12
How would I go about getting my Metadata?

I'm using Access 2003
Jan 12 '07 #5
NeoPa
32,181 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
MrChris
12
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

Reports

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.

Query
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]));
  4.  
Any clues?

Thanks again for your help
Jan 12 '07 #7
NeoPa
32,181 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
MrChris
12
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
MrChris
12
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.

Sorry.
Jan 12 '07 #10
NeoPa
32,181 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
NeoPa
32,181 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.

Sorry.
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
MrChris
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
NeoPa
32,181 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
NeoPa
32,181 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
MrChris
12
That is precisely what I wanted!!

Thanks you so much for you help!
Jan 12 '07 #16
MrChris
12
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
NeoPa
32,181 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

Post your reply

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

Similar topics

2 posts views Thread by Willem | last post: by
10 posts views Thread by Kenneth | last post: by
8 posts views Thread by Tony Williams | last post: by
2 posts views Thread by lewie | last post: by
5 posts views Thread by DeanL | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.