Problem with month value

[Rick Dunmire]

Hello all,

I have built a make table query to select information based on a field with
a month in it and the parameter is based on the value from two combo boxes
and the result is feed into another table which is built from the query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where the
license plate expiration month is between combo1.value and combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would get a
result back from the query where the license plate expiration months are
either 10, 11 or 12 (October, November or December). No problem here. but if
I select combo values from 11 to 01 (November to January), I get everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[John Vinson]

No problem here. but if
I select combo values from 11 to 01 (November to January), I get everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Yes, by including the year in your criteria! Access is doing exactly
what you're asking it to do: return all values of a numeric field
between 1 and 11. *YOU* know that you mean that the field is a
date/time field; there's no way for Access to know that.

Do you have the expiration year in your table? or an expiration
Date/Time? If not, you'll need a rather snarky two-level criterion:

WHERE ([Forms]![yourform]![combo1] >= [Forms]![yourform]![combo2] AND
Vehicle.license_plate_expiration_month >= [Forms]![yourform]![combo1]
AND Vehicle.license_plate_expiration_month <=
[Forms]![yourform]![combo2])
OR
([Forms]![yourform]![combo1] < [Forms]![yourform]![combo2]
AND (Vehicle.license_plate_expiration_month <=
[Forms]![yourform]![combo2] OR Vehicle.license_plate_expiration_month

= [Forms]![yourform]![combo1])

 

[Rick Dunmire]

Thanks John,
Didn't think this was an easy fix. I don't use a form for this query as of
yet, I have
been running it straight from the database. The finished product is used for
a crystal
report so I am bypassing a few things. This might sound dump but would it
recognize
the value December and January correctly instead of 12 and 01?

No problem here. but if
I select combo values from 11 to 01 (November to January), I get everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Yes, by including the year in your criteria! Access is doing exactly
what you're asking it to do: return all values of a numeric field
between 1 and 11. *YOU* know that you mean that the field is a
date/time field; there's no way for Access to know that.

Do you have the expiration year in your table? or an expiration
Date/Time? If not, you'll need a rather snarky two-level criterion:

WHERE ([Forms]![yourform]![combo1] >= [Forms]![yourform]![combo2] AND
Vehicle.license_plate_expiration_month >= [Forms]![yourform]![combo1]
AND Vehicle.license_plate_expiration_month <=
[Forms]![yourform]![combo2])
OR
([Forms]![yourform]![combo1] < [Forms]![yourform]![combo2]
AND (Vehicle.license_plate_expiration_month <=
[Forms]![yourform]![combo2] OR Vehicle.license_plate_expiration_month

= [Forms]![yourform]![combo1])

 

[John Vinson]

Thanks John,
Didn't think this was an easy fix. I don't use a form for this query as of
yet, I have
been running it straight from the database. The finished product is used for
a crystal
report so I am bypassing a few things. This might sound dump but would it
recognize
the value December and January correctly instead of 12 and 01?

If you're working with dates - WORK WITH DATES. I'd store a Date/Time
field in your table. Of course, if you store a text string "01" in a
table, Access will have NO WAY to know that this is equivalent to the
text string "January", or vice versa. Access has no intelligence -
it's just a very fast idiot, which can accurately do what you tell it
to do.

Store these dates in a Date/Time field. You can then use formatting to
display #1/23/2004# as "01" or as "January" or as "January 2003" or
however you like; you can also use the Year() and Month() functions to
extract a numeric year or month for searching.

 

[Peter Surcouf]

Rick

It's not elegant and it does not ORDER well but might do what you require

WHERE
IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon
th] Between [Combo1].[value] And
[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]
Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

 

[Peter Surcouf]

Rick

A little more thought gives the following

WHERE
[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat
ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY
[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat
ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you require

WHERE
IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon
th] Between [Combo1].[value] And
[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]
Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a field with
a month in it and the parameter is based on the value from two combo boxes
and the result is feed into another table which is built from the query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where the
license plate expiration month is between combo1.value and combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would get a
result back from the query where the license plate expiration months are
either 10, 11 or 12 (October, November or December). No problem here.

but

if
I select combo values from 11 to 01 (November to January), I get everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Rick Dunmire]

Thanks Peter,
When I put the SQL tegether and run it I get an error (Data type mismatch in
criteria expression). Here is what I ended up with when put together.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON
Customer.pk_customer=Vehicle.fk_customer) LEFT JOIN Inspection ON
Vehicle.pk_vehicle=Inspection.fk_vehicle
WHERE
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE
[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat
ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY
[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat
ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a field with
a month in it and the parameter is based on the value from two combo boxes
and the result is feed into another table which is built from the query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where the
license plate expiration month is between combo1.value and combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would

get

a but

 

[Peter Surcouf]

Rick

Not sure of your data types but for the criteria to give the correct answer
then

Vehicle.license_plate_expiration_month
Combo1.value
Combo2.value

have to be numeric, if any are not try wrapping each instance in cint()

cint( Vehicle.license_plate_expiration_month)
cint(Combo1.value )
cint(Combo2.value )

in the WHERE and ORDER clauses only.

I am assuming the SQL worked before but just gave the "wrong" answer.

Peter

Thanks Peter,
When I put the SQL tegether and run it I get an error (Data type mismatch in
criteria expression). Here is what I ended up with when put together.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON
Customer.pk_customer=Vehicle.fk_customer) LEFT JOIN Inspection ON
Vehicle.pk_vehicle=Inspection.fk_vehicle
WHERE
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a field
with
a month in it and the parameter is based on the value from two combo boxes
and the result is feed into another table which is built from the query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where the
license plate expiration month is between combo1.value and combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would

get

a
result back from the query where the license plate expiration months are
either 10, 11 or 12 (October, November or December). No problem

here.
but

if
I select combo values from 11 to 01 (November to January), I get
everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Rick Dunmire]

