Error: Too few paramenters

G

Guest

Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr] FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #], [DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact
Store?"));
***********

Thank you,

Dan
 
G

Guest

Hi.
What is wrong with this code?
Click to expand...

At least one of the field names in the query is misspelled or is no longer a
field name in the table where it once was.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


D said:
Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr] FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #], [DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact
Store?"));
***********

Thank you,

Dan
Click to expand...
 
G

Guest

Thanks!

But the query runs perfectly fine?!

Dan

'69 Camaro said:
Hi.
What is wrong with this code?
Click to expand...

At least one of the field names in the query is misspelled or is no longer a
field name in the table where it once was.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


D said:
Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr] FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #], [DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact
Store?"));
***********

Thank you,

Dan
Click to expand...
Click to expand...
 
P

Paul Overway

I think you need a MoveFirst before starting your loop through the
recordset. You are being prompted for a parameter because there is no
current record when you set the SQL for the query...hence ![NATL_STR_NBR]
has no value.
 
G

Guest

Hi Paul:

Thanks! still the same error!

Dan

Paul Overway said:
I think you need a MoveFirst before starting your loop through the
recordset. You are being prompted for a parameter because there is no
current record when you set the SQL for the query...hence ![NATL_STR_NBR]
has no value.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


D said:
Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr] FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #],
[DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN
[DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not
Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like
"Contact
Store?"));
***********

Thank you,

Dan
Click to expand...
Click to expand...
 
P

Paul Overway

Put debug.print strSQL before you set qdf.SQL...put a breakpoint on qdf.SQL
= strSQL. When you get to the breakpoint, copy the SQL from the immediate
window and paste it into a new query....see what you are actually assigning
to qdf.SQL. I suspect it is wrong or missing an expected parameter. Also,
look at what you are doing this querydef...after the code has run you are
not returning it to its orginal state (so BaseSQL will be wrong next time )

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


D said:
Hi Paul:

Thanks! still the same error!

Dan

Paul Overway said:
I think you need a MoveFirst before starting your loop through the
recordset. You are being prompted for a parameter because there is no
current record when you set the SQL for the query...hence ![NATL_STR_NBR]
has no value.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


D said:
Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr]
FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #],
[DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN
[DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS)
Not
Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like
"Contact
Store?"));
***********

Thank you,

Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi.

Are you sure that you've given us the correct SQL statement? On closer
examination, your code is removing the last three characters of the SQL
statement, which is "));" without the quotes, then adding " WHERE
natl_str_nbr=" and the value in that record. This will result in a "syntax
error (missing operator)" message, not the "Two few parameters" error message.

In other words, your SQL statement is being altered so that it has two
incomplete WHERE clauses, which results in a syntax error.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


D said:
Thanks!

But the query runs perfectly fine?!

Dan

'69 Camaro said:
Hi.
What is wrong with this code?
Click to expand...

At least one of the field names in the query is misspelled or is no longer a
field name in the table where it once was.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


D said:
Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr] FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #], [DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact
Store?"));
***********

Thank you,

Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks a lot guys!

Nothing wrong with the SQL; it is something wrong with the Qry Name?! if I
change the name, I am passing that error but hmm, now it cannot find the
report?

Dan

Paul Overway said:
Put debug.print strSQL before you set qdf.SQL...put a breakpoint on qdf.SQL
= strSQL. When you get to the breakpoint, copy the SQL from the immediate
window and paste it into a new query....see what you are actually assigning
to qdf.SQL. I suspect it is wrong or missing an expected parameter. Also,
look at what you are doing this querydef...after the code has run you are
not returning it to its orginal state (so BaseSQL will be wrong next time )

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


D said:
Hi Paul:

Thanks! still the same error!

Dan

Paul Overway said:
I think you need a MoveFirst before starting your loop through the
recordset. You are being prompted for a parameter because there is no
current record when you set the SQL for the query...hence ![NATL_STR_NBR]
has no value.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Hi :

What is wrong with this code?

When I run it I get the above error:

Private Sub Command70_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset(("SELECT DISTINCT [natl_str_nbr]
FROM
[Invalid MI after depl to run]"), dbOpenDynaset, dbReadOnly)
Set qdf = CurrentDb.QueryDefs("[Invalid Mi after depl to run]")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL) - 3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE natl_str_nbr=" & ![NATL_STR_NBR]
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "[Invalid Mi to run]", _
"SnapshotFormat", "C:\Documents and Settings\dpopescu\Desktop\te\" &
![NATL_STR_NBR] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing


End Sub

*************The SQL is:

SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #],
[DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD = ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN
[DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR = [DS Complete].[Natl#]
WHERE (((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [SD] And Date()) AND
((ODSDBA_WATP1MIT.AMIL_FG)="N") AND ((ODSDBA_WATP1MIT.MENU_ITEM_STS)
Not
Like
"T") AND ((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like
"Contact
Store?"));
***********

Thank you,

Dan
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

New records 2
Filter - Repost 1
Filter 4
What will be the function? 2

Top