If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculated query field will not filter
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#2
|
|||
|
|||
Calculated query field will not filter
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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ceebaby via AccessMonster.com" u6919@uwe wrote in message news:a45b00dbda17a@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#3
|
|||
|
|||
Calculated query field will not filter
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" u6919@uwe wrote in message news:a45b00dbda17a@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#4
|
|||
|
|||
Calculated query field will not filter
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 Allen Browne wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#5
|
|||
|
|||
Calculated query field will not filter
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 Rob Parker wrote: 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 -- Ceebaby Trying to be great at Access Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
#6
|
|||
|
|||
Calculated query field will not filter
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 "Ceebaby via AccessMonster.com" wrote: 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 Allen Browne wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 . |
#7
|
|||
|
|||
Calculated query field will not filter
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 Ceebaby via AccessMonster.com wrote: 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 Allen Browne wrote: 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 |
#8
|
|||
|
|||
Calculated query field will not filter
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 John Spencer wrote: 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] Cheers Ceebaby, London -- Ceebaby Trying to be great at Access Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Calculated query field will not filter
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 Duane Hookom wrote: 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] Cheers Ceebaby, London -- Ceebaby Trying to be great at Access Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|