Non numeric data

[Guest]

I have generated the criteria that I want to use to filter a sub report, into
a table (the values for this come from a list box), and since the criteria
need to be of the type >0, 1 0r 2, etc they come from a text field.

If I copy and paste the generated values directly into the query then it all
works fine, however if I use a Dlookup then I get a data mismatch because the
data is text and the field is numeric.

This is my query:

SELECT Stewards.StewardID, Stewards.Title, Stewards.Surname,
Stewards.Council, Stewards.Suffix
FROM Stewards
WHERE (((Stewards.Council)=DLookUp("value","tblsys","ID=1")));

Value is text and Stewards.Council is numeric (autonumber)

Any thoughts please

Helen

 

[Guest]

WHERE (((Stewards.Council)=Val(DLookUp("value","tblsys","ID=1"))));

 

[Guest]

That's great, and works fine for eg 1 or 2, but will not work for >0 . For
some reason this gives me the records that =0 !

 

[Guest]

I guess I am a little confused. Is what you are saying is if ID is 1 or 2
you want your DLookup to return only 1 or only 2, but if ID is 0, you want it
to return everything > 0? Can there be any entries with < 0 in "value"?

 

[Guest]

On further investigation I realise that infact your solution doesn't work -
what happens is it only takes the first value - so instead of getting results
where the ID is "1 or 2" val just takes the first number so I only get
results for ID = 1.

That is why the ">0" did not work.

Let me explain a little more fully:

Stewards.council is a look up field, the look up value can be "Full",
"Honarary", "Co-opted" and perhaps more values may be added. I am using a
list box (multi value) to allow the user to select which they want to appear
in a report - but this value is actually in a subreport. So I hit on the
idea of storing the criteria in a table and then retrieving that in my SQL
that drives the subreport.

