Date Tracking Query

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

When I correct all the fields I get this error at the HAVING clause.

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect. (Error 3136)

Any ideas what might cause this?

Thanks again






"KARL DEWEY" wrote:

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

Post your query SQL as it is hard to guess withou seeing what you did.
--
Build a little, test a little.


"sonofroy" wrote:

When I correct all the fields I get this error at the HAVING clause.

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect. (Error 3136)

Any ideas what might cause this?

Thanks again






"KARL DEWEY" wrote:

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

SELECT [tblvehicles].[Comm Number]
FROM tblvehicles LEFT JOIN tbldispatch ON tblvehicles.VechID =
tbldispatch.VechID;
GROUP BY [tblvehicles].[Comm Number]
HAVING ([tbldispatch].[VechID] Is Null) OR (Max([CheckOut]) Is Not Null AND
[CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time wanted]) AND
[CheckOut] Is Not Null)


"KARL DEWEY" wrote:

Post your query SQL as it is hard to guess withou seeing what you did.
--
Build a little, test a little.


"sonofroy" wrote:

When I correct all the fields I get this error at the HAVING clause.

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect. (Error 3136)

Any ideas what might cause this?

Thanks again






"KARL DEWEY" wrote:

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

You had a semicolon at the end of the FROM statement - a semicolon only goes
at end of SQL.
You had an extra closing parenthesis and missing one opening parenthesis.
Try this --
SELECT [tblvehicles].[Comm Number]
FROM tblvehicles LEFT JOIN tbldispatch ON tblvehicles.VechID =
tbldispatch.VechID
GROUP BY [tblvehicles].[Comm Number]
HAVING ([tbldispatch].[VechID] Is Null) OR (Max([CheckOut]) Is Not Null AND
[CheckIn] Is Not Null) OR ([EstRtn] = CVDate([Enter date-time wanted]) AND
[CheckOut] Is Not Null);
--
Build a little, test a little.


"sonofroy" wrote:


SELECT [tblvehicles].[Comm Number]
FROM tblvehicles LEFT JOIN tbldispatch ON tblvehicles.VechID =
tbldispatch.VechID;
GROUP BY [tblvehicles].[Comm Number]
HAVING ([tbldispatch].[VechID] Is Null) OR (Max([CheckOut]) Is Not Null AND
[CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time wanted]) AND
[CheckOut] Is Not Null)


"KARL DEWEY" wrote:

Post your query SQL as it is hard to guess withou seeing what you did.
--
Build a little, test a little.


"sonofroy" wrote:

When I correct all the fields I get this error at the HAVING clause.

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect. (Error 3136)

Any ideas what might cause this?

Thanks again






"KARL DEWEY" wrote:

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks

I tried it as you posted and then this error came up.


You tried to execute a query that does not include the specified expression
name as part of an aggregate function. (Error 3122)


"KARL DEWEY" wrote:

You had a semicolon at the end of the FROM statement - a semicolon only goes
at end of SQL.
You had an extra closing parenthesis and missing one opening parenthesis.
Try this --
SELECT [tblvehicles].[Comm Number]
FROM tblvehicles LEFT JOIN tbldispatch ON tblvehicles.VechID =
tbldispatch.VechID
GROUP BY [tblvehicles].[Comm Number]
HAVING ([tbldispatch].[VechID] Is Null) OR (Max([CheckOut]) Is Not Null AND
[CheckIn] Is Not Null) OR ([EstRtn] = CVDate([Enter date-time wanted]) AND
[CheckOut] Is Not Null);
--
Build a little, test a little.


"sonofroy" wrote:


SELECT [tblvehicles].[Comm Number]
FROM tblvehicles LEFT JOIN tbldispatch ON tblvehicles.VechID =
tbldispatch.VechID;
GROUP BY [tblvehicles].[Comm Number]
HAVING ([tbldispatch].[VechID] Is Null) OR (Max([CheckOut]) Is Not Null AND
[CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time wanted]) AND
[CheckOut] Is Not Null)


"KARL DEWEY" wrote:

Post your query SQL as it is hard to guess withou seeing what you did.
--
Build a little, test a little.


"sonofroy" wrote:

When I correct all the fields I get this error at the HAVING clause.

The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect. (Error 3136)

Any ideas what might cause this?

Thanks again






"KARL DEWEY" wrote:

I did not see VechID in your query from tblVehicles so I guesed.
--
Build a little, test a little.


"sonofroy" wrote:

Im kinda on to this but I was wondering where in the join expression is the
Commission Number field for the "DisPatch" table coming from. I keep getting
Syntax error in that statement and I do not have a Commission Number field in
table dispatch I am only using the VechID field.

"KARL DEWEY" wrote:

UNTESTED UNTESTED

I did not have the field names you used so you need to substitute mine for
yours --

