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

Hi,

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,
2))
End Function  

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

Thanks
JohnTallAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:

SELECT * FROM YourTable
WHERE LEN(YourField) >= 12 AND IsNumeric(Left(YourField,8)) = True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnTallAuthor Commented:
sorry, the correcct line in the function is:

YYYYMMDD_To_Date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Mid(strDate, 7, 2))
0
Dale FyeOwner, Developing Solutions LLCCommented:
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]"
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

JohnTallAuthor Commented:
Thanks mbizup
0
mbizupCommented:
Dale,

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:

<<yyyymmdd.pdf>>

WHERE [DateField] Like "########.???"
0
Dale FyeOwner, Developing Solutions LLCCommented:
Miriam,

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
0
JohnTallAuthor Commented:
to fyed and mbizup:
 
Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"

and

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

worked perfect as well

Thanks again
0
mbizupCommented:
Glad to help :-)
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.