Between...And; To Two Different Fields

[ryguy7272]

I posted this question in the Forms DG a few days ago. I though it was a
Forms question, because two values, two dates catually, were passed from a
Form to a Query. As I thought about it more and more, I began to realize
that this is a Query question, and I believe I need help with just one small
part of the SQL, which I can not, for the life of me, figure out! I’ve
looked at many posts on this DG, and all sorts of examples on the web, but I
can’t seem to find a solution anywhere.

Below is my SQL:
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start) >= [forms]![Calendar].[IO_Start]) AND
((RM.IO_End)<=[forms]![Calendar].[IO_End]));

Basically, I have a Form set up, and it is linked to a Calendar. The
Calendar dates go into two TextBoxes just fine, but I can't seem to pass the
values from the TextBoxes to the QBE grid. It seems like, if the dates were
just listed in one Field, I could easily use the Between...And to handle the
issue, but there are actually two Fields, and they are named IO_Start and
IO_End. This seems to be presenting a problem. How can I pass the two dates
from my Form to my IO_Start Field and my IO_End Field? It must be something
in the Where part of the SQL, but I can't figure it out. Please assist.

Thanks,
Ryan---

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It always works better when you define the parameters in the PARAMETERS
clause:

PARAMETERS Forms!Calendar!IO_End Date, Forms!Calendar!IO_Start Date;
SELECT ... etc.

Use the ! for the Control names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTuskIechKqOuFEgEQIxdgCfUPjM7jbFcTfKyyq6NFbKnEtUWt0AoMcU
y2/RcSqsHmyRXZ145USozC75
=nSg0
-----END PGP SIGNATURE-----

 

[ryguy7272]

Thanks, but still nothing. For one thing, I think I need a dot after the
'Calendar' because that is the name of the Form. I am now fiddling around
with this:
PARAMETERS Forms!Calendar.IO_Start Date, Forms!Calendar.IO_End Date
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start)=[forms]![Calendar].[IO_Start]) AND
((RM.IO_End)=[forms]![Calendar].[IO_End]));

However, as mentioned above, the Query is still not working. A Window pops
up and says 'Syntax error in the Parameter clause'. There must be a better
way to do this. I've worked with queries that are much more complex than
this. However, I am not very experienced with passing parameters from a
Form, especially to two different fields. I'd greatly appreciate any
additional suggestions.

Regards,
Ryan---
--
RyGuy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It always works better when you define the parameters in the PARAMETERS
clause:

PARAMETERS Forms!Calendar!IO_End Date, Forms!Calendar!IO_Start Date;
SELECT ... etc.

Use the ! for the Control names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTuskIechKqOuFEgEQIxdgCfUPjM7jbFcTfKyyq6NFbKnEtUWt0AoMcU
y2/RcSqsHmyRXZ145USozC75
=nSg0
-----END PGP SIGNATURE-----

I posted this question in the Forms DG a few days ago. I though it was a
Forms question, because two values, two dates catually, were passed from a
Form to a Query. As I thought about it more and more, I began to realize
that this is a Query question, and I believe I need help with just one small
part of the SQL, which I can not, for the life of me, figure out! I’ve
looked at many posts on this DG, and all sorts of examples on the web, but I
can’t seem to find a solution anywhere.

Below is my SQL:
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start) >= [forms]![Calendar].[IO_Start]) AND
((RM.IO_End)<=[forms]![Calendar].[IO_End]));

Basically, I have a Form set up, and it is linked to a Calendar. The
Calendar dates go into two TextBoxes just fine, but I can't seem to pass the
values from the TextBoxes to the QBE grid. It seems like, if the dates were
just listed in one Field, I could easily use the Between...And to handle the
issue, but there are actually two Fields, and they are named IO_Start and
IO_End. This seems to be presenting a problem. How can I pass the two dates
from my Form to my IO_Start Field and my IO_End Field? It must be something
in the Where part of the SQL, but I can't figure it out. Please assist.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First, you need a semi-colon ( at the end of the PARAMETERS clause
(before the SELECT). This is causing the syntax error.

Second, the WHERE clause is different from your original post:

(((RM.IO_Start) >= [forms]![Calendar].[IO_Start]) AND
((RM.IO_End)<=[forms]![Calendar].[IO_End]));

which indicates a possible range of dates to test.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTv4qoechKqOuFEgEQJ3tACgxJXdr5fDkSLNcFoEBszChY9RfAUAn0Vh
2uDGmQNVLYfRQfvIrcLkI8a4
=5xp5
-----END PGP SIGNATURE-----

 

[ryguy7272]

Interesting; I thought the semi colon went at the very end of the SQL
statement. Ok, I put it at the end of the Parameter clause, and now I get
another message that says my Query is too complex to be evaluated. I've
received that message a dozen times, at least. Maybe it has to convert the
date, using CDate(), or some such thing. However, I'm fairly certain that
the Query is not 'too complex'...

Any other ideas?

Thanks,
Ryan---

