Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Query Criteria where it contains 12 characters and the first 8 are numbers, not letters

Posted on 2012-03-22
Medium Priority
Last Modified: 2012-06-27

I'm trying to create a query that has a text field called 'Fname', it stores between others text formatted as follows: yyyymmdd.pdf (20120322.pdf for todays date, just for an example), so I want to set the criteria for this field to display only the records that are 12 char. long
Len([Fname])=12 and that the first 8 char. are numbers, this way I know that I have selected the proper field, and then I can run a function that turns the 8 char. into a date using the following: File_Date:YYYYMMDD_To_Date([Fname]) which calls the following function:

Function YYYYMMDD_To_Date(strDate As String) As Date
YYYYMMDD_To_Date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate,
End Function  

but I can't run it before I ensure that its len is 12 and that the first 8 char. are numbers

Question by:JohnTall
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 37753269
Try this:

WHERE LEN(YourField) >= 12 AND IsNumeric(Left(YourField,8)) = True

Author Comment

ID: 37753282
sorry, the correcct line in the function is:

YYYYMMDD_To_Date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Mid(strDate, 7, 2))
LVL 48

Expert Comment

by:Dale Fye
ID: 37753283
In your query, how about something like:

SELECT *, YYYYMMDD_To_Date([DateField]) as NewFieldName
FROM yourTable
WHERE [DateField] Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?


Author Closing Comment

ID: 37753309
Thanks mbizup
LVL 61

Expert Comment

ID: 37753316

Isn't that syntax for a SQL backend?

I think Access would be:

WHERE [DateField] Like "########*"

Or for a the exact format shown in the original post:


WHERE [DateField] Like "########.???"
LVL 48

Expert Comment

by:Dale Fye
ID: 37753377

You can use the [ ] syntax with Access as well, although the syntax should have looked like:

SELECT *, YYYYMMDD_To_Date(Left([DateField],8)) as NewFieldName
FROM yourTable
WHERE [DateField] Like "[0-9][0,9][0-9][0-9][0-9][0-9][0-9][0-9].*"

which includes the period as the 9th character and would accept any file extension

Author Comment

ID: 37753638
to fyed and mbizup:
Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"


Like "########.???"

worked perfect as well

Thanks again
LVL 61

Expert Comment

ID: 37753652
Glad to help :-)
LVL 48

Expert Comment

by:Dale Fye
ID: 37753657
The advantage of either of these, over the earlier method is that

IsNumeric(Left(YourField,8)) = True

would include values that include a minus sign as the first character, or a period anywhere in the first eight characters

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question