You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a
debug.print strSQL
line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have made
the problem obvious right away.
Nikos
Simonglencross wrote:
Nikos,
I copied and pasted the following statemant but I am geting a syntax
error,
any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, tblSubscribers.Title,
tblSubscribers.Forename, " _
tblSubscribers.Surname, tblSubscribers.Company,
tblSubscribers.Address,
tblSubscribers.City, _
tblSubscribers.[Country/Region], tblSubscribers.PostalCode, _
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID = tblsubscriptions.MailingListID" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria & ");"
Kind Regards
Simon
Simon,
Let Access do the hard work for you! Make the query that you need in
design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes; then
revert to SQL view, and the SQL expression is ready for you to copy and
paste in your code, adding just the WHERE clause exactly as you are
doing.
HTH,
Nikos
Simonglencross wrote:
Nikos,
You will pleased to hear I am making some progress I have now go the
multi
select list box to work with the following statement
Private Sub Command14_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("allsubscribersnew")
' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) &
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
This diplays to my screen mailinglistid of each subscriber and also the
cat
type, but I also need it to show me the name and address of each
subscriber
and the catcost, once it has done that I then need it to out put to
labels
automaticaly! Have you any ideas I have tried adding this statement in
but
it doesnt work.
strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *
FROM
tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =
tblsubscriptions.MailingListID
" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria & ");"
All of your help is much appreciated!
Simon
Simon,
Add a command button on the same form to open the report; the code
behind the button's click event should be something like:
For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) &
"'
AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If
Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual
name;
* remember, remove the WHERE clause on mag types from the original
query.
HTH,
Nikos
Simonglencross wrote:
Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called
Catalogue
Types
with my different types of Catalogues. The list box has the
multiselect
option enabled. Where would you suggest I go from here?
Kind Regards
Simon
Simon,
I would remove the mag type filtering from the query altogether, and
use
some VB code to "read" the listbox selections and put together a
WHERE
condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form. The
code
behind the button would look something like:
For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND
"
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If
Note: code above assumes MTyp_ID is type text; if numeric, use this
second line instead:
ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "
HTH,
Nikos
Simonglencross wrote:
Nikos,
The VBA way sounds like the better choice would you have any
resources
or
information which would help me?I'm reading up on the multi select
list
boxes at the moment.
Thanks again Nikos!
message
Simon,
The "proper" way to do it is to use a multi-select listbox on a
form,
and some VBA code behind it. The "quick and dirty" way (i.e. no
coding)
is to add an extra field in tblMagazineTypes (type yes/no), and
show
that on the form (in a subform?) in either continuous forms or
datasheet
view, so the user can click for including a type; then you will
need
to
include this field in your query as well, and filter on the True
values.
HTH,
Nikos
Simonglencross wrote:
Thanks for that Nikos,
Do you know how I can set this up so the user only has to select
the
magazine types from a form and then clicks a button to print out
the
labels,
I can't get me head round it at all.
Many Thanks
Simon
message
Simon,
Your data structure seems to be correct; if it's what I think it
is
based on your last post, then the query to print your labels off
of
should look something like:
SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,
tblSubscribers.Address
FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON
tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER JOIN
tblSubscribers ON tblSubscriptions.Sber_ID =
tblSubscribers.Sber_ID
WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or
(tblMagazineTypes.MTyp_ID)="mt4"))
GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,
tblSubscribers.Address
(hope my assumptions on field names are clear!)
HTH,
Nikos
Simonglencross wrote:
I shall try and explain this a little better.
I have three tables tblSubscribers, tblSubscriptions and
tblMagazineTypes.
tblSubscribers contains all names and addresses'
tblsubscriptions
holds
all
of the subscriptions information i.e what people have
subscribed
to
and
the
last table tblMagazineTypes contains the 6 different magazime
types.
I need to be able to set up a way where the user can select any
magazine
type combination and print off the labels with the subscribers
names
and
addresses on rememebering that I only need one label per
subscriber
regardless of the magazine type. for example mr smith may have
subscribed to
magazine 1,2 and 3 and the operator has selected to print
labels
for
magazine 1 and 3 in this instance I would only want 1 address
label
and
not
2.
Hope this helps a little more!
message
Can you give us some sample data?
Simonglencross wrote:
I understand this but the database already has a large amount
of
data
in
it,
to do what you suggest would invole alot of work is there
another
way?
or
can you explain what I would need to do with the current
data?
Your help is much appreciated!
Many Thanks
Simon
message
Simon,
This is a classic many-to-many relationship between
subscriber
s
and
magazines. To handle efficiently, you need to modify your
data
design
so
you have one table for subscribers (like you already do),
one
for
magazines (tblMagazines with an ID field, name field plus
whatever
else
required), and a third table called tblSubscriptions (with a
subscriber
ID and a magazine ID foreign keys) to represent actual
subcriptions,
breaking the many-to-many relationship down to two
one-to-many
ones.
HTH,
Nikos
Simonglencross wrote:
Sory for reposting but desperate for some help
I have created a database and have two tables one called
tblSubscribers
and
another called tblSubscriptions. Within these tables I
