QryDef

D

Daniel

Hello

I have the following code and I keep getting an error about my WHERE
statement about a missing operator... but I can't seem to see my mistake for
the life of me. Could someone please point it out to me.

************
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim Criteria As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me![Work Packages List].ItemsSelected
stWhat = stWhat & Me![Work Packages List].ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Criteria = (CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria))))
Set loqd = CurrentDb.QueryDefs("DRM Report Qry 04")
stSQL = "SELECT [DRM Report Qry 03 (CDI Commit Date)].[Work Package],
[DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM Report Qry
03 (CDI Commit Date)].[Drawing History Id], [DRM Report Qry 03 (CDI Commit
Date)].[Drawing Number], [DRM Report Qry 03 (CDI Commit Date)].[Drawing
Issue], [DRM Report Qry 03 (CDI Commit Date)].[Drawing Title], [DRM Report
Qry 03 (CDI Commit Date)].[CDI Commit Date], [DRM Report Qry 03 (CDI Commit
Date)].[Request for Review], [DRM Information Tbl].[DRM Drawing Priority],
[DRM Information Tbl].[DRM Drawing Status], [DRM Information Tbl].[DMR Gilda
Status], [DRM Information Tbl].[DRM Comments]" & _
" FROM [DRM Report Qry 03 (CDI Commit Date)] LEFT JOIN
[DRM Information Tbl] ON [DRM Report Qry 03 (CDI Commit Date)].[Drawing
History Id] = [DRM Information Tbl].[Drawing History Id]" & _
" WHERE ([DRM Report Qry 03 (CDI Commit Date)].[Work
Package] IN (" & Criteria & "))" & _
" ORDER BY [DRM Report Qry 03 (CDI Commit Date)].[Work
Package], [DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM
Report Qry 03 (CDI Commit Date)].[Drawing Number], [DRM Report Qry 03 (CDI
Commit Date)].[Drawing Issue];"
loqd.SQL = stSQL
loqd.Close

**************

Thanks

Daniel
 
D

Duane Hookom

Have you tried placing a Debug.Print in the code to print the stSQL
variable? You can then paste the sql into a query sql view.
Is the Work Package field text or numeric?

I'm trying to image how nice your SQL might look if you didn't use spaces
and symbols in your table and field names.
 
D

Daniel

Duane,

1- Work Package are Text

2- I a beginner... you mentioned not using spaces. Do you abbreviate,
simply remove space use underscores instead?! Could you give me some of you
experience please.

Thanks,

Daniel

Duane Hookom said:
Have you tried placing a Debug.Print in the code to print the stSQL
variable? You can then paste the sql into a query sql view.
Is the Work Package field text or numeric?

I'm trying to image how nice your SQL might look if you didn't use spaces
and symbols in your table and field names.
--
Duane Hookom
MS Access MVP


Daniel said:
Hello

I have the following code and I keep getting an error about my WHERE
statement about a missing operator... but I can't seem to see my mistake for
the life of me. Could someone please point it out to me.

************
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim Criteria As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me![Work Packages List].ItemsSelected
stWhat = stWhat & Me![Work Packages List].ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Criteria = (CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria))))
Set loqd = CurrentDb.QueryDefs("DRM Report Qry 04")
stSQL = "SELECT [DRM Report Qry 03 (CDI Commit Date)].[Work Package],
[DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM Report Qry
03 (CDI Commit Date)].[Drawing History Id], [DRM Report Qry 03 (CDI Commit
Date)].[Drawing Number], [DRM Report Qry 03 (CDI Commit Date)].[Drawing
Issue], [DRM Report Qry 03 (CDI Commit Date)].[Drawing Title], [DRM Report
Qry 03 (CDI Commit Date)].[CDI Commit Date], [DRM Report Qry 03 (CDI Commit
Date)].[Request for Review], [DRM Information Tbl].[DRM Drawing Priority],
[DRM Information Tbl].[DRM Drawing Status], [DRM Information Tbl].[DMR Gilda
Status], [DRM Information Tbl].[DRM Comments]" & _
" FROM [DRM Report Qry 03 (CDI Commit Date)] LEFT JOIN
[DRM Information Tbl] ON [DRM Report Qry 03 (CDI Commit Date)].[Drawing
History Id] = [DRM Information Tbl].[Drawing History Id]" & _
" WHERE ([DRM Report Qry 03 (CDI Commit Date)].[Work
Package] IN (" & Criteria & "))" & _
" ORDER BY [DRM Report Qry 03 (CDI Commit Date)].[Work
Package], [DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM
Report Qry 03 (CDI Commit Date)].[Drawing Number], [DRM Report Qry 03 (CDI
Commit Date)].[Drawing Issue];"
loqd.SQL = stSQL
loqd.Close

