Parameters for query from form

J

Jen

I have a query that currently runs using built in date parameters. I want to
change this to run from a form, but every time I try to run the query this
way it comes back with incorrect results (28 entries instead of about 3000 -
it only uses the last txt box instead of acting as a 'between' statement). I
have done this on many of the queries & reports that I have already built, so
I understand how it should work. I've checked the data type of the field in
the table, query & form. I've used the parameters area within the query to
define the txt boxes as parameters. Nothing seems to fix the problem. The
main table is not a sql table - I don't know what language it is based from
but it uses a Simba Client ODBC driver to bring in the info... does anyone
know of a way to set this so that they dates don't need to be hard-coded each
time? Here is the SQL behind the query.

PARAMETERS [forms]![frmDatesforTotalView]![txtStart] DateTime,
[forms]![frmdatesforTotalView]![txtEnd] DateTime;

SELECT schedheader.MODIFY_DATE_TIME, schedheader.AGENT_ID, schedheader.DATE,
schedheader.SCHED_ID, schedheader.EXCEPTION_CODE,
tblExeptionCodes.EXC_PRINT_NAME, schedheader.START_MINUTE,
schedheader.SCHEDULE_LENGTH INTO tblThisWeeksScheduleHeader
IN 'N:\ADMIN\Jennifer SM\Total View\TotalViewTest.mdb'

FROM tblExeptionCodes INNER JOIN schedheader ON tblExeptionCodes.EXC_ID =
schedheader.EXCEPTION_CODE

WHERE (((schedheader.DATE) Between [forms]![frmDatesforTotalView]![txtStart]
And [forms]![frmDatesforTotalView]![txtEnd]) AND
((schedheader.EXCEPTION_CODE)<>32))

ORDER BY schedheader.AGENT_ID, schedheader.DATE;


Thank you!
 
K

KARL DEWEY

