validation in pass through query

[Guest]

Hi guyz! Can anyone help me how to pass validation in a pass-through query in
MS Access. I want the between date to com from my ACCESS forms. how can i
change "10/9/2004" from below to [Forms]![frmdate]![txtDate1] and
[Forms]![frmdate]![txtDate2] so that the value it will get is from the form.
THANKS!

SELECT /*+ ORDERED */
d56.tdate
, d50.customer_part_desc AS jobname
, d50.lot_number_count AS lotnum
, d08.layers
, d08.TYPE
, round(((d56.qty_produced + d56.qty_rejected) / d06.parts_per_panel)) AS
numpanels
, d34.dept_code
, d34.dept_name
, d34.unit_ptr
, d34.dept_ptr
, d34.rpt_ptr
FROM CSI_LIVE.data0056 d56
, CSI_LIVE.data0034 d34
, CSI_LIVE.data0006 d06
, CSI_LIVE.data0050 d50
, CSI_LIVE.data0008 d08
WHERE d50.prod_code_ptr = d08.rkey
AND d06.cust_part_ptr = d50.rkey
AND d06.rkey = d56.wo_ptr
AND d56.d_g_w_ptr = d34.rkey
and d34.dept_ptr = 67
and d34.unit_ptr = 4
AND d56.tdate
BETWEEN TO_DATE('10/9/2004', 'mm/dd/yyyy')
AND TO_DATE('10/9/2004', 'mm/dd/yyyy')

 

[Jeff Boyce]

If I recall correctly, you have to convert references to Access objects
(e.g., [Forms]!...) into their evaluated values and string equivalents, then
pass a string. The reason SQL can't evaluate the contents of [Forms]!... is
because it can't "see" that.

 

[Guest]

how will i do that? can you show me how? thanks!!!

 

[Duane Hookom]

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" & [Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] & "',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

If I recall correctly, you have to convert references to Access objects
(e.g., [Forms]!...) into their evaluated values and string equivalents,
then
pass a string. The reason SQL can't evaluate the contents of [Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

how about if am not doing it by code instead type it directly as a pass
through query?

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" & [Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] & "',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

If I recall correctly, you have to convert references to Access objects
(e.g., [Forms]!...) into their evaluated values and string equivalents,
then
pass a string. The reason SQL can't evaluate the contents of [Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Duane Hookom]

Create a table in your back-end database name tblDateRange with two fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them). You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a pass
through query?

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

is there any other approach? i don't see it as the best solution...

Create a table in your back-end database name tblDateRange with two fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them). You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a pass
through query?

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Duane Hookom]

Yes, the better solution is to use code to modify the SQL property of the
saved query.

--
Duane Hookom
MS Access MVP

is there any other approach? i don't see it as the best solution...

Create a table in your back-end database name tblDateRange with two fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them). You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a pass
through query?

:

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

i want to do it in query not in vba mode can you help me how can i do that?
thanks in advance!

Yes, the better solution is to use code to modify the SQL property of the
saved query.

--
Duane Hookom
MS Access MVP

is there any other approach? i don't see it as the best solution...

Create a table in your back-end database name tblDateRange with two fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them). You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a pass
through query?

:

Assuming your P-T is named "qsptCustJobs", you can use DAO code to modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Duane Hookom]

I suggested before a method based your text boxes bound to a table in the
back-end. It involves absolutely no code.

--
Duane Hookom
MS Access MVP

i want to do it in query not in vba mode can you help me how can i do that?
thanks in advance!

Yes, the better solution is to use code to modify the SQL property of the
saved query.

--
Duane Hookom
MS Access MVP

is there any other approach? i don't see it as the best solution...

:

Create a table in your back-end database name tblDateRange with two fields
"Date1" and "Date2". Use your frmDate to set these dates (bind

them).
You

can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a pass
through query?

:

Assuming your P-T is named "qsptCustJobs", you can use DAO code

to
modify

the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" & [Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

i am not getting what your trying to point out... all i want is to have my
pass through query where clause to get from my form... i can't seem to figure
out what your trying to suggest.

I suggested before a method based your text boxes bound to a table in the
back-end. It involves absolutely no code.

--
Duane Hookom
MS Access MVP

i want to do it in query not in vba mode can you help me how can i do that?
thanks in advance!

Yes, the better solution is to use code to modify the SQL property of the
saved query.

--
Duane Hookom
MS Access MVP


is there any other approach? i don't see it as the best solution...

:

Create a table in your back-end database name tblDateRange with two
fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them).
You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly as a
pass
through query?

:

Assuming your P-T is named "qsptCustJobs", you can use DAO code to
modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" &
[Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>

 

[Duane Hookom]

Assuming you have two text boxes on your form: txtStartDate and txtEndDate.
Create a table in your back-end database "tblDates" with two date fields
"StartDate" and "EndDate". Set the Record Source property of the form to
tblDates and set the control sources of txtStartDate and txtEndDate to the
fields StartDate and EndDate. Only allow one record in tblDates.

Now the user enters dates into the text boxes which in turn stores the two
date values in the table on your server. Your pass-through can now include
your tblDates with the criteria including something like:
AND d56.tdate
BETWEEN tblDates.StartDate AND tblDates.EndDate


--
Duane Hookom
MS Access MVP
--

i am not getting what your trying to point out... all i want is to have my
pass through query where clause to get from my form... i can't seem to
figure
out what your trying to suggest.

I suggested before a method based your text boxes bound to a table in the
back-end. It involves absolutely no code.

--
Duane Hookom
MS Access MVP

i want to do it in query not in vba mode can you help me how can i do that?
thanks in advance!

:

Yes, the better solution is to use code to modify the SQL property of the
saved query.

--
Duane Hookom
MS Access MVP


is there any other approach? i don't see it as the best solution...

:

Create a table in your back-end database name tblDateRange with
two
fields
"Date1" and "Date2". Use your frmDate to set these dates (bind them).
You
can then substitute the stored values in tblDateRange for your criteria.

--
Duane Hookom
MS Access MVP
--

how about if am not doing it by code instead type it directly
as a
pass
through query?

:

Assuming your P-T is named "qsptCustJobs", you can use DAO
code to
modify
the SQL. You may need to modify the code and do some testing.
Dim strSQL as String
'build the stuff up to the where clause
strSQL = "SELECT /*+ ORDERED */...."
'add the where clause
strSQL = strSQL & " BETWEEN TO_DATE('" &
[Forms]![frmdate]![txtDate1] &
"', 'mm/dd/yyyy') "
strSQL = strSQL & " AND TO_DATE('" &
[Forms]![frmdate]![txtDate2] &
"',
'mm/dd/yyyy') "
CurrentDb.QueryDefs("qsptCustJobs").SQL = strSQL


--
Duane Hookom
MS Access MVP
--

how will i do that? can you show me how? thanks!!!

:

If I recall correctly, you have to convert references to Access
objects
(e.g., [Forms]!...) into their evaluated values and string
equivalents,
then
pass a string. The reason SQL can't evaluate the contents
of
[Forms]!...
is
because it can't "see" that.

--
Good luck

Jeff Boyce
<Access MVP>