**************

Thanks

Daniel
Click to expand...
Click to expand...
 
D

Duane Hookom

If your field is text, you must add in quotes. Try start with
stWhat = "": stCriteria = ""","""
You may need to trim some characters off the end.

Tony Toews has some naming convention resources at
http://www.granite.ab.ca/access/tipsindex.htm.

--
Duane Hookom
MS Access MVP


Daniel said:
Duane,

1- Work Package are Text

2- I a beginner... you mentioned not using spaces. Do you abbreviate,
simply remove space use underscores instead?! Could you give me some of you
experience please.

Thanks,

Daniel

Duane Hookom said:
Have you tried placing a Debug.Print in the code to print the stSQL
variable? You can then paste the sql into a query sql view.
Is the Work Package field text or numeric?

I'm trying to image how nice your SQL might look if you didn't use spaces
and symbols in your table and field names.
--
Duane Hookom
MS Access MVP


Daniel said:
Hello

I have the following code and I keep getting an error about my WHERE
statement about a missing operator... but I can't seem to see my
Click to expand...
mistake
for
the life of me. Could someone please point it out to me.

************
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim Criteria As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me![Work Packages List].ItemsSelected
stWhat = stWhat & Me![Work Packages List].ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
Criteria = (CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria))))
Set loqd = CurrentDb.QueryDefs("DRM Report Qry 04")
stSQL = "SELECT [DRM Report Qry 03 (CDI Commit Date)].[Work Package],
[DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM
Click to expand...
Report
Qry
03 (CDI Commit Date)].[Drawing History Id], [DRM Report Qry 03 (CDI Commit
Date)].[Drawing Number], [DRM Report Qry 03 (CDI Commit Date)].[Drawing
Issue], [DRM Report Qry 03 (CDI Commit Date)].[Drawing Title], [DRM Report
Qry 03 (CDI Commit Date)].[CDI Commit Date], [DRM Report Qry 03 (CDI Commit
Date)].[Request for Review], [DRM Information Tbl].[DRM Drawing Priority],
[DRM Information Tbl].[DRM Drawing Status], [DRM Information Tbl].[DMR Gilda
Status], [DRM Information Tbl].[DRM Comments]" & _
" FROM [DRM Report Qry 03 (CDI Commit Date)] LEFT JOIN
[DRM Information Tbl] ON [DRM Report Qry 03 (CDI Commit Date)].[Drawing
History Id] = [DRM Information Tbl].[Drawing History Id]" & _
" WHERE ([DRM Report Qry 03 (CDI Commit Date)].[Work
Package] IN (" & Criteria & "))" & _
" ORDER BY [DRM Report Qry 03 (CDI Commit Date)].[Work
Package], [DRM Report Qry 03 (CDI Commit Date)].[Associated Project], [DRM
Report Qry 03 (CDI Commit Date)].[Drawing Number], [DRM Report Qry 03 (CDI
Commit Date)].[Drawing Issue];"
loqd.SQL = stSQL
loqd.Close

**************

Thanks

Daniel
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

multiple multiselect listbox to limit records in report 5
None Uptables? 0
Concencate String from items in Multiselect List 5
What's wrong with the following code 1
Multi Select List to a Report Dev Ashish 3
Report not pulling all that I am looking for 0
Duplicate Record query doesn't work 0
Ask for Criteria. 1

Top