The values that I end up with in my tblsys eg "1 or 2" ">0" work fine if I
copy and paste then directly into the criteria in query, but when I try and
use them via Dlookup they appear to be non numeric (no I don't have the " "
round them, I've put that in for "Clarity"!) then I get data mismatch....

Any furhter thoughts?

 

[Guest]

Have you tried putting the criteria directly into the query? I would give
that a try. It will execute much faster than a DLookup. To get the query to
recognize it, you need to fully qualify it:
Forms!MyFormName!MySubFormName!MyControlName

 

[Guest]

That won't really work, because I have to build the criteria, I don't know
how I would take the values straight from the list box.....

 

[Guest]

If it is a multi select list box and you have a command button that runs the
report. You could build the where condition programmatically and put it in a
String variable and use the string variable in the Where argument of the
OpenReport method.

 

[Guest]

The problem is the criteria are for a SUB-report - that's why I'm going
through these convolutions!

 

[Guest]

Oh, yeah, I forgot that. i'll have to experiment with this a little and get
back to you.

 

[Guest]

That would be great

Oh, yeah, I forgot that. i'll have to experiment with this a little and get
back to you.

 

[Guest]

Hi, Helen.

I have generated the criteria that I want to use to filter a sub report, into
a table (the values for this come from a list box), and since the criteria
need to be of the type >0, 1 0r 2, etc they come from a text field.

Unfortunately, there are several problems with this approach. You're
storing multiple values in a field, which are often difficult to extract
using a query. You also assume that "1 or 2" as the criteria is what Jet
uses to process the query. Actually, what you type into the QBE Design Grid
is translated into SQL so that Jet can carry out your commands. For example,
when you type:

1 Or 2

.. . . as the Criteria in the Council field in the QBE Design Grid, it's
translated into the following SQL clause:

WHERE (Council = 1 Or Council = 2);

(Okay. With a lot more parentheses, but you get the picture.) So even if
the DLookup value could be evaluated "as is" (but it can't) from what's saved
in the tblSys table, it would look like this:

WHERE (Council = 1 Or 2);

That "2" evaluates to TRUE, so all records would be returned, not just the
ones you intended. Ultimately, you would need to parse the string value
stored in your table and build an appropriate WHERE clause.

Also, I notice elsethread that you mentioned you are using Lookup Fields in
your table. This practice causes multiple problems, so Access experts advise
avoiding them. Please see the following Web page for more information:

http://www.mvps.org/access/lookupfields.htm

Your best bet is to use a separate query for each subreport. For example,
to show the records where council is 1 or 2 on a subreport:

SELECT Stewards.StewardID, Stewards.Title, Stewards.Surname,
Stewards.Council, Stewards.Suffix
FROM Stewards
WHERE (Council = 1 Or Council = 2);

To show the records where council is greater than 0 on another subreport:

SELECT Stewards.StewardID, Stewards.Title, Stewards.Surname,
Stewards.Council, Stewards.Suffix
FROM Stewards
WHERE (Council > 0);

To show the records where council is between 3 and 6 on yet another subreport:

SELECT Stewards.StewardID, Stewards.Title, Stewards.Surname,
Stewards.Council, Stewards.Suffix
FROM Stewards
WHERE (Council BETWEEN 3 AND 6);

.. . . et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Guest]

Hi Gunny,

I read the article on lookups and got very confused - how do I then create a
drop down list that a user can easily add to?

But thanks for your explanation as to why my method is not working - I will
try another approach.

 

['69 Camaro]

Hi, Helen.

how do I then create a
drop down list that a user can easily add to?

The combo box should be on the form for the user to select an item, not
built into the table itself. Do you need the step-by-step instructions for
creating the combo box on the form, or do you need the instructions for
allowing the user to easily add items to the list displayed in the combo box
on the form?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

What confuses me is what value you store in the table - in the case that I am
using I have a list of people (stewards), who could be members of council -
there membership can have a different type eg Full, Co-opted, Honarary and I
would like the user to be able to edit (ie add / amend) this list.

What I have done is to store the CouncilID vaule from the Council table, in
the Stewards table - the council table consists of ID and description.

Is this the wrong thing to do? How else do I make the list easily editable?

Many thanks

 

[Guest]

Hi, Helen.

What I have done is to store the CouncilID vaule from the Council table, in
the Stewards table - the council table consists of ID and description.

Is this the wrong thing to do?

Let me make sure that I have the names straight. I believe that you have
the following table structures:

Table: Council
ID, primary key
.. . . and other fields

Table: Stewards
ID, primary key (but perhaps some other name, as you haven't mentioned it)
CouncilID, foreign key to Council table (ID in the Council table)
.. . . and other fields

If this is the case, then you are doing it correctly. You've designed it
just as a relational database should be designed.

How else do I make the list easily editable?

By placing a combo box on the bound form, with the bound field as CouncilID.
The combo box's Column Count Propert will be set to 2, and the Column Widths
Property will be set to 0;1" and the Row Source Type Property will be
Table/Query. The Row Source Property will be the name of a query, defined
such as this:

SELECT ID, CouncilName
FROM Council
ORDER BY CouncilName;

In Form View, the user will be able to see the currrent CouncilName selected
in the combo box, but the actual value stored in the Stewards table is the
CouncilID. The user will be able to assign any other CouncilName on the list
in the combo box, and that CouncilName's corresponding primary key will be
stored in the CouncilID column of the Stewards table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Guest]

Let me make sure that I have the names straight. I believe that you have
the following table structures:

Table: Council
ID, primary key
. . . and other fields

Yup - pretty much just ID and description

Table: Stewards
ID, primary key (but perhaps some other name, as you haven't mentioned it)
CouncilID, foreign key to Council table (ID in the Council table)
. . . and other fields

Again pretty much

If this is the case, then you are doing it correctly. You've designed it
just as a relational database should be designed.

By placing a combo box on the bound form, with the bound field as CouncilID.
The combo box's Column Count Propert will be set to 2, and the Column Widths
Property will be set to 0;1" and the Row Source Type Property will be
Table/Query. The Row Source Property will be the name of a query, defined
such as this:

SELECT ID, CouncilName
FROM Council
ORDER BY CouncilName;

In Form View, the user will be able to see the currrent CouncilName selected
in the combo box, but the actual value stored in the Stewards table is the
CouncilID. The user will be able to assign any other CouncilName on the list
in the combo box, and that CouncilName's corresponding primary key will be
stored in the CouncilID column of the Stewards table.

That's as I have it...

So what's the big deal about lookups - how is that different from what I
have done - this is where I am confused, because I thought I was doing it
correctly....and now it appears that I am

Meanwhile I will struggle on with passing a multiple selection to a
subreport - but from what you have said earlier I *might* just be able to do
that - But I think I will leave it til Monday - thanks for all your help

 

['69 Camaro]

Hi, Helen.

That's as I have it...

That's how it looks, but there's a big difference between building that
combo box control on the form and having one embedded in the TableDef object
as a Lookup Field.

So what's the big deal about lookups - how is that different from what I
have done

Table lookups are fine. That's how relational database applications are
designed. However, a Lookup Field (a combo box embedded in the table's
field when the table is in Datasheet View) is a horse of a different color.
You read about the problems that Lookup Fields cause in the article at the
link that I provided you. These problems don't occur if the property in the
"Lookup" tab of the table's field remains as the default "Text box," and you
build your combo boxes and list boxes for the table lookups as controls on
your forms, instead.

this is where I am confused, because I thought I was doing it
correctly....and now it appears that I am

I think that the terminology is confusing. Lookup Fields sound an awful lot
like table lookups, but there are adverse side effects with Lookup FIelds,
even if they do look the same on the form to the user.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Oh I see! So I have been doing it wrong, because although I had the combo
box on the form correctly I did have the property set to combo box in the
table.

I will now go back and read the article again.

Many thanks

 

['69 Camaro]

You're very welcome. Glad to help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.