Convert PHP query to MS Access

Question

How do I convert this query to MS Access?

SELECT * FROM table WHERE (id='11101958' OR number LIKE '%t11101958%' OR name LIKE '%t11101958%') OR (dateofbirth='1958-10-11' AND SUBSTR(name,1,1)='t') ORDER BY name LIMIT 0,10;

I have already found out:

LIMIT 0,10 > TOP 10

dateofbirth='1958-10-11' AND SUBSTR(name,1,1)='t' > dateofbirth=#10-11-1958# AND LEFT(name,1)='t'

What I can't seem to find out is how to have 2 sets of "AND". So basically I have:

SELECT TOP 10 * FROM table WHERE (id='11101958' OR number LIKE '%t11101958%' OR name LIKE '%t11101958%') OR (dateofbirth=#10-11-1958# AND SUBSTR(name,1,1)='t') ORDER BY name;

Also: is it correct that I have to search for mm-dd-yyyy instead of dd-mm-yyyy or yyyy-mm-dd?

What do you mean by "Two sets of "AND"" There is only one AND in your original mysql query. I think you just need to change out Substr(name, 1, 1) = 't' to LEFT(name, 1) = "t" and then go back and change all of your single quotes to double for your string literals. — JNevill
– JNevill, Commented Jan 10, 2020 at 20:03

forpas · Accepted Answer · 2020-01-10 20:15:18Z

2

The equivalent of the wild char % in Access is *.
The equivalent of SUBSTR() is MID() but since you want only the 1st char then LEFT() works too.
Instead of LIMIT use TOP.
You can use the format yyyy-mm-dd for the dates:

SELECT TOP 10 * 
FROM table 
WHERE (id='11101958' OR number LIKE '*t11101958*' OR name LIKE '*t11101958*') 
   OR (dateofbirth=#1958-10-11# AND LEFT(name,1)='t') 
ORDER BY name

answered Jan 10, 2020 at 20:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

vespino Over a year ago

Thank you, it almost works. When I add a string instead of an integer for "id" the query fails.

forpas Over a year ago

If the data type of the id is integer you should pass an integer or instead of id = '5' pass id = val('5')

vespino Over a year ago

BTW: TOP 10 doesn't seem to work. It simply shows the complete result set...

forpas Over a year ago

TOP 10 is the way to set the LIMIT in Access.

forpas Over a year ago

Check this: support.office.com/en-us/article/…

Collectives™ on Stack Overflow

Convert PHP query to MS Access

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related