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
Solved

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

Posted on 2012-03-22
9
358 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:JohnTall
  • 3
  • 3
  • 3
9 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37753269
Try this:

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

Author Comment

by:JohnTall
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))
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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]"
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Closing Comment

by:JohnTall
ID: 37753309
Thanks mbizup
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37753316
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37753377
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
 

Author Comment

by:JohnTall
ID: 37753638
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37753652
Glad to help :-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

861 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