What is wrong with this SQL Statement??(Resolved For MDB Files)

[BodwadUK]

Set DB = OpenDatabase(App.Path & "\Customer Database.mdb")
Set Records = DB.OpenRecordset("SELECT * FROM Customers WHERE [Site Name] LIKE 'A'%")

[Simon_R]

("SELECT * FROM Customers WHERE [Site Name] LIKE 'A%'")

Should do it

Simon

[kleinma]

try putting the wildcard in the single ticks

[kleinma]

Originally posted by Simon_R
("SELECT * FROM Customers WHERE [Site Name] LIKE 'A%'")

Should do it

Simon

beat me to it

[BodwadUK]

Your both wrong that just causes a

Syntax error in query expression '[Site Name] LIKE 'A'%'.

error

Any more suggestions???

[doofusboy]

No, they're correct and YOU'RE wrong. Your SQL statement should look like this when it's finished:


SELECT * FROM Customers WHERE [Site Name] LIKE 'A%'

That will return all records that have entries in the Site Name column that start with the capital letter A.


You may want to check if the column should be called:

Site_Name 'may need to avoid space in column name

[techgnome]

Originally posted by doofusboy
No, they're correct and YOU'RE wrong. Your SQL statement should look like this when it's finished:


SELECT * FROM Customers WHERE [Site Name] LIKE 'A%'

That will return all records that have entries in the Site Name column that start with the capital letter A.


You may want to check if the column should be called:

Site_Name 'may need to avoid space in column name

WOW! Talk about the kettle calling the pot black.... Doofus... before you start sligning it around like that, you may want to look up your info first. So far I have not yet seen the right answer.... Bodw is right, putting the % WILL CAUSE A SYNTAX ERROR. That's because he's using an ACCESS DATABASE.... Remember the open database command at the top of his code? He's opening an MDB file, Access. In Access, for what ever reason, MS decided that the wildcard should be a "*", not "%". So, the truly correct answer is:
1) Change the % to a *
2) Move the * to inside the tick marks
That should do it for you.

[kleinma]

Originally posted by techgnome
WOW! Talk about the kettle calling the pot black.... Doofus... before you start sligning it around like that, you may want to look up your info first. So far I have not yet seen the right answer.... Bodw is right, putting the % WILL CAUSE A SYNTAX ERROR. That's because he's using an ACCESS DATABASE.... Remember the open database command at the top of his code? He's opening an MDB file, Access. In Access, for what ever reason, MS decided that the wildcard should be a "*", not "%". So, the truly correct answer is:
1) Change the % to a *
2) Move the * to inside the tick marks
That should do it for you.

actually the % wont cause an error.. it will just return an empty recordset.. the error was due to the tick mark placement

[BodwadUK]

*Bodwad Snogs techgnome


YIIIIPPPPIIIIIIIEEEEEEEEEEEE

*Dances Around the room chanting "Here We Go, Here We Go Here We Go"

[doofusboy]

By the way, the word is spelled "s l i n g i n g".

When use SQL within Access wildcard character is *.

When using SQL from VB to query an Access database, using wildcard character % is perfectly acceptable. I use it all the time Mr. Smart (__*__).

[BodwadUK]

You cant because it doesnt work mister smartie pants!!!!

[kleinma]

Originally posted by BodwadUK
You cant because it doesnt work mister smartie pants!!!!

no he is right... i use % in some SQL statements via ADO that query an access database... and low and behold they always did and still work

[BodwadUK]

Ohhhh OOPS Sorry i got carried away and ran around screaming about nothing in perticular!!!

My family suggested i make a small padded room for myself. I just dont understand why i need to put the lock on the other side