Calculated query field will not filter

[Ceebaby via AccessMonster.com]

Hi Everyone
I have a query of addresses where I would like to filter on even door numbers.
The StreetNo field is a text field and so I have used the VAL function to get
the numbers only as a calculated field called [Even], and have then tried to
filter the query using the [Even] calculated field in another calculated
field called [Door].

So far I have

SELECT [TBL LLPG].UPRN, [TBL LLPG].SAON, [TBL LLPG].Building, [TBL LLPG].
StreetNo, [TBL LLPG].Street, "Mellenden" AS B, Val([StreetNo]) Mod 2 AS Even,
[even] AS Door
FROM [TBL LLPG]
WHERE ((([TBL LLPG].StreetNo) Between "16" And "132") AND (([TBL LLPG].Street)
Like "Ham Rye") AND (([even])=0));

But when I try to run the query I am get a parameter box prompting for the
caluclated field Even. I have tried several variations but cannot get the
table to list only the even door numbers.

Any help would be most appreciated.
Cheers
Ceebaby, London

 

[Allen Browne]

Repeat the expression in the WHERE clause: JET doesn't recognise the alias
there.

As a side note, this still may not work as you expect:
- Val() generates an error for Null values.
- the Between clause won't give you the correct numeric values, as it will
perform a *text* comparison (i.e. character by character.)

 

[Rob Parker]

I think your problem comes from trying to use a calculated field name as
another field in the same query - something that you can't do. I'm not sure
why you have the field [Even] AS Door in your Select statement, since you've
already got that field (the calculated field), and you're not setting the
criteria on Door (which wouldn't work, for the same reason). This should
work:

SELECT [TBL LLPG].UPRN, [TBL LLPG].SAON, [TBL LLPG].Building, [TBL LLPG].
StreetNo,
[TBL LLPG].Street, "Mellenden" AS B, Val([StreetNo]) Mod 2 AS Even,
FROM [TBL LLPG]
WHERE ([TBL LLPG].StreetNo Between "16" And "132") AND ([TBL LLPG].Street
Like "Ham Rye") AND ([Val([StreetNo]) Mod 2])=0);

Note: bracketing in the WHERE clause may be a little confused - Access puts
in too many, and I've (hopefully) removed all except those that matter. If
you're doing this in the query design grid, just put the criteria (0) in
your calculated [Even] field.

HTH,

Rob

 

[Ceebaby via AccessMonster.com]

Hi Allen

Thank you so much for your response. I use the tips from your website often
they have been a great help. SQL however is not my greatest strength yet so
please bear with me.

I am not sure what you mean by repeat the expression in the where clause. Do
I repeat this expression
Even:VAL([StreetNo] )mod 2 again in a new field column or

do I create a new column with VAL([Even]) mod 2 and then have 0 in the
criteria column. Both still prompt for the even calculated field.

I understand what you are saying about the text field producing error. As
there are not too many records returned I can check these records and decide
which will stay and which will go. I just need to get the majority of the
even door numbers.

Thank you once again for your prompt response.

Regards
Ceebaby

Repeat the expression in the WHERE clause: JET doesn't recognise the alias
there.

As a side note, this still may not work as you expect:
- Val() generates an error for Null values.
- the Between clause won't give you the correct numeric values, as it will
perform a *text* comparison (i.e. character by character.)

Hi Everyone
I have a query of addresses where I would like to filter on even door

[quoted text clipped - 24 lines]

Cheers
Ceebaby, London

 

[Ceebaby via AccessMonster.com]

Hi Rob

Thank you for your quick response.

I have tried what you suggested and receive a syntax error in the where
clause line in SQL
and a data mismatch error when I try your design grid suggestion .

Thanks for any further help you can offer.

Cheers
Ceebaby
London

I think your problem comes from trying to use a calculated field name as
another field in the same query - something that you can't do. I'm not sure
why you have the field [Even] AS Door in your Select statement, since you've
already got that field (the calculated field), and you're not setting the
criteria on Door (which wouldn't work, for the same reason). This should
work:

SELECT [TBL LLPG].UPRN, [TBL LLPG].SAON, [TBL LLPG].Building, [TBL LLPG].
StreetNo,
[TBL LLPG].Street, "Mellenden" AS B, Val([StreetNo]) Mod 2 AS Even,
FROM [TBL LLPG]
WHERE ([TBL LLPG].StreetNo Between "16" And "132") AND ([TBL LLPG].Street
Like "Ham Rye") AND ([Val([StreetNo]) Mod 2])=0);

Note: bracketing in the WHERE clause may be a little confused - Access puts
in too many, and I've (hopefully) removed all except those that matter. If
you're doing this in the query design grid, just put the criteria (0) in
your calculated [Even] field.

HTH,

Rob

Hi Everyone
I have a query of addresses where I would like to filter on even door

[quoted text clipped - 24 lines]

Cheers
Ceebaby, London

 

[Duane Hookom]

I think both Allen and Rob are suggesting something like:

SELECT [TBL LLPG].UPRN, [TBL LLPG].SAON, [TBL LLPG].Building,
[TBL LLPG].StreetNo, [TBL LLPG].Street, "Mellenden" AS B,
Val([StreetNo]) Mod 2 AS Even, Val([StreetNo]) Mod 2 AS Door
FROM [TBL LLPG]
WHERE Val([TBL LLPG].StreetNo) Between 16 And 132 AND
[TBL LLPG].Street) = "Ham Rye" AND Val([StreetNo]) Mod 2=0;

