Where is the error?

[Ling]

I have converted an ordinary query to a pass through
query. here's the syntax:
SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between [Forms]![frmDeliveryOrder
(pop)]![txtstartYr] And [Forms]![frmDeliveryOrder (pop)]!
[txtendYr]))
ORDER BY tblDo.DoDate DESC;

However, this query could not work. There's an error msg
saying syntax near '!'.
I tink the error is here: Between [Forms]!
[frmDeliveryOrder (pop)]![txtstartYr] And [Forms]!
[frmDeliveryOrder (pop)]![txtendYr]))

But I need to narrow down the records returned according
to this criteria.

Any ideas?

Thanx

Ling

 

[Ken Snell]

I believe that you must evaluate the parameters before passing them to SQL
server. Try wrapping the parameters with Eval function.

SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between Eval([Forms]![frmDeliveryOrder
(pop)]![txtstartYr])
And Eval([Forms]![frmDeliveryOrder (pop)]![txtendYr])))
ORDER BY tblDo.DoDate DESC;

 

[Ling]

Thanx for ur sugg.
I tried it but there 's still the error of Incorrect
syntax near '!'

What other functions do u think i could try?

Thanx

Ling

-----Original Message-----
I believe that you must evaluate the parameters before passing them to SQL
server. Try wrapping the parameters with Eval function.

SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between Eval([Forms]! [frmDeliveryOrder
(pop)]![txtstartYr])
And Eval([Forms]![frmDeliveryOrder (pop)]![txtendYr])))
ORDER BY tblDo.DoDate DESC;

--
Ken Snell
<MS ACCESS MVP>

I have converted an ordinary query to a pass through
query. here's the syntax:
SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between [Forms]![frmDeliveryOrder
(pop)]![txtstartYr] And [Forms]![frmDeliveryOrder (pop)]!
[txtendYr]))
ORDER BY tblDo.DoDate DESC;

However, this query could not work. There's an error msg
saying syntax near '!'.
I tink the error is here: Between [Forms]!
[frmDeliveryOrder (pop)]![txtstartYr] And [Forms]!
[frmDeliveryOrder (pop)]![txtendYr]))

But I need to narrow down the records returned according
to this criteria.

Any ideas?

Thanx

Ling

.

 

[Ken Snell]

I must admit that I've not worked with pass through queries, so I've done a
bit of research on them....and haven't learned a whole lot yet!

You may need to do this in VBA code so that you can evaluate the parameters
before you pass the query through to the server. This is done by creating a
QueryDef query based on the stored pass through query that you've already
prepared, evaluating the parameters, and then opening a recordset based on
the querydef.

Dim qdf As QueryDef
Dim prm As Parameter
Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("NameOfStoredPassThroughQuery")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

(etc.)

--
Ken Snell
<MS ACCESS MVP>

Thanx for ur sugg.
I tried it but there 's still the error of Incorrect
syntax near '!'

What other functions do u think i could try?

Thanx

Ling

-----Original Message-----
I believe that you must evaluate the parameters before passing them to SQL
server. Try wrapping the parameters with Eval function.

SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between Eval([Forms]! [frmDeliveryOrder
(pop)]![txtstartYr])
And Eval([Forms]![frmDeliveryOrder (pop)]![txtendYr])))
ORDER BY tblDo.DoDate DESC;

--
Ken Snell
<MS ACCESS MVP>

I have converted an ordinary query to a pass through
query. here's the syntax:
SELECT tblDo.DoNumber AS [DO No], tblDo.DoYear AS [Year],
tblDo.DoDate AS [DO Date]
FROM tblDo
WHERE (((tblDo.DoDate) Between [Forms]![frmDeliveryOrder
(pop)]![txtstartYr] And [Forms]![frmDeliveryOrder (pop)]!
[txtendYr]))
ORDER BY tblDo.DoDate DESC;

However, this query could not work. There's an error msg
saying syntax near '!'.
I tink the error is here: Between [Forms]!
[frmDeliveryOrder (pop)]![txtstartYr] And [Forms]!
[frmDeliveryOrder (pop)]![txtendYr]))

But I need to narrow down the records returned according
to this criteria.

Any ideas?

Thanx

Ling

.