Yes Peter,

The data types are numeric (01 through 12).
This is the value that the Vehicle.license_plate_expiration_month
field holds and the combo boxes hold the same values.

Prior to the last set of changes everything work except for the ordering
of the retrieved info from the query.

Rick

Rick

Not sure of your data types but for the criteria to give the correct answer
then

Vehicle.license_plate_expiration_month
Combo1.value
Combo2.value

have to be numeric, if any are not try wrapping each instance in cint()

cint( Vehicle.license_plate_expiration_month)
cint(Combo1.value )
cint(Combo2.value )

in the WHERE and ORDER clauses only.

I am assuming the SQL worked before but just gave the "wrong" answer.

Peter

Thanks Peter,
When I put the SQL tegether and run it I get an error (Data type

mismatch

in
criteria expression). Here is what I ended up with when put together.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON
Customer.pk_customer=Vehicle.fk_customer) LEFT JOIN Inspection ON
Vehicle.pk_vehicle=Inspection.fk_vehicle
WHERE

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a field
with
a month in it and the parameter is based on the value from two combo
boxes
and the result is feed into another table which is built from the query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between
[combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query

where
the

license plate expiration month is between combo1.value and combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I

would
get

a
result back from the query where the license plate expiration

months
are

either 10, 11 or 12 (October, November or December). No problem here.
but
if
I select combo values from 11 to 01 (November to January), I get
everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Peter Surcouf]

They both work fine on my access 2000!

If the WHERE clause works from the first suggestion try that with the ORDER
BY of the second,

ORDER BY
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100

If that doesn't work try (long shot)
ORDER BY

Vehicle.license_plate_expiration_month-cint(Vehicle.[license_plate_expiratio
n_month]<Combo1.value)*100

Peter

Yes Peter,

The data types are numeric (01 through 12).
This is the value that the Vehicle.license_plate_expiration_month
field holds and the combo boxes hold the same values.

Prior to the last set of changes everything work except for the ordering
of the retrieved info from the query.

Rick

Rick

Not sure of your data types but for the criteria to give the correct answer
then

Vehicle.license_plate_expiration_month
Combo1.value
Combo2.value

have to be numeric, if any are not try wrapping each instance in cint()

cint( Vehicle.license_plate_expiration_month)
cint(Combo1.value )
cint(Combo2.value )

in the WHERE and ORDER clauses only.

I am assuming the SQL worked before but just gave the "wrong" answer.

Peter

mismatch

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you
require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a field
with
a month in it and the parameter is based on the value from two combo
boxes
and the result is feed into another table which is built from the
query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date
INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between
[combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where
the
license plate expiration month is between combo1.value and
combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would
get
a
result back from the query where the license plate expiration months
are
either 10, 11 or 12 (October, November or December). No problem here.
but
if
I select combo values from 11 to 01 (November to January), I get
everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Rick Dunmire]

Hey Peter,
My problem seems to be in the where clause and the LEFT JOIN in inspection
table.
When I remove the LEFT JOIN and make it an INNER JOIN, I don't get an error
with it but it doesn't seem to be filtering from the combo box values. And
if I put the left join back in I get the criteria error again.
With LEFT JOIN Inspection in query, this works,but with the wrong results

WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[text] And
[combo2].[text]))

but this doesn't work at all