Here are a couple of things to try --
--Change field name of schedheader.DATE as Date is a reserved word in Access
and can cause problems.
--Try changing WHERE (((schedheader.DATE) Between
[forms]![frmDatesforTotalView]![txtStart] And
[forms]![frmDatesforTotalView]![txtEnd]) AND ....
to WHERE (((schedheader.DATE) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....
Remember the change for schedheader.DATE.
 
J

Jen

Thank you Karl, unfortunately it didn't work. I can't change the actual field
name on Schedheader.Date, but I did try a select subquery where I gave it an
alias. Still the same results (27 or 28 records). Then when I adjusted the
code to add CVDate to the form dates, I got 0 records.

Help?

Thank you again!



KARL DEWEY said:
Here are a couple of things to try --
--Change field name of schedheader.DATE as Date is a reserved word in Access
and can cause problems.
--Try changing WHERE (((schedheader.DATE) Between
[forms]![frmDatesforTotalView]![txtStart] And
[forms]![frmDatesforTotalView]![txtEnd]) AND ....
to WHERE (((schedheader.DATE) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....
Remember the change for schedheader.DATE.

--
KARL DEWEY
Build a little - Test a little


Jen said:
I have a query that currently runs using built in date parameters. I want to
change this to run from a form, but every time I try to run the query this
way it comes back with incorrect results (28 entries instead of about 3000 -
it only uses the last txt box instead of acting as a 'between' statement). I
have done this on many of the queries & reports that I have already built, so
I understand how it should work. I've checked the data type of the field in
the table, query & form. I've used the parameters area within the query to
define the txt boxes as parameters. Nothing seems to fix the problem. The
main table is not a sql table - I don't know what language it is based from
but it uses a Simba Client ODBC driver to bring in the info... does anyone
know of a way to set this so that they dates don't need to be hard-coded each
time? Here is the SQL behind the query.

PARAMETERS [forms]![frmDatesforTotalView]![txtStart] DateTime,
[forms]![frmdatesforTotalView]![txtEnd] DateTime;

SELECT schedheader.MODIFY_DATE_TIME, schedheader.AGENT_ID, schedheader.DATE,
schedheader.SCHED_ID, schedheader.EXCEPTION_CODE,
tblExeptionCodes.EXC_PRINT_NAME, schedheader.START_MINUTE,
schedheader.SCHEDULE_LENGTH INTO tblThisWeeksScheduleHeader
IN 'N:\ADMIN\Jennifer SM\Total View\TotalViewTest.mdb'

FROM tblExeptionCodes INNER JOIN schedheader ON tblExeptionCodes.EXC_ID =
schedheader.EXCEPTION_CODE

WHERE (((schedheader.DATE) Between [forms]![frmDatesforTotalView]![txtStart]
And [forms]![frmDatesforTotalView]![txtEnd]) AND
((schedheader.EXCEPTION_CODE)<>32))

ORDER BY schedheader.AGENT_ID, schedheader.DATE;


Thank you!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Then when I adjusted the code to add CVDate to the form dates, I got 0
records.
It sounds as if your schedheader.DATE field is not a DateTime datatype but
is a text field.
If you can not change the field then use this ---
WHERE (((CVDate(schedheader.DATE)) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....

--
KARL DEWEY
Build a little - Test a little


Jen said:
Thank you Karl, unfortunately it didn't work. I can't change the actual field
name on Schedheader.Date, but I did try a select subquery where I gave it an
alias. Still the same results (27 or 28 records). Then when I adjusted the
code to add CVDate to the form dates, I got 0 records.

Help?

Thank you again!



KARL DEWEY said:
Here are a couple of things to try --
--Change field name of schedheader.DATE as Date is a reserved word in Access
and can cause problems.
--Try changing WHERE (((schedheader.DATE) Between
[forms]![frmDatesforTotalView]![txtStart] And
[forms]![frmDatesforTotalView]![txtEnd]) AND ....
to WHERE (((schedheader.DATE) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....
Remember the change for schedheader.DATE.

--
KARL DEWEY
Build a little - Test a little


Jen said:
I have a query that currently runs using built in date parameters. I want to
change this to run from a form, but every time I try to run the query this
way it comes back with incorrect results (28 entries instead of about 3000 -
it only uses the last txt box instead of acting as a 'between' statement). I
have done this on many of the queries & reports that I have already built, so
I understand how it should work. I've checked the data type of the field in
the table, query & form. I've used the parameters area within the query to
define the txt boxes as parameters. Nothing seems to fix the problem. The
main table is not a sql table - I don't know what language it is based from
but it uses a Simba Client ODBC driver to bring in the info... does anyone
know of a way to set this so that they dates don't need to be hard-coded each
time? Here is the SQL behind the query.

PARAMETERS [forms]![frmDatesforTotalView]![txtStart] DateTime,
[forms]![frmdatesforTotalView]![txtEnd] DateTime;

SELECT schedheader.MODIFY_DATE_TIME, schedheader.AGENT_ID, schedheader.DATE,
schedheader.SCHED_ID, schedheader.EXCEPTION_CODE,
tblExeptionCodes.EXC_PRINT_NAME, schedheader.START_MINUTE,
schedheader.SCHEDULE_LENGTH INTO tblThisWeeksScheduleHeader
IN 'N:\ADMIN\Jennifer SM\Total View\TotalViewTest.mdb'

FROM tblExeptionCodes INNER JOIN schedheader ON tblExeptionCodes.EXC_ID =
schedheader.EXCEPTION_CODE

WHERE (((schedheader.DATE) Between [forms]![frmDatesforTotalView]![txtStart]
And [forms]![frmDatesforTotalView]![txtEnd]) AND
((schedheader.EXCEPTION_CODE)<>32))

ORDER BY schedheader.AGENT_ID, schedheader.DATE;


Thank you!
Click to expand...
Click to expand...
Click to expand...
 
J

Jen

Thank you! Thank you! Thank you!

I honestly did check & the table states that it is a Date/Time field, but it
wasn't working like one.

Have a great day!

KARL DEWEY said:
records.
It sounds as if your schedheader.DATE field is not a DateTime datatype but
is a text field.
If you can not change the field then use this ---
WHERE (((CVDate(schedheader.DATE)) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....

--
KARL DEWEY
Build a little - Test a little


Jen said:
Thank you Karl, unfortunately it didn't work. I can't change the actual field
name on Schedheader.Date, but I did try a select subquery where I gave it an
alias. Still the same results (27 or 28 records). Then when I adjusted the
code to add CVDate to the form dates, I got 0 records.

Help?

Thank you again!



KARL DEWEY said:
Here are a couple of things to try --
--Change field name of schedheader.DATE as Date is a reserved word in Access
and can cause problems.
--Try changing WHERE (((schedheader.DATE) Between
[forms]![frmDatesforTotalView]![txtStart] And
[forms]![frmDatesforTotalView]![txtEnd]) AND ....
to WHERE (((schedheader.DATE) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....
Remember the change for schedheader.DATE.

--
KARL DEWEY
Build a little - Test a little


:

I have a query that currently runs using built in date parameters. I want to
change this to run from a form, but every time I try to run the query this
way it comes back with incorrect results (28 entries instead of about 3000 -
it only uses the last txt box instead of acting as a 'between' statement). I
have done this on many of the queries & reports that I have already built, so
I understand how it should work. I've checked the data type of the field in
the table, query & form. I've used the parameters area within the query to
define the txt boxes as parameters. Nothing seems to fix the problem. The
main table is not a sql table - I don't know what language it is based from
but it uses a Simba Client ODBC driver to bring in the info... does anyone
know of a way to set this so that they dates don't need to be hard-coded each
time? Here is the SQL behind the query.

PARAMETERS [forms]![frmDatesforTotalView]![txtStart] DateTime,
[forms]![frmdatesforTotalView]![txtEnd] DateTime;

SELECT schedheader.MODIFY_DATE_TIME, schedheader.AGENT_ID, schedheader.DATE,
schedheader.SCHED_ID, schedheader.EXCEPTION_CODE,
tblExeptionCodes.EXC_PRINT_NAME, schedheader.START_MINUTE,
schedheader.SCHEDULE_LENGTH INTO tblThisWeeksScheduleHeader
IN 'N:\ADMIN\Jennifer SM\Total View\TotalViewTest.mdb'

FROM tblExeptionCodes INNER JOIN schedheader ON tblExeptionCodes.EXC_ID =
schedheader.EXCEPTION_CODE

WHERE (((schedheader.DATE) Between [forms]![frmDatesforTotalView]![txtStart]
And [forms]![frmDatesforTotalView]![txtEnd]) AND
((schedheader.EXCEPTION_CODE)<>32))

ORDER BY schedheader.AGENT_ID, schedheader.DATE;


Thank you!
Click to expand...
Click to expand...
Click to expand...
 
T

Travis

Karl,

I am having the same issue. I've tried your suggestion below, however to no
success on getting my query to use the date parameters from the form. The
criteria I am using follows:

WHERE ((CVDate([Tbl_NewAssign]![Order Date]) Between
CVDate([Forms]![Frm_Update_Data]![Lbl_StartDate]) And
CVDate([Forms]![Frm_Update_Data]![Lbl_EndDate])));

Any ideas?

Thank you,
Travis

KARL DEWEY said:
records.
It sounds as if your schedheader.DATE field is not a DateTime datatype but
is a text field.
If you can not change the field then use this ---
WHERE (((CVDate(schedheader.DATE)) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....

--
KARL DEWEY
Build a little - Test a little


Jen said:
Thank you Karl, unfortunately it didn't work. I can't change the actual field
name on Schedheader.Date, but I did try a select subquery where I gave it an
alias. Still the same results (27 or 28 records). Then when I adjusted the
code to add CVDate to the form dates, I got 0 records.

Help?

Thank you again!



KARL DEWEY said:
Here are a couple of things to try --
--Change field name of schedheader.DATE as Date is a reserved word in Access
and can cause problems.
--Try changing WHERE (((schedheader.DATE) Between
[forms]![frmDatesforTotalView]![txtStart] And
[forms]![frmDatesforTotalView]![txtEnd]) AND ....
to WHERE (((schedheader.DATE) Between
CVDate([forms]![frmDatesforTotalView]![txtStart]) And
CVDate([forms]![frmDatesforTotalView]![txtEnd])) AND ....
Remember the change for schedheader.DATE.

--
KARL DEWEY
Build a little - Test a little


:

I have a query that currently runs using built in date parameters. I want to
change this to run from a form, but every time I try to run the query this
way it comes back with incorrect results (28 entries instead of about 3000 -
it only uses the last txt box instead of acting as a 'between' statement). I
have done this on many of the queries & reports that I have already built, so
I understand how it should work. I've checked the data type of the field in
the table, query & form. I've used the parameters area within the query to
define the txt boxes as parameters. Nothing seems to fix the problem. The
main table is not a sql table - I don't know what language it is based from
but it uses a Simba Client ODBC driver to bring in the info... does anyone
know of a way to set this so that they dates don't need to be hard-coded each
time? Here is the SQL behind the query.

PARAMETERS [forms]![frmDatesforTotalView]![txtStart] DateTime,
[forms]![frmdatesforTotalView]![txtEnd] DateTime;

SELECT schedheader.MODIFY_DATE_TIME, schedheader.AGENT_ID, schedheader.DATE,
schedheader.SCHED_ID, schedheader.EXCEPTION_CODE,
tblExeptionCodes.EXC_PRINT_NAME, schedheader.START_MINUTE,
schedheader.SCHEDULE_LENGTH INTO tblThisWeeksScheduleHeader
IN 'N:\ADMIN\Jennifer SM\Total View\TotalViewTest.mdb'

FROM tblExeptionCodes INNER JOIN schedheader ON tblExeptionCodes.EXC_ID =
schedheader.EXCEPTION_CODE

WHERE (((schedheader.DATE) Between [forms]![frmDatesforTotalView]![txtStart]
And [forms]![frmDatesforTotalView]![txtEnd]) AND
((schedheader.EXCEPTION_CODE)<>32))

ORDER BY schedheader.AGENT_ID, schedheader.DATE;


Thank you!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running a Report From A Crosstab Query 1
Date query parameters entered from form not working 1
Using an Access form to enter parameters for a Query 1
Form Entered Parameters for Pass-thru query 0
How do I set up query to look for parameters in a form? 1
Pass Parameters to Query from Form Control 5
Update Query using parameters passed from a form 3
Using field in a form to set parameters for a query 3

Top