Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


MS ACCESS text date field compared with datereltoday in query

Posted on 2013-11-14
Medium Priority
Last Modified: 2013-11-14
I have a field SVC_DATE stored as text in this format 20131114
and what I want to do is use a criteria of 'this month' as in current month based on todays date.

I am able to get the month out of the thing with this
SVC_MONTH: Month(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy"))

And I'm having trouble with setting up the criteria of 'this month'.

I was thinking something like "Like Month(DATERELTODAY(0))" but get a data type mismatch

Question by:ghettocounselor
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
  • 2
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39648581
This month would simply be:


Is that what you are looking for?
LVL 61

Expert Comment

ID: 39648591
Based on your expression, I think you would  just need:

SVC_MONTH: Month(Format(Mid([SVC_DATE],5,2) & "/" & Mid([SVC_DATE],7,2) & "/" & Left([SVC_DATE],4),"mm/dd/yyyy"))

And set the criteria

= Month(Date())

Author Closing Comment

ID: 39648596
Works as advertised.
Appreciate the quick reply.
LVL 40

Expert Comment

ID: 39648598
You can use DateSerial(Y,M,D) for conversion if you like to use date functions:
DateSerial(Left([SVC_DATE],4), Mid([SVC_DATE],5,2), Mid([SVC_DATE],7,2))
but in you case month is equal to Mid([SVC_DATE],7,2)
should be enough

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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