Checking string pattern in MS Access query

Question

I have the following query in MS Access

SELECT CREAT.Subject, CREAT.CREAT, (IIf([CREAT.TargetDays]=".",".",Val([CREAT.TargetDays])*24)) AS TargetDays
FROM CREAT
WHERE IsNumeric(CREAT.TargetDays) AND IsNumeric(CREAT.CREAT)
ORDER BY Subject, Val([CREAT.TargetDays]);

I would like to check if a field called FolderName is a string like D18, D1 etc.. which means the first character should be 'D' which is immediately followed by digits. Could someone please tell me how to accomplish this.

4dmonster · Accepted Answer · 2018-09-26 13:08:12Z

2

Try this

[FolderName] Like 'D[0-9]*'

edited Sep 26, 2018 at 13:08

answered Sep 26, 2018 at 12:39

4dmonster

3,0411 gold badge16 silver badges25 bronze badges

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

4 Comments

4dmonster Over a year ago

i've mistaken. must be Like 'D[0-9]*'

sam Over a year ago

Yes that worked. Now I hav e string like 'Day 2', 'Day 21', 'Day 10 -Observational Cohort', how can I get strings that are like 'Day' and followed by only digits. In my example, it should return only 'Day 2', 'Day 21' and also in the select query I want to extract the numbers 2 from 'Day 2' and 21 from 'Day 21'. How to accomplish this? It would be great if you could help me with this.

mxix Over a year ago

You could probably do it with 'D#' as well. [0-9] will only look for 1 single digit. and * for any character. # will allow more than 1 digit, but digits only. According to: support.office.com/pt-pt/article/…

4dmonster Over a year ago

if you have 'Day' then space, then number , then anything unnecessary and want to extract number - try this: val(mid([FolderName],5))

Collectives™ on Stack Overflow

Checking string pattern in MS Access query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related