Needed to build a searchform in MS Access which will allow searching of one or multiple fields. I know that in Sql, this can be accomplished by using:
SELECT * FROM Table where IsNull([ID],'') LIKE IsNull('%' + @ID + '%','%')
However, Access doesn't recognize this as a valid statement, so back to the drawing board...
Eventually I came up with this solution...
IIf(IsNull([Forms]![Form]![Control]),[FieldName],LIKE "*" & [Forms]![Form]![Control] & "*")
With Form being the Form name, and Control being the Control Name.
In a nutshell, this is saying If IsNull(Form Control) Is True, i.e., If no value was put into the search form for this field, then simply show all items in that field. Otherwise, show all items that are LIKE (Wildcard Search) the search criteria.
Hope this helps someone else...
Thursday, May 20, 2010
Access True Wildcard Form Search
Posted by Joshua Blackstone at 12:54 PM 0 comments
Friday, April 23, 2010
YQL create states Dropdown List
OK, a very simple one today.
Using YQL's upcoming.state table, you can create a simple dropdown list to store either the State name or the State Abbreviation.
HTML Page:
Create a select element, and give it an id of ddlStates.
The jQuery Script:
$(function () {
var query = "select code, name from upcoming.state where country_id = 1 and id <= 51";
$.getJSON(
"http://query.yahooapis.com/v1/public/yql?q=" + encodeURIComponent(query) + "&format=json&callback=?",
function (data) {
$.each(data.query.results,
function (index, item) {
for (i = 0; i < item.length; i++) {
var each = item[i];
var stateName = each.name.toUpperCase();
var stateCode = each.code.toUpperCase();
$('<option></option>').val(stateName).html(stateCode).appendTo("#ddlStates");
}
});
});
});
You'll note here I simply changed it to Uppercase, and used the value of State Name, while displaying the state code.
Download Script at: http://www.joshuablackstone.com/Scripts/StatesDropdown.js
And all this time I was storing the data in a database... Ugh...
Posted by Joshua Blackstone at 10:35 AM 0 comments
Labels: yql json yahoo US Dropdownlist
Thursday, April 22, 2010
Using YQL create Food Network Search
More YQL, gotta love how easy they have made this!!
I have found that Food Network can be quite good at making boring foods, well... not so boring. Wondered what it would take to search their system from another webpage. They don't have a feed for you, so what to do? YQL's html table...
Go to the YQL Console, and enter:
select h3 from html where url="http://www.foodnetwork.com/search/delegate.do?fnSearchString=FOODITEM&fnSearchType=site" and
xpath='//div[@class="result-item"]'
as your query. You'll notice it breaks it down to a simple array of h3 elements.
From there, simply create an array and show the results in an unordered list.
HTML Page:
- Add jQuery to your Page
- Create a textbox called txtSearch
- Create a button called btnSearch
- Create an unordered list called searchResults
JS Code:
function searchRecipes(search) {
$('#searchResults').empty();
$.getJSON(
"http://query.yahooapis.com/v1/public/yql?q=select%20h3%20from%20html(5)%20where%20url%3D%22http%3A%2F%2Fwww.foodnetwork.com%2Fsearch%2Fdelegate.do%3FfnSearchString%3D" + search + "%26fnSearchType%3Dsite%22%20and%0D%0A%20%20%20%20%20%20xpath%3D'%2F%2Fdiv%5B%40class%3D%22result-item%22%5D'&format=json&callback=?",
function (data) {
$.each(data.query.results,
function (index, item) {
for (i = 0; i < item.length; i++) {
var each = item[i];
var link = (each.h3.a.href).replace('/', 'http://www.foodnetwork.com/');
$('<li></li>').html('' + each.h3.a.content + '').appendTo('#searchResults');
}
});
});
}
$("#btnSearch").click(function () {
var searchText = document.getElementById('txtSearch').value;
if (searchText == "") {
alert('You must first enter a food to search!');
}
else {
searchRecipes(searchText);
}
});
There you have it. Enter a food to search in the textbox, click on btnSearch, and a list of recipes are found. Only returns 12, as that is how the site is setup, but its a nice basic example of yql page scraping...
Oh, by the way, if you want a copy of the script, feel free to download at:
http://www.joshuablackstone.com/Scripts/Searches/FoodNetwork.js
Posted by Joshua Blackstone at 1:10 PM 0 comments
Labels: yql json yahoo feed
Tuesday, March 16, 2010
White Pages API Sample
I was looking on the web, and found that there are few if any real samples available for the White Pages People and Reverse Number Searches.
I've posted a simple example that has 2 search forms. The first one is a simple people search that takes the name and the zip code as parameters and displays the results below.
The second search was simpler, as I simply ask for a 10 digit phone number and it displays the results found below as well. You can optionally pass a state code in place of the area code, but I figured i'd keep it simple for now.
If anyone is interested in checking it out, there is source code and a working example available at:
Sample with Source.
Hope this helps someone out...
Posted by Joshua Blackstone at 9:33 AM 0 comments
Labels: API, Jquery, JSON, White Pages
Sunday, March 14, 2010
NYTimes Json Feeds
Just getting started at this, but I'm planning on working now with the New York Times JSON API that is provided.
Looks like this is a very solid api, that allows for feeds from entertainment, to current Congress members, to much, much more...
Just signed up for my membership and requested an API by going to: NY Times Registration
A quick example that I just used for testing...
$.getJSON(
"http://api.nytimes.com/svc/news/v2/all/recent.json?api-key=XXX",
function(data) {
$.each(data.results,
function(index, item) {
var myItem = $("<ul></ul>");
$("<li></li>").html(item.headline).appendTo(myItem)
.wrap("<a target='_blank' href='" + item.url + "')></a>");
myItem.appendTo("#results");
});
});
});
Send that to a div called 'results', and you're done.
Posted by Joshua Blackstone at 3:16 PM 0 comments
Sunday, March 7, 2010
YQL Basic JSON Yahoo News Feed
Just getting started with YQL and JSON Feeds, must say im impressed...
A quick script to pull the top stories from yahoo news in a JSON Feed
$.getJSON(
"http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20rss%20where%20url%3D%22http%3A%2F%2Frss.news.yahoo.com%2Frss%2Ftopstories%22&format=json&env=http%3A%2F%2Fdatatables.org%2Falltables.env&format=json&callback?",
function(data) {
$.each(data.query.results.item,
function(index, item) {
var myItem = $("
$("").html(item.title).attr("href", item.link).appendTo(myItem);
$("
").appendTo(myItem);
myItem.appendTo("#MYDIVID");
});
}
);
That's all it takes...
Posted by Joshua Blackstone at 2:04 PM 0 comments
Labels: yql json yahoo feed
Thursday, March 4, 2010
Post Server Side without Postback
I was reading from the following article:
http://disturbedbuddha.wordpress.com/2008/01/08/executing-server-side-code-from-javascript/
and realized that this opened up some possibilities for me. Hopefully it can for you as well.
It discusses the use of combining both Javascript and Server-Side Code. I took his example a step further.
Lets say that you want to validate a user form, but you don't want to have a postback to the server during the validation. Javascript would be the preference here, and it could look similar to the following:
if
(document.formname.elementname.value == "") {
var results = document.getElementById('resultslabel');
results.innerHTML = ("Please put in a name.");
return false;
}
and the control to validate would simply be a standard html button:
<input type=button onclick=formCheck(); value=Submit />
However, let's say that you want to include a server-side event after the validation is complete. I tried first to simply add a onserverclick event handler to the button, but it posted back everytime regardless of the client status. I tried using an Update Panel, with a standard asp.net button control, same thing. This hack solved the trick. I added an ASP.NET Button, called it Button2, such as:
<asp:Button ID=Button2 runat=server style=display:none; onclick=Button2_Click />
(note the style tag...), and added a Server-side event handler to Button2.
Now, simply add the line:
document.getElementByID('Button2').click();
after the if condition in your javascript code.
Easier than I thought...
Posted by Joshua Blackstone at 1:36 PM 0 comments