Link to home
Start Free TrialLog in
Avatar of JohnTall
JohnTallFlag for Afghanistan

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnTall

ASKER

sorry, the correcct line in the function is:

YYYYMMDD_To_Date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Mid(strDate, 7, 2))
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]"
Thanks mbizup
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 "########.???"
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
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
Glad to help :-)
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