I'm not sure why you used
Like "Ham Rye"
without any wildcards. You should be using wildcards or
= "Ham Rye"

It looks like your query calculates the same value into [Even] and [Door].
--
Duane Hookom
Microsoft Access MVP

Hi Allen

Thank you so much for your response. I use the tips from your website often
they have been a great help. SQL however is not my greatest strength yet so
please bear with me.

I am not sure what you mean by repeat the expression in the where clause. Do
I repeat this expression
Even:VAL([StreetNo] )mod 2 again in a new field column or

do I create a new column with VAL([Even]) mod 2 and then have 0 in the
criteria column. Both still prompt for the even calculated field.

I understand what you are saying about the text field producing error. As
there are not too many records returned I can check these records and decide
which will stay and which will go. I just need to get the majority of the
even door numbers.

Thank you once again for your prompt response.

Regards
Ceebaby

Repeat the expression in the WHERE clause: JET doesn't recognise the alias
there.

As a side note, this still may not work as you expect:
- Val() generates an error for Null values.
- the Between clause won't give you the correct numeric values, as it will
perform a *text* comparison (i.e. character by character.)

Hi Everyone
I have a query of addresses where I would like to filter on even door

[quoted text clipped - 24 lines]

Cheers
Ceebaby, London

--



.

 

[John Spencer]

Pardon me for jumping in. Using Allen Browne's sage advice, I would expect to
see the query rewritten as follows:

SELECT [TBL LLPG].UPRN
, [TBL LLPG].SAON
, [TBL LLPG].Building
, [TBL LLPG].StreetNo
, [TBL LLPG].Street
, "Mellenden" AS B
, Val(Nz([StreetNo],1)) Mod 2 AS Even
, Val([StreetNo]) AS Door
FROM [TBL LLPG]
WHERE Val(Nz([StreetNo],1)) Between 16 And 132
AND [TBL LLPG].Street Like "Ham Rye"
AND Val(Nz([StreetNo],1)) Mod 2=0;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Allen

Thank you so much for your response. I use the tips from your website often
they have been a great help. SQL however is not my greatest strength yet so
please bear with me.

I am not sure what you mean by repeat the expression in the where clause. Do
I repeat this expression
Even:VAL([StreetNo] )mod 2 again in a new field column or

do I create a new column with VAL([Even]) mod 2 and then have 0 in the
criteria column. Both still prompt for the even calculated field.

I understand what you are saying about the text field producing error. As
there are not too many records returned I can check these records and decide
which will stay and which will go. I just need to get the majority of the
even door numbers.

Thank you once again for your prompt response.

Regards
Ceebaby

Repeat the expression in the WHERE clause: JET doesn't recognise the alias
there.

As a side note, this still may not work as you expect:
- Val() generates an error for Null values.
- the Between clause won't give you the correct numeric values, as it will
perform a *text* comparison (i.e. character by character.)

Hi Everyone
I have a query of addresses where I would like to filter on even door

[quoted text clipped - 24 lines]

Cheers
Ceebaby, London

 

[Ceebaby via AccessMonster.com]

Hi John

Thank you very much for your suggestion - it finally worked.

I think my initial problem was that the [streetno] field is a text field and
as Allen said some records were returning error. My thanks to both Allen and
Rob also for their help I understand more than I did, you guys are really
doing a sterling job. I hope to be as good some day.

Have a good day.

Cheers
Ceebaby
London

Pardon me for jumping in. Using Allen Browne's sage advice, I would expect to
see the query rewritten as follows:

SELECT [TBL LLPG].UPRN
, [TBL LLPG].SAON
, [TBL LLPG].Building
, [TBL LLPG].StreetNo
, [TBL LLPG].Street
, "Mellenden" AS B
, Val(Nz([StreetNo],1)) Mod 2 AS Even
, Val([StreetNo]) AS Door
FROM [TBL LLPG]
WHERE Val(Nz([StreetNo],1)) Between 16 And 132
AND [TBL LLPG].Street Like "Ham Rye"
AND Val(Nz([StreetNo],1)) Mod 2=0;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Allen

[quoted text clipped - 32 lines]

 

[Ceebaby via AccessMonster.com]

Hi Duane

I have just seen your response. Thank you for taking the time out to respond.
I finally got it working through John's suggestion. Thanks also for setting
me straight on my criteria you were right I should have used = instead of
like.

Have a good day.

Cheers
Ceebaby
London

I think both Allen and Rob are suggesting something like:

SELECT [TBL LLPG].UPRN, [TBL LLPG].SAON, [TBL LLPG].Building,
[TBL LLPG].StreetNo, [TBL LLPG].Street, "Mellenden" AS B,
Val([StreetNo]) Mod 2 AS Even, Val([StreetNo]) Mod 2 AS Door
FROM [TBL LLPG]
WHERE Val([TBL LLPG].StreetNo) Between 16 And 132 AND
[TBL LLPG].Street) = "Ham Rye" AND Val([StreetNo]) Mod 2=0;

I'm not sure why you used
Like "Ham Rye"
without any wildcards. You should be using wildcards or
= "Ham Rye"

It looks like your query calculates the same value into [Even] and [Door].

Hi Allen

[quoted text clipped - 32 lines]