WHERE
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100

Am I not holding my tongue right here or what?
This is really starting to play mind games with me, especially if it is
working for you and not me.

They both work fine on my access 2000!

If the WHERE clause works from the first suggestion try that with the ORDER
BY of the second,

ORDER BY
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100

If that doesn't work try (long shot)
ORDER BY

Vehicle.license_plate_expiration_month-cint(Vehicle.[license_plate_expiratio
n_month]<Combo1.value)*100

Peter

Yes Peter,

The data types are numeric (01 through 12).
This is the value that the Vehicle.license_plate_expiration_month
field holds and the combo boxes hold the same values.

Prior to the last set of changes everything work except for the ordering
of the retrieved info from the query.

Rick

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you
require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based on a
field
with
a month in it and the parameter is based on the value from two combo
boxes
and the result is feed into another table which is built from the
query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date
INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON

Vehicle.pk_vehicle

=

Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between
[combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query where
the
license plate expiration month is between combo1.value and
combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I would
get
a
result back from the query where the license plate expiration months
are
either 10, 11 or 12 (October, November or December). No problem
here.
but
if
I select combo values from 11 to 01 (November to January), I get
everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Peter Surcouf]

Hi Rick

This is begining to wind me up the SQL bit was a no brainer as I'm doing
that sort of thing all the time, however I only use Access for prototyping
projects and generating basic SQL statements that I can modify in code. An
Access Guru I am definitely not.

I thought combo boxes always returned strings

Have you a small database that you could zip up and send to my address
(which I'll email you direct) then I'll sort it.

kind regards

Peter

Hey Peter,
My problem seems to be in the where clause and the LEFT JOIN in inspection
table.
When I remove the LEFT JOIN and make it an INNER JOIN, I don't get an error
with it but it doesn't seem to be filtering from the combo box values. And
if I put the left join back in I get the criteria error again.
With LEFT JOIN Inspection in query, this works,but with the wrong results

WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[text] And
[combo2].[text]))

but this doesn't work at all

WHERE
Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo
nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100

Am I not holding my tongue right here or what?
This is really starting to play mind games with me, especially if it is
working for you and not me.

They both work fine on my access 2000!

If the WHERE clause works from the first suggestion try that with the ORDER
BY of the second,

ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100

If that doesn't work try (long shot)
ORDER BY

Vehicle.license_plate_expiration_month-cint(Vehicle.[license_plate_expiratio

n_month]<Combo1.value)*100

Peter

Yes Peter,

The data types are numeric (01 through 12).
This is the value that the Vehicle.license_plate_expiration_month
field holds and the combo boxes hold the same values.

Prior to the last set of changes everything work except for the ordering
of the retrieved info from the query.

Rick

Rick

Not sure of your data types but for the criteria to give the correct
answer
then

Vehicle.license_plate_expiration_month
Combo1.value
Combo2.value

have to be numeric, if any are not try wrapping each instance in cint()

cint( Vehicle.license_plate_expiration_month)
cint(Combo1.value )
cint(Combo2.value )

in the WHERE and ORDER clauses only.

I am assuming the SQL worked before but just gave the "wrong" answer.

Peter

Thanks Peter,
When I put the SQL tegether and run it I get an error (Data type
mismatch
in
criteria expression). Here is what I ended up with when put together.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON
Customer.pk_customer=Vehicle.fk_customer) LEFT JOIN Inspection ON
Vehicle.pk_vehicle=Inspection.fk_vehicle
WHERE

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do what you
require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information based

on

a

field
with
a month in it and the parameter is based on the value from two
combo
boxes
and the result is feed into another table which is built

from
the

query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Inspection.inspection_date
INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON

Vehicle.pk_vehicle

=
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between
[combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query
where
the
license plate expiration month is between combo1.value and
combo2.value.
Lets say the values are combo1.value=10 and combo2.value=12. I
would
get
a
result back from the query where the license plate expiration
months
are
either 10, 11 or 12 (October, November or December). No problem
here.
but
if
I select combo values from 11 to 01 (November to January), I get
everything
from 01 to 11(January to November). That is the problem.

Can this be corrected somehow?

Rick

 

[Peter Surcouf]

Hi Rick

Thanks for the mdb.

Your problems were basically incorrect data types and incorrect data

Data Types
I have copied your Vehicles to "Copy of Vehicles" and made the field
[Vehicle].[license_plate_expiration_month] =>Numeric Integer from Text
I have changed the parameters [Combo1]/[Combo2].[Value] to [Combo1]
/[Combo2] and set them as Integer

Data
in [Vehicle].[license_plate_expiration_month] you had some month values of
null, zero string, and 0 so it is necessary to enhance the WHERE clause to
ignore them with "AND
((nz([Vehicle].[license_plate_expiration_month],0))>0)"

to finally give

WHERE
(((([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_e
xpiration_month])<[Combo1])*100) Between [Combo1] And
[Combo2]-([Combo2]<[Combo1])*100) AND
((nz([Vehicle].[license_plate_expiration_month],0))>0))
ORDER BY
([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_expi
ration_month])<[Combo1])*100;


