error 3126 Invalid bracketing (1 Viewer)

[BobK]

I am running this query from VBA code:

SELECT * FROM tblWalkers WHERE tblWalkers.RefNo>=[First ref no.:] AND tblWalkers.walk5K=""1"";

It gives error 3126 Invalid bracketing of name '[First ref no.:]'

Why is this - what is wrong?

 

[Minty]

You can't have a full stop (or possibly the colon) in a field name like that.
I'm amazed access let you do that.

Remove both the full stop and the colon, you should avoid spaces and special characters in both table and field names, that cause lots of additional typing and strange errors.

Avoid using VBA functions or references as names as well (Date and Value are two obvious ones we see a lot)

 

[Gasman]

You can have captions if you want to see format like that.
I tended to use captions when I wanted spaces displayed EG: Account Number

 

[BobK]

You can't have a full stop (or possibly the colon) in a field name like that.
I'm amazed access let you do that.

Remove both the full stop and the colon, you should avoid spaces and special characters in both table and field names, that cause lots of additional typing and strange errors.

Avoid using VBA functions or references as names as well (Date and Value are two obvious ones we see a lot)

Thanks, the full stop was the issue but the colon is fine and would be common in a parameter. However, it does not ask the user for the parameter and gives "error 3061 Too few parameters. Expected 1"

 

[arnelgp]

just a guess:

AND tblWalkers.walk5K="1"

or

AND tblWalkers.walk5K=1

 

[Minty]

Thanks, the full stop was the issue but the colon is fine and would be common in a parameter. However, it does not ask the user for the parameter and gives "error 3061 Too few parameters. Expected 1"

I would still remove it. The colon is used in time values as a separator and could be misinterpreted. It also forces you to surround the field with square brackets every time you type it in. I'm lazy, so anything to reduce typing is a win as far as I'm concerned, and avoiding weird faults is an added bonus.

What you display to end-users and what you call things in the background are two different things.
As @Gasman said tables have a caption property that can be used to format the displayed label etc.