re posted

  • Thread starter Thread starter Simonglencross
  • Start date Start date
Nikko,


Just adding in the Group BY and I am getting an error miss operator in Query
expression '*' and the following appears in the immediate window

SELECT 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('1','2','3','4','5','6','7') GROUP BY
tblSubscribers.Title, tblSubscribers.Forename, tblSubscribers.Surname,
tblSubscribers.Company, tblSubscribers.Address, tblSubscribers.City,
tblSubscribers.[Country/Region], tblSubscribers.PostalCode, *



Any Ideas?


Thanks Simon



Nikos Yannacopoulos said:
For single records:

strSQL = "SELECT 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 & ") " & _
"GROUP BY tblSubscribers.Title, tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, * "
Debug.Print strSQL

or something like that... To check, go back to the query where you
copied the SQL string from, and set the Totals to on (View > Totals",
keeping the default setting Group By. While you are at it, save the
query, you will need it for the report.

Coming to your second question, you don't need to modify the query
everytime! Keep the saved query as above, i.e. with no filter on
cattypes, and base your report on it, so it will include all subscribers
if opened directly by hand. Then with the use of the code, you can apply
the filter on the report at runtime, without changing the query. This is
exactly what my original code sample did. Assuming you have saved the
base query (no filtering) and based your report on it, the code bhind
the button should be:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "Typ_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.

This is actually my first post this morning, five postings ago! Do you
see now?

Nikos

Nikos,

I see what I was doing wrong now Sorry Nikos!

Ok that works great the only other two things I need to do now are

If I select a for instance cat 1 and cat 2 from the list box and a
subscriber has subscribed to both cat 1 and cat 2 I only want 1 label
printed out for that Subscriber.

The other thing is I want instead of the query screen displaying the data I
want to be able to print off the labels

Curretly at the end I have


' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew", _
view:=acViewPreview

This diplays the results to screen.

Many Thanks

Simon




But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Simonglencross wrote:

Nikos

Nothing appears in the immediate window here is the code to show you
Click to expand...

where I
have inserted the debug.print strSQL.


SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon



Do the debug trick, and post back the SQL expression that you get in the
immediate window (and the exact error message and line of code it
occurs on).

Simonglencross wrote:


Nikos,

I have done what you suggested but still no difference and I still get

the


same syntax error message, any ideas why?

Simon





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



message






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




message








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
Click to expand...
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!



in

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



in


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

have


a



number







of








subscribers and 6 different magazine type's setup in
the

subscriptions








table.
I had setup the database to print off labels, these
where

separated






in








to










cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a
Click to expand...
subscriber

to
subscribe






to








any number of catalogues and these catalogues may
Click to expand...
well

be
sent




out






together










for example this week I am sending out cat 1, cat 2 and

cat3



but





next







week










it may be cat 2 and cat 6 but I also need to be careful

as


1



subscriber








might subscribe to more than 1 catalogue and
Click to expand...
therefore

I
would




only






want









one










label with the subscribers name and address and not one

for


each





catalogue










selected.

Any advise would be much appreciated, I hope you can

understand




what







I








am










trying to achieve.


Kind Regards


Simon
Click to expand...
Click to expand...
Click to expand...
 
Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.


Many Thanks


Simon

Nikos Yannacopoulos said:
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
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
Click to expand...
Click to expand...
 
Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.


When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )


is, probably, the correction.


Hoping it may help,
Vanderghast, Access MVP


Simonglencross said:
Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.


Many Thanks


Simon

Nikos Yannacopoulos said:
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
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!



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





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!







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 subscribers

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 have a

number


of



subscribers and 6 different magazine type's setup in the

subscriptions



table.
I had setup the database to print off labels, these where

separated

in



to





cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to



any number of catalogues and these catalogues may well be sent

out

together





for example this week I am sending out cat 1, cat 2 and cat3 but

next


week





it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber



might subscribe to more than 1 catalogue and therefore I would

only

want




one





label with the subscribers name and address and not one for each

catalogue





selected.

Any advise would be much appreciated, I hope you can
understand

what


I



am





trying to achieve.


Kind Regards


Simon
Click to expand...
Click to expand...
Click to expand...
 
Correct... that's what is should have been in the first place. Sorry for
the oversight (always a possibility with untested, sample code).

Thanks Michel!

Nikos

Michel said:
Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.


When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )


is, probably, the correction.


Hoping it may help,
Vanderghast, Access MVP


Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.


Many Thanks


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) & "'
Click to expand...

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!




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






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 subscribers

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 have a

number



of




subscribers and 6 different magazine type's setup in the

subscriptions




table.
I had setup the database to print off labels, these where

separated


in




to






cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe


to




any number of catalogues and these catalogues may well be sent

out


together






for example this week I am sending out cat 1, cat 2 and cat3
but

next



week






it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber




might subscribe to more than 1 catalogue and therefore I would

only


want





one






label with the subscribers name and address and not one for
each

catalogue






selected.

Any advise would be much appreciated, I hope you can
understand

what



I




am






trying to achieve.


Kind Regards


Simon
Click to expand...
Click to expand...
Click to expand...
 
Nikos,
Just a little note to say thank you for all of your help yesterday!


Regards

Simon


Nikos Yannacopoulos said:
Correct... that's what is should have been in the first place. Sorry for
the oversight (always a possibility with untested, sample code).

Thanks Michel!

Nikos

Michel said:
Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.


When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )


is, probably, the correction.


Hoping it may help,
Vanderghast, Access MVP


Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.


Many Thanks


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!




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






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 subscribers

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 have a

number



of




subscribers and 6 different magazine type's setup in the

subscriptions




table.
I had setup the database to print off labels, these where

separated


in




to






cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe


to




any number of catalogues and these catalogues may well be sent

out


together






for example this week I am sending out cat 1, cat 2 and cat3

but

next



week






it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber




might subscribe to more than 1 catalogue and therefore I would

only


want





one






label with the subscribers name and address and not one for

each

catalogue






selected.

Any advise would be much appreciated, I hope you can
understand

what



I




am






trying to achieve.


Kind Regards


Simon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top