kind regards

Peter

Hi Rick

This is begining to wind me up the SQL bit was a no brainer as I'm doing
that sort of thing all the time, however I only use Access for prototyping
projects and generating basic SQL statements that I can modify in code. An
Access Guru I am definitely not.

I thought combo boxes always returned strings

Have you a small database that you could zip up and send to my address
(which I'll email you direct) then I'll sort it.

kind regards

Peter

Hey Peter,
My problem seems to be in the where clause and the LEFT JOIN in inspection
table.
When I remove the LEFT JOIN and make it an INNER JOIN, I don't get an error
with it but it doesn't seem to be filtering from the combo box values. And
if I put the left join back in I get the criteria error again.
With LEFT JOIN Inspection in query, this works,but with the wrong results

WHERE (((Vehicle.license_plate_expiration_month) Between [combo1].[text] And
[combo2].[text]))

but this doesn't work at all

WHERE

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100

Am I not holding my tongue right here or what?
This is really starting to play mind games with me, especially if it is
working for you and not me.

They both work fine on my access 2000!

If the WHERE clause works from the first suggestion try that with the ORDER
BY of the second,

ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100

If that doesn't work try (long shot)
ORDER BY

Vehicle.license_plate_expiration_month-cint(Vehicle.[license_plate_expiratio

n_month]<Combo1.value)*100

Peter


Yes Peter,

The data types are numeric (01 through 12).
This is the value that the Vehicle.license_plate_expiration_month
field holds and the combo boxes hold the same values.

Prior to the last set of changes everything work except for the ordering
of the retrieved info from the query.

Rick

Rick

Not sure of your data types but for the criteria to give the correct
answer
then

Vehicle.license_plate_expiration_month
Combo1.value
Combo2.value

have to be numeric, if any are not try wrapping each instance in cint()

cint( Vehicle.license_plate_expiration_month)
cint(Combo1.value )
cint(Combo2.value )

in the WHERE and ORDER clauses only.

I am assuming the SQL worked before but just gave the "wrong" answer.

Peter

Thanks Peter,
When I put the SQL tegether and run it I get an error (Data type
mismatch
in
criteria expression). Here is what I ended up with when put together.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Inspection.inspection_date
INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON
Customer.pk_customer=Vehicle.fk_customer) LEFT JOIN Inspection ON
Vehicle.pk_vehicle=Inspection.fk_vehicle
WHERE

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100 Between Combo1.value And
Combo2.value-(Combo2.value<Combo1.value)*100
ORDER BY

Vehicle.license_plate_expiration_month-(Vehicle.[license_plate_expiration_mo

nth]<Combo1.value)*100;

Does this look right?

Rick

A little more thought gives the following

WHERE

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100 Between [Combo1] .[value] And
[Combo2].[value]-([Combo2].[value]<[Combo1].[value])*100

ORDER BY

[Vehicle].[license_plate_expiration_month]-([Vehicle].[license_plate_expirat

ion_month]<[Combo1].[value])*100

Peter

Rick

It's not elegant and it does not ORDER well but might do

what
you

require

WHERE

IIf([Combo2].[value]>=[Combo1].[value],[Vehicle.license_plate_expiration_mon

th] Between [Combo1].[value] And

[Combo2].[value],[Vehicle.license_plate_expiration_month]<=[Combo2].[value]

Or [Vehicle.license_plate_expiration_month]>=[Combo1].[value])

Peter

Hello all,

I have built a make table query to select information

based

on

a
field
with
a month in it and the parameter is based on the value from two
combo
boxes
and the result is feed into another table which is built from
the
query.

SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state,
Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make,
Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month,
Inspection.inspection_date
INTO
INSPReminder
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle
=
Inspection.fk_vehicle
WHERE (((Vehicle.license_plate_expiration_month) Between
[combo1].[value]
And [combo2].[value]))
ORDER BY Vehicle.license_plate_expiration_month;

What is happening here, is I am selecting info from the query
where
the
license plate expiration month is between combo1.value and
combo2.value.
Lets say the values are combo1.value=10 and

combo2.value=12.

I I
get