Unfortunately, it will be impossible to get a true macro rig for just $500, but you should be able to get a great all-around rig for $500. As has been mentioned, most macro lenses will be more expensive than $500 by themselves. I agree with the recommendation of a Nikon D40 + the 18-55mm kit lens (or whatever the equivalent Canon is). Start with this, then purchase a dedicated macro lens when your budget allows. You'll be able to get by pretty well with a rig like this. Moreover, it will be a versatile setup for non-tank photos.
EDIT: Check adorama.com or keh.com for some great deals on used/refurb equipment. I just checked adorama.com, for example, and they have a complete Nikon D40 kit (including 18-55m kit lens) for $429. Check here:
25420B Nikon D40 6.1 Megapixel Digital SLR Camera 3X Zoom Kit Outfit, with 18-55mm f/3.5-5.6G ED II AF-S DX Zoom Nikkor Lens - Refurbished by Nikon U.S.A. I'm sure there are similar deals on equivalent Canons.
By the way, the term "macro" (or "micro" in Nikon vernacular) is so often thrown about and misused, unfortunately. In order to be considered "macro", a lens needs to be able to project a focused image of the subject onto film or the digital sensor at (or close to) life-size or 1:1 scale. To give some perspective, this means being able to focus on a nickel (US 5-cent coin) close enough so that it fills the frame horizontally (not quite 100%, but close)
and the image needs to be focused. Sensors from various manufacturers differ, but Nikon's crop sensor (used on the D40, D60, D90, D300, etc) is ~23mm wide, and a nickel is ~21mm in diameter. Canon's crop sensor is 22mm wide.
Unfortunately, many P&S cameras marketed as capable of macro photography and even some DSLR zoom lenses with a "macro" setting don't even come close to this, which eliminates them as macro lenses. These are really "close-up" lenses because they can't reproduce a focused image of the subject onto the sensor at 1:1 scale.
Having said that, while the 18-55mm might be able to take pictures up close, they are not macro photos. The Nikon 18-55mm is an awesome all-around lens (at least the Nikon is. I shoot Nikon and am not familiar with the Canon 18-55mm), and can take great close-ups (1:3 scale), but it isn't capable of taking macro photos. I like using the 18-55mm (1:3) and my 35mm (1:6) to take fish shots and relative close-ups of the tank as well as FTSs, but for macros, I use my 105mm micro (1:1).