Apart from those who think that the moon landing was faked, there are many who think that the whole space program was and is a complete waste of money. But I think that the critics are missing some of the key benefits of the program.
Earlier today, a well-respected member of the blogosphere Tweeted "What's with the lunar nostalgia? It's like the Taj Mahal: impressive but wasteful one-off, done for show, with no regard for cost of capital". I replied, "Moon landing reflects a time when US could set a goal and achieve it with American talent and manufacturing. Couldn't happen now." His response was to the effect that he's glad that it couldn't -- this from someone with a doctoral degree.
Unlike your faithful correspondent, that particular blogger wasn't around for the whole Sputnik to the moon landing era. He also wasn't around for JFK's inspirational speech supporting a Moon mission (http://www.youtube.com/watch?v=ouRbkBAOGEw).
We live in a completely different world today, one with different priorities, challenges, and cultures. So it is very difficult for someone without that historical context and life experience to understand why JFK issued that challenge and why the country responded enthusiastically, even as we spent our tax dollars on it. (In the same era, we also devoted national resources to civil rights and to reducing poverty, efforts that continue to this day.)
But I think that it's unfair to say that the impact of the space program was simply Tang and a bunch of rocks. For those of us at a formative age during that era, the space program inspired many of us to go into the sciences. There have been innumerable contributions in medicine, biology, physics, genetics, computer science, and other disciplines from people born between 1945 and 1955. If those people had been born in the 1980's, they might have gone into banking, finance, real estate development, or consulting, the most lucrative fields of this decade (at least until last year). We might not have sequenced the genome or created the Apple II.
Beyond that, I wonder if the US is capable of ever again successfully addressing a national goal of similar magnitude. We've so far shown ourselves unable to achieve consensus on reducing dependence on foreign sources of energy (especially petroleum), on addressing the most egregious symptoms of climate change, on improving the transportation infrastructure, on improving the telecommunications infrastructure (broadband, mobile, and more), and on providing the basic human right of health care to all of our citizens.
Every discussion on these topics (and others) becomes quickly bogged down in a battle of special interests and a reluctance to make the investments needed to achieve those goals, especially when the payoff is long-term or uncertain. Why is that? Is it because people see the problems as being insurmountable? Is it because people have so many near-term and personal problems that demand their attention? Is it because people are more focused on themselves and their families? Is it because our country has become so much more diverse in culture, tastes, income, and education that it is much harder to build consensus? Is it because people think that it takes too much effort to go up against the large-scale special interests and their well-financed lobbyists and legislators? Or are there other reasons that I am missing?