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?
 
mbizupConnect With a Mentor Commented:
Try this:

SELECT * FROM YourTable
WHERE LEN(YourField) >= 12 AND IsNumeric(Left(YourField,8)) = True
0
 
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 FyeCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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 FyeCommented:
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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.