Hmm, Translating MySQL to MS Access?

Question

Below is the MySQL code I am looking to translate mysql to ms-access. Exact help is of course super nice - but better understanding the logic is fine too if it helps me work this out (which would help me in other such queries I want to run)

thank you in advance :)

'''// Law report for  // 
select
    b.bib_id as 'Bib ID',
    b.status as 'Bib Status',
//    b.created_by as 'Created by',
//    b.date_created as 'Date created',
//    b.updated_by as 'Updated by',
//    b.date_updated as 'Date updated',
    h.holdings_id as 'Holdings ID',
   h.location as 'Location',
    h.call_number as 'Call Number',
    i.updated_by as 'Updated by',
    i.date_updated as 'Date updated',
    n.note as 'Item note'
from ole_ds_bib_t b
join ole_ds_holdings_t h on b.bib_id = h.bib_id
join ole_ds_item_t i on h.holdings_id = i.holdings_id
join ole_ds_item_note_t n on i.item_id = n.item_id
where 
    h.location like '%Law%' and 
    n.type = 'nonPublic' and 
    n.note regexp '^[[:alpha:]]{6}$' and 
    n.note not in ('folder', 'binder', 'SLAVEX', 'NISORI') and
   i.date_updated between '2019-11-01' and '2019-11-31';'''

MySQL to Access? Quite a backwards step! The only thing that won't really work out there is the regex bit.. You might be able to fudge it with `Not ALike "%[0-9]%"` and a length check — Caius Jard, Dec 05 '19 at 17:02
[MS Access doesn't have a regex operator](https://stackoverflow.com/a/5539359/16587); this question needs the data structure of the postgres table; the data structure of the ms access table; the types at play; and relationships. — George Stocker, Dec 05 '19 at 17:02
I agree, not ideal. This example should help me (I think): https://stackoverflow.com/questions/44506748/translating-mysql-queries-to-ms-access — jyssyl, Dec 05 '19 at 17:16
Also this: https://stackoverflow.com/questions/7854969/sql-multiple-join-statement — Andre, Dec 05 '19 at 17:16

score 0 · Accepted Answer · answered Dec 05 '19 at 17:33

You sql as written should work just fine, EXCEPT for this:

n.note regexp '^[[:alpha:]]{6}$'

Access does not support or have reg expressions.

It not clear what the "^" is in above, but if you looking for 6 characters?

then

n.note like "??????"

So your problem is not the sql - it looks ok. The issue is changing the regexpresison to a like command in Access sql. You have to explain what the regexp does, and then we can see if the like command in Access can recreate this ability.

Hmm, Translating MySQL to MS Access?

1 Answers1