You will typically first be memory limited by the program model (2GB for each process in Windows32), then OS ability to map to RAM (4GB for standard WindowsXP, you can use more memory but it will be in swap, not on physical RAM), then Mainboard (typically 2GB-8GB).
CPUs addressing limits is a different thing though they strongly influence OS and the program model. And that is why we should move to 64-bit as soon as possible.
Corrections to a number of above posts:
1: The current generation of AMD64 cpus is limited to addressing 1 TB physical RAM. This is just a limitation of early hardware implementation. Not a limitation in AMD86-64.
2: 32-bit X86 can per definition address 64GB.
But the largest linear space to map some of those 64GB to is only 4GB.
This affects a number of decisions regarding the design and function of OS and program model. In practice this means that the Windows32 program model only allows for using a 2GB space per process. This translates into ~1.5-1.7 GB max memory for a program.
3: AMD86-64 can per definition extend addressing to 4PB (1 PetaByte = 1024 TeraBytes)
But the linear space to map those 4PB is 16EB (1 ExaByte = 1024 Petabytes)
Note that in this case the space is larger than addressable memory! By magnitudes. In practice this means that 64-bit applications are unlimited in a wide range of cases where 32-bit applications today are confined and crippled.
You will however be limited by whatever limits Microsoft see fit to provide you with in their 64-bit OSes.