JohnTall
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In your query, how about something like:
SELECT *, YYYYMMDD_To_Date([DateFiel d]) 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] "
SELECT *, YYYYMMDD_To_Date([DateFiel
FROM yourTable
WHERE [DateField] Like "[0-9][0-9][0-9][0-9][0-9]
ASKER
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 "########.???"
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([Dat eField],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
You can use the [ ] syntax with Access as well, although the syntax should have looked like:
SELECT *, YYYYMMDD_To_Date(Left([Dat
FROM yourTable
WHERE [DateField] Like "[0-9][0,9][0-9][0-9][0-9]
which includes the period as the 9th character and would accept any file extension
ASKER
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
Like "[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
IsNumeric(Left(YourField,8
would include values that include a minus sign as the first character, or a period anywhere in the first eight characters
ASKER
YYYYMMDD_To_Date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Mid(strDate, 7, 2))