--
RyGuy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First, you need a semi-colon ( at the end of the PARAMETERS clause
(before the SELECT). This is causing the syntax error.

Second, the WHERE clause is different from your original post:

(((RM.IO_Start) >= [forms]![Calendar].[IO_Start]) AND
((RM.IO_End)<=[forms]![Calendar].[IO_End]));

which indicates a possible range of dates to test.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTv4qoechKqOuFEgEQJ3tACgxJXdr5fDkSLNcFoEBszChY9RfAUAn0Vh
2uDGmQNVLYfRQfvIrcLkI8a4
=5xp5
-----END PGP SIGNATURE-----

Thanks, but still nothing. For one thing, I think I need a dot after the
'Calendar' because that is the name of the Form. I am now fiddling around
with this:
PARAMETERS Forms!Calendar.IO_Start Date, Forms!Calendar.IO_End Date
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start)=[forms]![Calendar].[IO_Start]) AND
((RM.IO_End)=[forms]![Calendar].[IO_End]));

However, as mentioned above, the Query is still not working. A Window pops
up and says 'Syntax error in the Parameter clause'. There must be a better
way to do this. I've worked with queries that are much more complex than
this. However, I am not very experienced with passing parameters from a
Form, especially to two different fields. I'd greatly appreciate any
additional suggestions.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is RM a table or a query? If a query that may be causing the "too
complex" error. It's usually calculations or multiple JOINs that cause
this error.

Try using the exclamation point as the delimiter before the Control
names IO_Start and IO_End. Be sure there ARE controls with those names
and that the Input Mask is a date input mask.

Using a period as the delimiter after the Form name means read the value
of the Field with the indicated name. The Field is from the form's
RecordSource. Using the exclamation point means read the value of the
Control with the indicated name.

Forms!FormName.FieldName <- the Field is a property of the form

Forms!FormName!ControlName <- an object of the form's Control's
collection

Something else I noticed that I did not notice before - if RM is a table
it isn't in Normal Form. If it was in Normal Form the query would be
simpler. RM has columns for each month of the year and for each
quarter. That data could be in one, or two, columns.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTwvzYechKqOuFEgEQKYZACeJ/sI6EJaAA8r+nmxGU/DLrMAM2gAoKef
AXHuoQJrU0OCHKal55HMgTXk
=snHh
-----END PGP SIGNATURE-----

 

[ryguy7272]

I finally figured it out!! For instance, if I enter a date of 12/1/2008 into
the IO_Start and a date of 12/19/2008 into the IO_End, I wanted to see dates
that are greater than 12/1/2008 in the IO_Start Field and less than
12/19/2008 in the IO_Start Field. Similarly, if I enter a date of 12/1/2008
into the IO_Start and a date of 12/19/2008 into the IO_End, I wanted to see
dates that are greater than 12/1/2008 in the IO_End Field and less than
12/19/2008 in the IO_End Field.

Below is my SQL:
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start)>=[forms]![Calendar]![IO_Start] And
(RM.IO_Start)<=[forms]![Calendar]![IO_End]) AND
((RM.IO_End)>=[forms]![Calendar]![IO_Start] And
(RM.IO_End)<=[forms]![Calendar]![IO_End]));

Thanks to all who reviewed this!!


Regards,
Ryan---

 

[BruceM]

As MGFoster pointed out in the most recent reply, if RM is a table it should
not have a column for each month, for quarters, and for a total. With a
single date field you can break out the data by year, quarter, month, week,
or day. As it is you need to search each field month or quarter field
separately. Your database will become unwieldy very soon if it is not
already.

I finally figured it out!! For instance, if I enter a date of 12/1/2008
into
the IO_Start and a date of 12/19/2008 into the IO_End, I wanted to see
dates
that are greater than 12/1/2008 in the IO_Start Field and less than
12/19/2008 in the IO_Start Field. Similarly, if I enter a date of
12/1/2008
into the IO_Start and a date of 12/19/2008 into the IO_End, I wanted to
see
dates that are greater than 12/1/2008 in the IO_End Field and less than
12/19/2008 in the IO_End Field.

Below is my SQL:
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End,
RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN,
RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start)>=[forms]![Calendar]![IO_Start] And
(RM.IO_Start)<=[forms]![Calendar]![IO_End]) AND
((RM.IO_End)>=[forms]![Calendar]![IO_Start] And
(RM.IO_End)<=[forms]![Calendar]![IO_End]));

Thanks to all who reviewed this!!


Regards,
Ryan---



--
RyGuy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is RM a table or a query? If a query that may be causing the "too
complex" error. It's usually calculations or multiple JOINs that cause
this error.

Try using the exclamation point as the delimiter before the Control
names IO_Start and IO_End. Be sure there ARE controls with those names
and that the Input Mask is a date input mask.

Using a period as the delimiter after the Form name means read the value
of the Field with the indicated name. The Field is from the form's
RecordSource. Using the exclamation point means read the value of the
Control with the indicated name.

Forms!FormName.FieldName <- the Field is a property of the form

Forms!FormName!ControlName <- an object of the form's Control's
collection

Something else I noticed that I did not notice before - if RM is a table
it isn't in Normal Form. If it was in Normal Form the query would be
simpler. RM has columns for each month of the year and for each
quarter. That data could be in one, or two, columns.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTwvzYechKqOuFEgEQKYZACeJ/sI6EJaAA8r+nmxGU/DLrMAM2gAoKef
AXHuoQJrU0OCHKal55HMgTXk
=snHh
-----END PGP SIGNATURE-----