Microsoft Access 2000 Help

[SmavidDavid]

Ok, well my gran runs a chicken club. It has over 200 members, and she hand writes them all out. So as the loving kind grandson i am, i said i can save her the hassle of hand writng them by putting all members into a database. But i didnt wanna just make a simple one, i wanted to make it complex, but easy to use. So far its easy to use, one form directs you to any other. But i want to make it alot easier, i was goin to have,
Members A-Z
Members a,b,c
Members d e f
Members etc...
Untill Z, but im struggling now, as i dont know how to do this using queries. If anybody could help i would be grateful. As well i was goin to put:
Members Sussex
Members Kent, and so on for major countys around me,. However for people who dont live in one of these, i added a "other" option, of which ill type in thier county. But to have
Members Other, i dont know how to do. As it shows members from all countys. Any help?

 

[SmavidDavid]

B u M p Y !

 

[Kaori]

Members A-Z
Members a,b,c
Members d e f
Members etc...

Explain this again... it's so confusing... why is it 'd e f' and not 'd,e,f'? what is 'etc...'
for 'Other' you can exclude 'Kent' and 'Sussex' right?

 

[SmavidDavid]

OK i want to search my query for people whos names begin with
A or B or C
D or E or F
and search like this untill
X or Y or Z

Forget "other" members things, i sovled my prblem

 

[Kaori]

in your query design view.. you can do something like...

Criteria Like A*
or Like B*
or Like C*

Criteria Like [A-C]*
I have 2007, so I am not sure if it's the same in 2000. I can also do this instead.

You can guess for other characters you can do Not Like [A-Z]*

 

[emmille]

DIM sqlstr as string

Select * from tablename where ["tablefield"] = "a*" or ["tablefield"] = "b*" or ["tablefield"] = "c*" ORDER BY surname

summit like that, you may have to correct the syntax a little as i dont have a copy of access in front of me.

 

[Kaori]

http://www.fgcu.edu/support/office2000/access/queries.html

I don't think you can use = with A*
it has to be LIKE.

 

[emmille]

http://www.fgcu.edu/support/office2000/access/queries.html

I don't think you can use = with A*
it has to be LIKE.

like would show anything that has an A in it, where as using =a with a wildcard would search for anything begining with A.

I think, not used it properly in a while like that will run a test and let you know as im infront of a pc now.

 

[emmille]

sorry you where right, i was thinking of something else.

correct syntax is

Dim sqlstr As String
sqlstr = "Select * from tablename "
sqlstr = sqlstr & " where fieldname like " & "'" & textboxname.Value & "*'"
'MsgBox sqlstr
With Me.lstpeople
.RowSource = sqlstr
.ColumnCount = 4
.ColumnHeads = True
End With

where With Me.lstpeople = the name of your list box.

Putting the star at the end like i have includes anything at the end of it, so if you search for a in the text box anything that starts with an a would be shows. if you searched b in the text box, anything that starts with a b would be shown, etc etc.



edit - sorry for double post.

 

[SmavidDavid]

well i put

A* , and it changes automatically to Like "A*"

AND IT WORKS!!! so tyvm guys