query when a date is between two field values

[MrChris]

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!

[NeoPa]

The idea would be something along the lines of :

SELECT *
FROM tblOpCallout
WHERE [Date entered by Operator] Between [StartDate] And [EndDate]

[MrChris]

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

[NeoPa]

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

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

[MrChris]

How would I go about getting my Metadata?

I'm using Access 2003

[NeoPa]

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 :)

[MrChris]

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

SELECT [get request by date].Date, Jobs.ID, Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], Jobs.[Site Name], Jobs.[Site Address]
FROM [get request by date], Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
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

[NeoPa]

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.

[MrChris]

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

Table Name=Ops
ID; Autonumber
First Name; text
Last Name; text
Mobile; text

The Job Database

Table Name=Jobs
Date Start; Date/Time
Date End; Date/Time
Site Name; Text
Site Address; Memo
Notes; Memo

is this correct?

I apologise if I get it wrong.

[MrChris]

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.

[NeoPa]

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.

[NeoPa]

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).

[MrChris]

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.

[NeoPa]

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.

[NeoPa]

Don't forget to visit the post above (#14) first but try this :

SELECT CDate([Enter Date Here :]) AS SelectDate, Jobs.ID, 
       Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], 
       Jobs.[Site Name], Jobs.[Site Address]
FROM Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
WHERE (CDate([Enter Date Here :]) Between [Jobs]![Date Start] And [Jobs]![Date End]);

[MrChris]

That is precisely what I wanted!!

Thanks you so much for you help!

[MrChris]

Do you know if there is a book or somthing online where I can learn expressions?

once again.. thank you for you help!!!

[NeoPa]

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.