SELECT [tblVehicles].[Commission Number]
FROM tblVehicles LEFT JOIN Dispatch ON [tblVehicles].[Commission Number] =
[Dispatch].[Commission Number]
GROUP BY [tblVehicles].[Commission Number]
HAVING ([Dispatch].[Commission Number] Is Null) OR (Max([CheckOut]) Is Not
Null AND [CheckIn] Is Not Null)) OR ([EstRtn] = CVDate[Enter date-time
wanted]) AND [CheckOut] Is Not Null)

1- ([Dispatch].[Commission Number] Is Null) == Never been dispatch.
2- OR (Max([CheckOut]) Is Not Null AND [CheckIn] Is Not Null) == Dispatched
but has been returned.
3- OR ([EstRtn] = CVDate[Enter date-time wanted]) AND [CheckOut] Is Not
Null) == Out but due back on or before need.

--
Build a little, test a little.


"sonofroy" wrote:

In might lie my problem with my query I DO have TWO tables and my query is
definitely messed up I am unsure how to write both tables in

"KARL DEWEY" wrote:

What I posted as a suggestion was TWO tables - Vehicles & Dispatch.
Your query indicates you made it all one table.
--
Build a little, test a little.


"sonofroy" wrote:

I tried intergrating the one from the previous poster with variations

SELECT [Commission Number]
FROM tblVehicles
WHERE #08/03/2009 14:50:00# =[CheckOut]
AND (#08/03/2009 14:50:00# =[CheckIn]
Or [CheckOut] Is Null)

My thinking was if I inversed the less than greater than it would give me
everything not already checked out. I have something wrong somewhere because
it keeps asking for my input. I am pretty novice at SQL building I have only
done a handfuls. Thanks for helping me

"KARL DEWEY" wrote:

Post the SQL of your query.
--
Build a little, test a little.


"sonofroy" wrote:

I agree with the table desing you have suggested and I went ahead and created
it that why but now I am having difficulties writing the query for it. It
keeps asking for my to input the values for vehicle number and I do not that
to occur. When someone requests a vehicle I want to run the query and only
ask for a check out date to find what vehicles are available

"KARL DEWEY" wrote:

At 08/03/09 2:50 the data for 64219 would not look like this -- 08/03/09
12:00 pm 08/03/09 3:00 pm as it has not returned at that time.

You should have a table with the vehicles and their information with a
second table for dispatching and related as one-to-many.
Vehicles --
VechID - Autonumber - primary key
VechNum - text
Type - Sedan 2Dr
Make - Prexous
Year - 1994
etc.

Dispatch --
DisptID - Autonumber - primary key
VechID - number - long integer - foreign key
CheckOut - DateTime
CheckIn - DateTime
EstRtn - DateTime
Driver -
Purpose -

--
Build a little, test a little.


"sonofroy" wrote:

Sorry about that. I have a table "tblvehicles" with fields as described below
[Vehicle Number],[Date Assigned],[Time Assigned],[Date Returned],[Time
Returned]

sample records of vehicles checked out and this information is in the
"tblvehicles"
64202 08/03/09 7:00 am 08/03/09 2:00 pm
64219 08/03/09 12:00 pm 08/03/09 3:00 pm
64077 08/03/09 7:00 am 08/03/09 9:00 am

What I am trying to accomplish is to run a query of some sort that will give
a list of which vehicle numbers are available at the time of the run query
that are not checked out. In this case if the query was ran on 08/03/09 2:50
pm vehicle 64202 and 64077 should be returned in the query.

"Duane Hookom" wrote:

I'm not sure how you can have "unique vehicles" with multiple times a day.
Please provide table, query, and field names with some sample records.

Hint: when you get a reply with "can we assume", it generally begs you to
provide greater detail so we don't have to "assume".
--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

my table is setup to only have unique vehicles but each vehicle can be
checked out multiple times a day so there is part of my confusion not to
create duplicates

"Duane Hookom" wrote:

Can we assume you have a table of unique vehicles? Can we assume your
returned fields always have values for expected returns? Can we assume your
query name is qryNoName?

Try something like:

SELECT tblVehicles.*
FROM tblVehicles
WHERE [Vehicle Number] NOT IN
(SELECT [Vehicle Number]
FROM qryNoName
WHERE Now() Between [Date Assigned] + [Time Assigned] AND [Date Returned]
+ [Time Returned])

--
Duane Hookom
Microsoft Access MVP


"sonofroy" wrote:

I am working on vehicle tracking database and I have a query with these fields

Vehicle Number Date Assigned Time Assigned Date Returned Time
Returned


How can I create a function that will tell me what vehicles are available at
the time the query is opened for assignment so I do not create a duplicate. I
have 12 unique vehicle numbers to choose from. I have tried unmatched query
and several different functions but nothing is working. Please help! Thanks